Home » RDBMS Server » Security » Reset SYS Account (Oracle 10g and Windows 2003)
Reset SYS Account [message #611331] Tue, 01 April 2014 11:44 Go to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
To make a long story short -- we change passwords on the sys, system and about 4 other accounts that we have in our Oracle 10g DB. I changed the password on all of the accounts (had to include "" around the password), but now I'm unable to log on with any of them due to an incorrect password or ID. I'm basically looking for any other option to log onto the db via sqlplus and change the sys password without logging in.

What I've already done:
checked to make sure my domain account is in the ora_dba group
altered the sqlnet.authentication_services = (NTS)
already tried to logon with sqlplus / as sysdba (ORA-01031: insufficient privileges.) even with sqlplus /nolog

I haven't tried deleting the pwd file -- didn't look as if it would've made a difference for me....

Is there any type of back door when unlocking the sys account?

can I restore the db from an RMAN backup to reset the password?

Any help would be greatly appreciated.
Re: Reset SYS Account [message #611333 is a reply to message #611331] Tue, 01 April 2014 11:51 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
I would log on to Windows as a local user (not with a domain account) who is in the ORA_DBA group, and then try
sqlplus / as sysdba
Please can you show (with copy/paste) what happens happens when you do. Also, show what happens when you try to connect as another user, such as SYSTEM.
Re: Reset SYS Account [message #611334 is a reply to message #611333] Tue, 01 April 2014 12:00 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
I cannot copy and paste due to the server is on a dev network.

I created a local account called sys.
logged on as the local account.
opened a command prompt
used the "sqlplus / as sysdba" as you suggested
recieved the following error:

ERROR
ORA-01031: insufficient priveleges
Re: Reset SYS Account [message #611337 is a reply to message #611334] Tue, 01 April 2014 12:35 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
So how did you create the account? What local groups is it a member of? The output of WHOAMI /ALL would be a good place to start. And what happens with the non-SYS logon attempts?

Please use copy/paste, then everyone can see what is going on. I transfer files between machines all the time, I'm sure you can find away Smile
Re: Reset SYS Account [message #611338 is a reply to message #611337] Tue, 01 April 2014 12:51 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
I created the account through the mmc and added the sys account to the local administrators as well as the ora_dba groups.
Whoami /all output:
ora_dba
owner
remote interactive logon
interactive
authenticated users

There's plenty of ways for me to transfer the data -- unfortunatley i would probably lose my job if i transfered the data to this workstation (think Snowden -- I don't plan on moving to Russia either....) I would love to copy the information directly instead of transcribing it, but it a security thang....

When I try to log on as system I get the following error:
ORA-01017: invalid username/password; logon denied.
Re: Reset SYS Account [message #611339 is a reply to message #611338] Tue, 01 April 2014 13:07 Go to previous messageGo to next message
EdStevens
Messages: 261
Registered: September 2013
Senior Member
papertiger wrote on Tue, 01 April 2014 12:51


When I try to log on as system I get the following error:
ORA-01017: invalid username/password; logon denied.


Well, that's progress, of sorts. Oracle is too dumb to lie about such things. This time, instead of relying on os authentication, you supplied a password. And either the username or the password you supplied was incorrect. Of course, we don't see the syntax you used, so have to guess ....
Re: Reset SYS Account [message #611340 is a reply to message #611338] Tue, 01 April 2014 13:19 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
OK, your IP address does suggest that you need to be careful.
The output of WHOAMI is not right. I think each group should be prefixed with the name of the machine, for instance. Is what you have transcribed what you actually see?

One suggestion: set some environment variables explicitly before attempting to connect, like this:
c:\users\john>
c:\users\john>set ORACLE_HOME=c:\app\oracle\product\12.1.0\dbhome_1

c:\users\john>set PATH=%ORACLE_HOME%\bin;%PATH%

c:\users\john>set ORACLE_SID=orclz

c:\users\john>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 1 19:16:07 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orclz>


You have still not shown how you attempted to log on a SYSTEM. Can you provide the exact command?
Re: Reset SYS Account [message #611348 is a reply to message #611340] Tue, 01 April 2014 13:41 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
What I've done up to this point:

c:\documents and settings\sys>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 1 19:16:07 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Error:
ORA-01031: insufficient priveleges

-I closed out of the command prompt at this time to do other work-

c:\documents and settings\sys>sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 1 19:16:07 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter user-name: system
Enter password:

Error:
ORA-01017: invalid username/password; logon denied

***I will transcribe exactly what comes up in Whoami /all in a sec.

Re: Reset SYS Account [message #611349 is a reply to message #611348] Tue, 01 April 2014 13:44 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
what specifically are you looking for in the whoami /all?

Re: Reset SYS Account [message #611360 is a reply to message #611349] Tue, 01 April 2014 15:53 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
C:\Documents and settings\sys>whoami /all

USER INFORMATION


user Name SID
============= ===================================
<servername>\sys XXXXXXXXXXXXXX


GROUP INFORMATION

Group Name Type Attributes
Everyone well-known group Mandatory group, enabled by default, enabled group
<servername>\ora_dba Alias Mandatory group, enabled by default, enabled group
Builtin\administrators Alias Mandatory group, Enabled by default, enabled group, group owner
Builtin\users Alias Mandatory group, enabled by default, enabled group
NT authority\Remote Interactive Logon Alias Mandatory group, enabled by default, enabled group
NT Authority\Interactive well-known group Mandatory group, enabled by default, enabled group
NT Authority\Authenticated Users well-known group Mandatory group, enabled by default, enabled group
NT Authority\This Organization well-known group Mandatory group, enabled by default, enabled group
Local well-known group Mandatory group, enabled by default, enabled group
NT Authority\NTLM Authentication well-known group Mandatory group, enabled by default, enabled group

PRIVILEGES INFORMATION

Privilege Name Description State

SeChangeNotifyPrivilege Bypass traverse checking Enabled

SeBackupPrivilege back up files and directories Disabled

SeRestorePrivilege change the system time Disabled

SeSystemTimePrivilege change the system time Disabled

SeShutdownPrivilege shut down the system Disabled

SeTakeOwnerShipPrivilege Take ownership of files or other objects Disabled

SeSystemEnvironmentPrivilege Modify firmware environment va1ues Disabled

SeSystemProfilePrivilege Profile system performance Disabled

SeProfilesingleProcessPrivilege Profile single process Disabled

SeIncreasesBasePriorityPrivilege Increase scheduling priority Disabled

SeLoadDriverPrivilege Load and unload device drivers Disabled

SeCreatePagefilePrivilege Create a page file Disabled

SeIncreaseQuotaPrivilege Adjust memory quotas for a process Disabled

SeUndockPrivileg Remove computer from docking station Disabled

SeManageVolume Privilege Perfomr volume maintenance tasks Disabled

SeImpersonatePrivilege Impersonate a client after authentication Enable

SeCreateGlobalPrivilege Create global objects Enable





Re: Reset SYS Account [message #611364 is a reply to message #611348] Tue, 01 April 2014 18:33 Go to previous messageGo to next message
EdStevens
Messages: 261
Registered: September 2013
Senior Member


papertiger wrote on Tue, 01 April 2014 13:41
What I've done up to this point:

c:\documents and settings\sys>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 1 19:16:07 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Error:
ORA-01031: insufficient priveleges

-I closed out of the command prompt at this time to do other work-

c:\documents and settings\sys>sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 1 19:16:07 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter user-name: system
Enter password:

Error:
ORA-01017: invalid username/password; logon denied

***I will transcribe exactly what comes up in Whoami /all in a sec.



Ok, when you specify to connect "as sysdba", it means username and password are ignored and you are authenticated by the OS, based on your OS account group membership.

When you do NOT specify 'as sysdba' (as in your second attempt) authentication is by username/password. Whatever password you gave for oracle user SYSTEM was not the correct for that account.
Re: Reset SYS Account [message #611378 is a reply to message #611360] Wed, 02 April 2014 01:53 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
Well, the whoami output looks fine. If your environment variables are right and your OS permissions are right and NTS is enabled in your sqlnet.ora and the service is running under a local system account (it is, isn't it?) then it should work.
The only remaining suggestion I have is to delete the Windows service for the instance, and create a new one (use the oradim.exe utility for this). Failing that, install a new Oracle Home and use that to create a service and open the database.
Perhaps someone else has an idea.

[Updated on: Wed, 02 April 2014 01:57]

Report message to a moderator

Re: Reset SYS Account [message #611421 is a reply to message #611364] Wed, 02 April 2014 06:57 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
totally understandable -- that's why I'm looking for a back door or an easy way to get connected to the DB so I can reset the passwords.
Re: Reset SYS Account [message #611422 is a reply to message #611421] Wed, 02 April 2014 06:59 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
John -- I will try your suggestion in a little and let you know how I fair. Other than deleting/altering the password file and your suggestion - i'm running out of ideas.
Re: Reset SYS Account [message #611429 is a reply to message #611422] Wed, 02 April 2014 08:08 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
I'm thinking that we need to install a new bd and transfer the data. Any suggestions?
Re: Reset SYS Account [message #611466 is a reply to message #611429] Wed, 02 April 2014 14:51 Go to previous messageGo to next message
EdStevens
Messages: 261
Registered: September 2013
Senior Member
papertiger wrote on Wed, 02 April 2014 08:08
I'm thinking that we need to install a new bd and transfer the data. Any suggestions?



And how do you envision 'transfer the data' without working credentials for the source database.

If you can't connect "/ as sysdba", then you need to address that issue, post haste. That is your real problem, and it is fixable.
Re: Reset SYS Account [message #611560 is a reply to message #611466] Thu, 03 April 2014 10:40 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
Resolution: switched the sql_authentication_services= backt to (NTS) from (NONE) in sqlnet.ora
added multiple admin groups to the Oracle Administrators Assistant for Windows snap-in
restarted listener and db service and we were able to login "/as sysdab"

thanks for the help
Re: Reset SYS Account [message #611562 is a reply to message #611560] Thu, 03 April 2014 11:08 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
You said it was NTS in your very first post! We all assumed that you were telling the truth!! Sad

I'll forgive you, because you have posted the solution. Smile

Re: Reset SYS Account [message #611563 is a reply to message #611562] Thu, 03 April 2014 12:12 Go to previous messageGo to next message
papertiger
Messages: 22
Registered: March 2014
Location: MD
Junior Member
Ha -- fooled you. just joking -- I changed the setting afterwards for testing purposes and then changed it back. I truely think what resolved the issue was adding the admin groups into the admin assistant snap-in. Right now I don't have the time to test it, but I will in the near future with another local account. Thanks again for all the help.
Re: Reset SYS Account [message #611570 is a reply to message #611563] Thu, 03 April 2014 13:28 Go to previous messageGo to next message
EdStevens
Messages: 261
Registered: September 2013
Senior Member
papertiger wrote on Thu, 03 April 2014 12:12
Ha -- fooled you. just joking -- I changed the setting afterwards for testing purposes and then changed it back. I truely think what resolved the issue was adding the admin groups into the admin assistant snap-in. Right now I don't have the time to test it, but I will in the near future with another local account. Thanks again for all the help.


I'm not sure exactly what you mean by "adding the admin groups into the admin assistant snap-in", but ultimately there are only two pieces to the puzzle of connecting '/ as sysdba'.
First, sql_authentication_services must be set to NTS
Second, the OS account being used must be a member of the local group ORA_DBA. Dosn't really matter if it's a local account or a domain account, but it must be a member of that local group.

That's it.
Re: Reset SYS Account [message #611573 is a reply to message #611331] Thu, 03 April 2014 13:34 Go to previous messageGo to next message
EdStevens
Messages: 261
Registered: September 2013
Senior Member
I thought I was seeng double.

Why were you running people ragged by double posting?


http://www.orafaq.com/forum/t/192247/

http://www.orafaq.com/forum/t/192229/
Re: Reset SYS Account [message #612578 is a reply to message #611573] Tue, 22 April 2014 03:29 Go to previous message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I know, this discussion is over. Anyway:
papertiger
I'm unable to log on with any of them due to an incorrect password or ID

John
Your IP address does suggest that you need to be careful.

Edward Snowden might know your lost password, eh? ./fa/1599/0/
Previous Topic: Oracle password
Next Topic: what is wrong here
Goto Forum:
  


Current Time: Fri Aug 29 15:32:30 CDT 2014

Total time taken to generate the page: 0.20521 seconds