ORA-01017: invalid username/password; logon denied

[Former Member]

HI all,

Issue in EHP5 upgarde - Main phase - Pre processing , sub phase - MAIN_SHDINST/SUBMOD_SHDALIASCRE/SCEXEC_ALIAS:

Following are the error lines from file SCEXEC_A.EC1

TRACE-INFO: 78:  [     dev trc,00000]  Got SAPSR3SHD's password from OPS$-user                                            27  3.723855
TRACE-INFO: 79:  [     dev trc,00000]  Disconnecting from connection 0 ...                                                25  3.723880
TRACE-INFO: 80:  [     dev trc,00000]  Rolling back transaction                                                           44  3.723924
TRACE-INFO: 81:  [     dev trc,00000]  Closing user session (con=0, svc=6000000001fdca28, usr=600000000207dad0)          240  3.724164
TRACE-INFO: 82:  [     dev trc,00000]  Disconnected (con=0) from ORACLE.                                                 794  3.724958
TRACE-INFO: 83:  [     dev trc,00000]  Connect as 'SAPSR3SHD' with password from table SAPUSER                            27  3.724985
TRACE-INFO: 84:  [     dev trc,00000]  Connecting as SAPSR3SHD/<pwd>@EXQ on connection 0 (nls 0) ... (dbsl 720 090112, UNICODE[2])
TRACE-INFO: 85:                                                                                                           35  3.725020
TRACE-INFO: 86:  [     dev trc,00000]  Assigning username to user session: con=0, usrhp= 600000000207dad0                 24  3.725044
TRACE-INFO: 87:  [     dev trc,00000]  Assigning password to user session: con=0, usrhp=600000000207dad0                  29  3.725073
TRACE-INFO: 88:  [     dev trc,00000]  Starting user session: OCISessionBegin(con=0, usr='SAPSR3SHD', svc=6000000001fdca28, srv=6000000002003990, usr=600000000207dad0)
TRACE-INFO: 89:                                                                                                          946  3.726019
TRACE-INFO: 90:  [     dev trc,00000]  Tue Jul 17 23:56:36 2012                                                      10095292  13.821311
TRACE-INFO: 91:  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with -1=OCI_ERROR                           23  13.821334
TRACE-INFO: 92:  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:                     56538  13.877872
TRACE-INFO: 93:  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                       
TRACE-INFO: 94:                                                                                                           51  13.877923
TRACE-INFO: 95:  [    dbsloci.,00000]  *** ERROR => CONNECT failed with sql error '1017'                                 357  13.878280
TRACE-INFO: 96:  [     dev trc,00000]     set_ocica() -> SQL error code 1017                                              27  13.878307
TRACE-INFO: 97:  [     dev trc,00000]  -->oci_get_errmsg (con=0, rc=1017)                                                 30  13.878337
TRACE-INFO: 98:  [     dev trc,00000]     OCIErrorGet -> SQL error code: 1017                                            145  13.878482
TRACE-INFO: 99:  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                       
TRACE-INFO: 100:                                                                                                           28  13.878510
TRACE-INFO: 101:  [     dev trc,00000]     DbSlConnect(con=0) -> orc=0, 99=DBSL_ERR_DB                                     33  13.878543
TRACE-INFO: 102:  [    dblink  ,00000]  ***LOG BY2=>sql error 1017   performing CON                                     26015  13.904558
TRACE-INFO: 103:  [    dblink  ,00000]  ***LOG BY0=>ORA-01017: invalid username/password; logon denied                     31  13.904589

tp returncode summary:

TOOLS: Highest return code of single steps was: 0
ERRORS: Highest tp internal error was: 0232
tp finished with return code: 232
meaning:
  connect failed
SAPup> Process with PID 16231 terminated with status 232 at 20120717235636!

Yes, during as part trouble shoot i tried follwoing sap notes 400241 and fixed the paswprd with brconnect for SAPSR3SHD as follows,,

Step 1

exqadm 30> brconnect -u system/manager -f chpass -o SAPSR3SHD -p SAP
BR0801I BRCONNECT 7.20 (22)

BR0280I BRCONNECT time stamp: 2012-07-17 23.48.19
BR0828I Changing password for database user SAPSR3SHD

BR0280I BRCONNECT time stamp: 2012-07-17 23.48.19
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRCONNECT:
c
BR0280I BRCONNECT time stamp: 2012-07-17 23.48.20
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...
BR0280I BRCONNECT time stamp: 2012-07-17 23.48.20
BR0829I Password changed successfully in database for user SAPSR3SHD
BR0830I Password changed successfully in table OPS$EXQADM.SAPUSER for user SAPSR3SHD

BR0280I BRCONNECT time stamp: 2012-07-17 23.48.20
BR0802I BRCONNECT completed successfully

Step 2

SQL> conn SAPSR3SHD/SAP@EXQ

Connected.

SQL> conn SAPSR3SHD/SAP

Connected.

SQL>

After setting the password witha bove commands issue remains same and we have maintianed UPGSHD settings in DBCON from DB02 and tested successfully...Issue still remains same and when we tried to analyze table SAPUSER following are my findings mentioned below

SQL> select owner from dba_tables where table_name='SAPUSER';

OWNER
------------------------------
OPS$EXQADM

SQL> select * from OPS$EXQADM.SAPUSER;

USERID
--------------------------------------------------------------------------------
PASSWD
--------------------------------------------------------------------------------
SAPSR3-CRYPT
V01/0028ZctvSB67Wv2d+/ye7iY=

SAPSR3SHD
jai

system

sandbox

USERID
--------------------------------------------------------------------------------
PASSWD
--------------------------------------------------------------------------------
system
sandbox

system
manager

SAPSR3
sap

USERID
--------------------------------------------------------------------------------
PASSWD
--------------------------------------------------------------------------------
SAPSR3SHD
SAP

SAPSR3SHD-CRYPT
V01/0028ZctvSB67Wv2d2/y+7gY=

SAPSR3SHD
jai

USERID
--------------------------------------------------------------------------------
PASSWD
--------------------------------------------------------------------------------
SAPSR3SHD
jai

SAPSR3SHD

jai

SAPSR3SHD
jai

USERID
--------------------------------------------------------------------------------
PASSWD
--------------------------------------------------------------------------------
SAPSR3SHD
jai

SAPSR3SHD
sap

SAPSR3SHD
sandbox

15 rows selected.

SQL>
SQL>
SQL> select owner from dba_tables where table_name='SAPUSER';

OWNER
------------------------------
OPS$EXQADM

When we look in to table and compare SAPSR3 and SAPSR3SHD looks like too many passwords are synchronized for user SAPSR3SHD. In such cases how do we fix it if my analysis is correct....please advice..

Thanks,

Rahul

[Former Member · Accepted solution]

Hi Guys,

I could resolve issue with following steps....

1) I did reset of pwd with BRCONNECT as per standard process

brconnect -u system/manager -f chpass -o SAPSR3SHD -p SAP

2) Earlier i was also using command mentioned below when i tried to set password with "alter user SAPSR3SHD identified by SAP;" and running below below...

INSERT INTO "OPS$EXQADM".SAPUSER VALUES ('SAPSR3SHD', 'SAP');

inallly when i tried to evalute table SAPUSER

SQL> select owner from dba_tables where table_name='SAPUSER';

OWNER
------------------------------
OPS$EXQADM

SQL> select * from OPS$EXQADM.SAPUSER;

So when i compared output of above SQL in other SAP system it is noticed entries were not correct....

finally table truncated as follows are recreated enties as follows ...using brtools...

1) truncate table OPS$EXQADM.SAPUSER;                             - table truncated successfully

2) select count(*) from OPS$EXQADM.SAPUSER;                    - 0 rows selected

2) synchronize system password with manager                          - brconnect -u system/manager -f chpass -o system -p manager

3) set SAPSR3 and SAPSR3SHD passwords using BRTOOLS, this will create PUBLIC YSNONYMS also druing recreation of passwords..

brtools --> select -- 8 - Additional functions -->4 - Change password of database user -> select 2 and provide user and pasword

2 - Database user/password (user) ............. [system/*******]

now select 3 and provide user for which we are trying to synchronize new password..

3 ~ Database owner to change password (owner) . [SAPSR3]

continue and provide new password, during this PUBLIC SYNONYMS are created as follows..

BR0925I Public synonym SAP_SDBAH created successfully for table SAPSR3SHD.SDBAH

BR0925I Public synonym SAP_SDBAD created successfully for table SAPSR3SHD.SDBAD

BR0925I Public synonym SAP_DBSTATC created successfully for table SAPSR3SHD.DBSTATC

BR0925I Public synonym SAP_DBSTATTORA created successfully for table SAPSR3SHD.DBSTATTORA

BR0925I Public synonym SAP_DBSTATIORA created successfully for table SAPSR3SHD.DBSTATIORA

BR0925I Public synonym SAP_DBSTATHORA created successfully for table SAPSR3SHD.DBSTATHORA

BR0925I Public synonym SAP_DBSTAIHORA created successfully for table SAPSR3SHD.DBSTAIHORA

BR0925I Public synonym SAP_DBCHECKORA created successfully for table SAPSR3SHD.DBCHECKORA

BR0925I Public synonym SAP_DBMSGORA created successfully for table SAPSR3SHD.DBMSGORA

Once created succesfully for SAPSR3 and SAPSR3SHD check the output with belwo query..

SQL>  select * from OPS$EXQADM.SAPUSER;

USERID
--------------------------------------------------------------------------------
PASSWD
--------------------------------------------------------------------------------
SAPSR3-CRYPT
V01/0028ZctvSB67Wv2d+/ye7iY=

SAPSR3SHD-CRYPT
V01/0028ZctvSB67Wv2d+/ye7iY=

SQL> conn SAPSR3/sap@EXQ

Connected.

SQL> conn SAPSR3SHD/sap@EXQ

Connected.

A new learning for ORA 1017 error,,..Experts please advice if something is required to do..we can close this thread..

Thanks,

Rahul

[former_member189725 · Accepted solution]

You need to keep the same password in both the OPS$<SID>ADM.SAPUSER table and the oracle database . If they are not in sync , the shadow instance will get the password from SAPUSER table of user SAPSR3SHD  , but this will not be correctly maintained in the oracle database dictionary.

Once use brconnect to set the password and the set the same password in the oracle dictionary as well.

Regards

Ratnajit

[Former Member · Accepted solution]

When we set password with brtools for users SAPSR3 and SAPSR3SHD , brconnect is the tool that is internally called ....am i correct . Will it not update tables SAPUSER and same information to oracle dictionary? If not how do we set in Oracle dictionary

Thanks,

Rahul

[former_member189725 · Accepted solution]

Ideally brconnect should be used to maintain the password of SAPSR3 user and it maintains the password correctly in oracle dictionary as well as the SAPUSER table.

This should also be the same for SAPSR3SHD , but I have seen quite a few threads where issues have been faced . So maintain the password with "alter user SAPSR3SHD identified by SAP"  as well.

The password in SAPUSER table is stored encrypted , hence only brconnect can only update the password in the correct encrypted format . Do not insert the password manually.

Regards

Ratnajit

[Former Member · Accepted solution]

Hi Rahul,

Thanks for the solution provided. We have the same issue and it got solved by resetting the password of SAPR3SHD to SAP (Upper Case) using brtools.

We tried resetting the SAPR3SHD password to sap (lower case) but the issue was not solved.

SAP (Upper case) resolved our issue.

We are using Oracle 11g

Regards,

Surendra

[nicola_blasi]

Hy

i'd like to contribute for another solution if none of the above works.

I'm upgrading a SRM 5.00 to a 7.02 ...before this, i've upgraded oracle from 10.2.0.4. to 11.2.0.3.

I' had the same problem in SCEXEC_ALIAS with SQL error code 1017 and then ORA-28000 ..

SAPSR3SHD locked.

I tried to reset the password through brtools and working on OPS$ mechanism as Rahul suggested.

The same problem...

The problem was the case sensitive mechanism in oracle 11 after the upgrade so after unlocking SAPSR3SHD with alter user .....  account unlock  I did

alter system set sec_case_sensitive_logon=FALSE scope=BOTH;

problem solved and upgrade go on.

Thanks

Nickk

[former_member189725]

Please check this thread

http://scn.sap.com/thread/1807706

Should help you.

Regards

Ratnajit