cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-01017: invalid username/password; logon denied quality system refresh from production

Former Member
0 Kudos

Dear All;

I am in the post activities of SAP system refresh.

I restored the system using brtools successfully as you can see below:

then I went with the rest of the steps based on sap recomendations, and reached to SAP sql queries:

CREATE USER "OPS$<DOMAIN>\<SID>ADM" DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;

CREATE USER "OPS$<DOMAIN>\SAPSERVICE<SID>" DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;

GRANT CONNECT, RESOURCE, SAPDBA TO "OPS$<DOMAIN>\<SID>ADM";

GRANT CONNECT, RESOURCE, SAPDBA TO "OPS$<DOMAIN>\SAPSERVICE<SID>";

CREATE TABLE "OPS$<DOMAIN>\<SID>ADM".SAPUSER (USERID VARCHAR2(256), PASSWD VARCHAR2(256));

INSERT INTO "OPS$<DOMAIN>\<SID>ADM".SAPUSER VALUES ('SAPSR3', '<Password>');

CREATE SYNONYM "OPS$<DOMAIN>\SAPSERVICE<SID>".SAPUSER FOR "OPS$<DOMAIN>\<SID>ADM".SAPUSER;

CONNECT /

GRANT SELECT, UPDATE ON "OPS$<DOMAIN>\<SID>ADM".SAPUSER TO "OPS$<DOMAIN>\SAPSERVICE<SID>";

CONNECT /as sysdba

GRANT CONNECT, RESOURCE TO "OPS$<DOMAIN>\SAPSERVICE<SID>";

GRANT CONNECT, RESOURCE TO "OPS$<DOMAIN>\<SID>ADM";

I checked if all users are on opened state using select USERNAME,ACCOUNT_STATUS from dba_users;

The database is running file, and it is opened, but when I run R3trans -d it gives me RC 12 and i get this error:

*** ERROR => CONNECT failed with sql error '1017'

set_ocica() -> SQL error code 1017

-->oci_get_errmsg (con=0, rc=1017)

OCIErrorGet -> SQL error code: 1017

ORA-01017: invalid username/password; logon denied

DbSlConnect(con=0) -> orc=0, 99=DBSL_ERR_DB

***LOG BY2=>sql error 1017   performing CON

***LOG BY0=>ORA-01017: invalid username/password; logon denied

I used brconnect -u / -f chpass -o PSAPSR3 -p sap so i can reset the password, but still I am getting the same invalid username or password error.

I checked the listener status, and it is running.

Kindly let me know how I can fix my error

Best Regards

~Amal Aloun

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello All;

Thank you all for the help you provided me with. I followed the advice of Reagan, and recreated the OPS$ users based on sap note 50088 - Creating OPS$ users on Windows NT/Oracle

It turned out that my issue was with the naming convention of both the SID, and SAPService users. I was still using the same sid and sapservice users of the production system which I got the backup from. I should have changed them to the current system where I wanted to do the system refresh on.

Best Regards

~Amal Aloun

Answers (13)

Answers (13)

0 Kudos

well,

if you (anyone) would have taken a look at the log file (trans.log) would have seen the error:


4 ETW000  [     dev trc,00000]  Logon as OPS$-user to get SAPSR3's password                               28  0.015142

4 ETW000  [     dev trc,00000]  Connecting as /@TST on connection 0 (nls 0) ... (dbsl 720 220612, UNICODE[2])

4 ETW000                                                                                                  61  0.015203

4 ETW000  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_SVCCTX, con=0, svchp=02795F48)               370  0.015573

4 ETW000  [     dev trc,00000]  Allocating server context handle                                          31  0.015604

4 ETW000  [     dev trc,00000]  Attaching to database server TST (con=0, svchp=02795F48, srvhp=0279EFF8)

4 ETW000                                                                                                  74  0.015678

4 ETW000  [     dev trc,00000]  Assigning server context 0279EFF8 to service context 02795F48          30008  0.045686

4 ETW000  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_SESSION, con=0, usrhp=027ABBE0)               86  0.045772

4 ETW000  [     dev trc,00000]  Starting user session: OCISessionBegin(con=0, usr='/', svc=02795F48, srv=0279EFF8, usr=027ABBE0)

4 ETW000                                                                                                  40  0.045812

4 ETW000  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with -1=OCI_ERROR               28164  0.073976

4 ETW000  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:               95  0.074071

4 ETW000  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                      

4 ETW000                                                                                                  39  0.074110

4 ETW000  [    dbsloci.,00000]  *** ERROR => CONNECT failed with sql error '1017'                         38  0.074148

4 ETW000  [     dev trc,00000]     set_ocica() -> SQL error code 1017                                     19  0.074167

4 ETW000  [     dev trc,00000]  -->oci_get_errmsg (con=0, rc=1017)                                        20  0.074187

4 ETW000  [     dev trc,00000]     OCIErrorGet -> SQL error code: 1017                                    57  0.074244

4 ETW000  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                      

4 ETW000                                                                                                  39  0.074283

4 ETW000  [     dev trc,00000]  Info: 99=DBSL_ERR_DB, oerr=1017, try to connect with default password.

There is nothing wrong with the SAPSR3 password, the system cannot retrieve it BECAUSE the OPS$ is NOT working.

You can change the password for this user, you can drop the sapuser table you can do everything that has been said here (trying to find the magic bullet? giving recommendations to see if I'm lucky?) and it will not solve the problem.

first question

1.- do you have the parameter os_authent_prefix = ops$ set in your system?

2.- do you have created the ops$ user? (I do not see it on the screenshot you provided but there are some "hidden")

3.- forget about R3Trans and try to connect with sqlplus using the ops$ functionality

4.- Bit stupid, but as what user are you connected at OS level?

Former Member
0 Kudos

HI Amal,

Can you tell me what is the output of below commands.

sqlplus /as sysdba

1. SELECT USERNAME FROM DBA_USERS WHERE USERNAME like 'OPS$%';

2. connect /

3. connect /@SID --> put your system ID in place of SID

regards,

Vineet

Former Member
0 Kudos

Hi Amal,

Follow the OSS note 1576837 ORA-01017 invalid username password; and change the system password. This will help to solve the issue.

Thanks & Regards

Jose H.

former_member182657
Active Contributor
0 Kudos

Hi Amal,

Follow SAP Note   998004 - Update the Oracle Instant Client on Windows 

to update oracle client .

Hope this will resolve your issue.

Regards,

Gaurav

former_member182657
Active Contributor
0 Kudos

In addition kindly follow SAP Note   556232 - Environment settings for R/3/Oracle on Windows

Regards,

Gaurav

former_member182657
Active Contributor
0 Kudos

Hi,


DIR_CLIENT_ORAHOME is not set as profile nor env.

Your issue completely related with oracle client.Kindly follow SCN link

Regards,

Gaurav

former_member182657
Active Contributor
0 Kudos

Hi Amal,

If possible try to upgrade oracle client version and try again.

Regards,

Gaurav

former_member182657
Active Contributor
0 Kudos

Hi Amal,

Kindly share complete trans.log file and the kernel version on the system.

Regards,

Gaurav

Former Member
0 Kudos

Dear Gaurav;

I am running SAP Kernel 720 64bit unicode PL300

The content of my trans log file is the following:

4 ETW000 r3trans version 6.23 (release 720 - 15.05.12 - 20:12:03).

4 ETW000 unicode enabled version

4 ETW000 ===============================================

4 ETW000

4 ETW000 date&time   : 05.08.2014 - 17:50:30

4 ETW000 control file: <no ctrlfile>

4 ETW000 R3trans was called as follows: r3trans -d

4 ETW000  trace at level 2 opened for a given file pointer

4 ETW000  [     dev trc,00000]  Tue Aug 05 17:50:30 2014                                                 149  0.000149

4 ETW000  [     dev trc,00000]  db_con_init called                                                        27  0.000176

4 ETW000  [     dev trc,00000]  set_use_ext_con_info(): rsdb/ssfs_connect not set => ssfs not used        41  0.000217

4 ETW000  [     dev trc,00000]  determine_block_commit: no con_hdl found as blocked for con_name = R/3

4 ETW000                                                                                                  28  0.000245

4 ETW000  [     dev trc,00000]  create_con (con_name=R/3)                                                 14  0.000259

4 ETW000  [     dev trc,00000]  Loading DB library 'dboraslib.dll' ...                                    44  0.000303

4 ETW000  [     dev trc,00000]  DlLoadLib success: LoadLibrary("dboraslib.dll"), hdl 0, addr 000007FEF1650000

4 ETW000                                                                                                 947  0.001250

4 ETW000  [     dev trc,00000]      using "E:\usr\sap\TST\SYS\exe\uc\NTAMD64\dboraslib.dll"               18  0.001268

4 ETW000  [     dev trc,00000]  Library 'dboraslib.dll' loaded                                            19  0.001287

4 ETW000  [     dev trc,00000]  function DbSlExpFuns loaded from library dboraslib.dll                    23  0.001310

4 ETW000  [     dev trc,00000]  Version of 'dboraslib.dll' is "720.00", patchlevel (0.215)               242  0.001552

4 ETW000  [     dev trc,00000]  function dsql_db_init loaded from library dboraslib.dll                   26  0.001578

4 ETW000  [     dev trc,00000]  function dbdd_exp_funs loaded from library dboraslib.dll                  34  0.001612

4 ETW000  [     dev trc,00000]  New connection 0 created                                                 237  0.001849

4 ETW000  [     dev trc,00000]  0: name = R/3, con_id = -000000001, state = DISCONNECTED, tx = NO , bc = NO , hc = NO , perm = YES, reco = NO , frco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO , prog = 

4 ETW000                                                                                                  56  0.001905

4 ETW000  [     dev trc,00000]  db_con_connect (con_name=R/3)                                             20  0.001925

4 ETW000  [     dev trc,00000]  determine_block_commit: no con_hdl found as blocked for con_name = R/3

4 ETW000                                                                                                  25  0.001950

4 ETW000  [     dev trc,00000]  find_con_by_name found the following connection:                          16  0.001966

4 ETW000  [     dev trc,00000]  0: name = R/3, con_id = 000000000, state = DISCONNECTED, tx = NO , bc = NO , hc = NO , perm = YES, reco = NO , frco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO , prog = 

4 ETW000                                                                                                  36  0.002002

4 ETW000  [     dev trc,00000]  -->DbSlConnect(vers=-1, type=0, env='[default env]', userid='[default usr]')

4 ETW000                                                                                                  53  0.002055

4 ETW000  [     dev trc,00000]  DIR_CLIENT_ORAHOME is not set as profile nor env. parameter.             574  0.002629

4 ETW000                        Assuming using instant client with unspecified location.

4 ETW000  [     dev trc,00000]  Register application info.                                                97  0.002726

4 ETW000  [     dev trc,00000]  Oracle client version: 10.2.0.4.0, (dbsl 720 220612, UNICODE[2])       10033  0.012759

4 ETW000  [     dev trc,00000]  Detailed Oracle client (patch) info not available.                        30  0.012789

4 ETW000  [     dev trc,00000]    Default connection: tnsname ='TST'                                      73  0.012862

4 ETW000  [     dev trc,00000]  -->oci_initialize(con=0, char='UTF16', nchar='AL16UTF16', nls=-1),uc_ln=2

4 ETW000                                                                                                  50  0.012912

4 ETW000  [     dev trc,00000]  OCIEnvCreate(mode=16384=OCI_UTF16) returned 0 (for default NLS handle)

4 ETW000                                                                                                1828  0.014740

4 ETW000  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_ERROR, con=0, ErrHp=027944B0)                 58  0.014798

4 ETW000  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_ERROR, con=0, ErrBt=02796088)                 36  0.014834

4 ETW000  [     dev trc,00000]  NLS env. settings: lang=41004D00450052004900430041004E00                 167  0.015001

4 ETW000  [     dev trc,00000]                     terr=41004D0045005200490043004100                      29  0.015030

4 ETW000  [     dev trc,00000]                     char=55005400460031003600                              22  0.015052

4 ETW000  [     dev trc,00000]  Client NLS setting (by OCINlsGetInfo(con=0)): 'AMERICAN_AMERICA.UTF16'

4 ETW000                                                                                                  43  0.015095

4 ETW000  [     dev trc,00000]  charset='UTF16', ncharset='AL16UTF16', UNI_ASC=FALSE                      19  0.015114

4 ETW000  [     dev trc,00000]  Logon as OPS$-user to get SAPSR3's password                               28  0.015142

4 ETW000  [     dev trc,00000]  Connecting as /@TST on connection 0 (nls 0) ... (dbsl 720 220612, UNICODE[2])

4 ETW000                                                                                                  61  0.015203

4 ETW000  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_SVCCTX, con=0, svchp=02795F48)               370  0.015573

4 ETW000  [     dev trc,00000]  Allocating server context handle                                          31  0.015604

4 ETW000  [     dev trc,00000]  Attaching to database server TST (con=0, svchp=02795F48, srvhp=0279EFF8)

4 ETW000                                                                                                  74  0.015678

4 ETW000  [     dev trc,00000]  Assigning server context 0279EFF8 to service context 02795F48          30008  0.045686

4 ETW000  [     dev trc,00000]     OCIHandleAlloc(OCI_HTYPE_SESSION, con=0, usrhp=027ABBE0)               86  0.045772

4 ETW000  [     dev trc,00000]  Starting user session: OCISessionBegin(con=0, usr='/', svc=02795F48, srv=0279EFF8, usr=027ABBE0)

4 ETW000                                                                                                  40  0.045812

4 ETW000  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with -1=OCI_ERROR               28164  0.073976

4 ETW000  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:               95  0.074071

4 ETW000  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                       

4 ETW000                                                                                                  39  0.074110

4 ETW000  [    dbsloci.,00000]  *** ERROR => CONNECT failed with sql error '1017'                         38  0.074148

4 ETW000  [     dev trc,00000]     set_ocica() -> SQL error code 1017                                     19  0.074167

4 ETW000  [     dev trc,00000]  -->oci_get_errmsg (con=0, rc=1017)                                        20  0.074187

4 ETW000  [     dev trc,00000]     OCIErrorGet -> SQL error code: 1017                                    57  0.074244

4 ETW000  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                       

4 ETW000                                                                                                  39  0.074283

4 ETW000  [     dev trc,00000]  Info: 99=DBSL_ERR_DB, oerr=1017, try to connect with default password.

4 ETW000                                                                                                  38  0.074321

4 ETW000  [     dev trc,00000]  Connecting as SAPSR3/<pwd>@TST on connection 0 (nls 0) ... (dbsl 720 220612, UNICODE[2])

4 ETW000                                                                                                  30  0.074351

4 ETW000  [     dev trc,00000]  Assigning username to user session: con=0, usrhp= 027ABBE0                23  0.074374

4 ETW000  [     dev trc,00000]  Assigning password to user session: con=0, usrhp=027ABBE0                 36  0.074410

4 ETW000  [     dev trc,00000]  Starting user session: OCISessionBegin(con=0, usr='SAPSR3', svc=02795F48, srv=0279EFF8, usr=027ABBE0)

4 ETW000                                                                                                 133  0.074543

4 ETW000  [     dev trc,00000]  Tue Aug 05 17:50:40 2014                                             9992977  10.067520

4 ETW000  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with -1=OCI_ERROR                  33  10.067553

4 ETW000  [     dev trc,00000]     OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:               85  10.067638

4 ETW000  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                       

4 ETW000                                                                                                  37  10.067675

4 ETW000  [    dbsloci.,00000]  *** ERROR => CONNECT failed with sql error '1017'                         29  10.067704

4 ETW000  [     dev trc,00000]     set_ocica() -> SQL error code 1017                                     19  10.067723

4 ETW000  [     dev trc,00000]  -->oci_get_errmsg (con=0, rc=1017)                                        18  10.067741

4 ETW000  [     dev trc,00000]     OCIErrorGet -> SQL error code: 1017                                    55  10.067796

4 ETW000  [     dev trc,00000]     ORA-01017: invalid username/password; logon denied                                                                       

4 ETW000                                                                                                  52  10.067848

4 ETW000  [     dev trc,00000]     DbSlConnect(con=0) -> orc=0, 99=DBSL_ERR_DB                            22  10.067870

4 ETW000  [    dblink  ,00000]  ***LOG BY2=>sql error 1017   performing CON                               58  10.067928

4 ETW000  [    dblink  ,00000]  ***LOG BY0=>ORA-01017: invalid username/password; logon denied            22  10.067950

2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_ora_tnsname = 'TST'"

Best Regards

~Amal Aloun

Reagan
Advisor
Advisor
0 Kudos

Check who the owner of the table SAPUSER is.

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

Former Member
0 Kudos

Dear Reagan;

SQL> SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

OWNER

------------------------------

SYS

OPS$AMCAN-PRD\TSTADM

TSTADM is the sid user for the system where I am doing the refresh for.

Best Regards

~Amal Aloun

Reagan
Advisor
Advisor
0 Kudos

Drop the table owned by SYS.

DROP TABLE "SYS".SAPUSER;

Change the password using BR*Tools.

brconnect -u / -f chpass -o SAPSR3 -p passw0rd

Check whether R3trans -d ends with 0000. If not then provide the output of the below query.

SELECT USERNAME FROM DBA_USERS;

Note: If you want to maintain the confidentiality of the system details then provide the output in text format and rename the SID and hostname details with a fake one.

Cheers

RB

Former Member
0 Kudos

What is the return code when you run r3trans -d ?

I guess you are not running the proper command to change the password plz check this link


https://help.sap.com/saphelp_nw70ehp2/helpdata/en/4f/c3883989676778e10000000a11402f/content.htm

Regards,

Pavan

divyanshu_srivastava3
Active Contributor
0 Kudos

Your schema user is SAPSR3 and you have used PSAPSR3.

Correct the name in query and reset the password.

divyanshu_srivastava3
Active Contributor
0 Kudos

Stop SAP.

Recreate user using - 50088 - Creating OPS$ users on Windows NT/Oracle

brconnect -u system/<password> -f chpass -o <sapowner> -p <password>

Rerun R3trans -d

Try again

Former Member
0 Kudos

Dear;

I used the command brconnect -u system/Amcan1234 -f chpass -o sapsr3 -p Amcan1234 as you recommended as you can see below:

But I am still getting the error ORA-01017: invalid username/password; logon denied when I run R3trans -d

Best Regards

~Amal Aloun

divyanshu_srivastava3
Active Contributor
0 Kudos

Can you check

SQLNET.AUTHENTICATION_SERVICES = (NTS) in sqlnet.ora

os_authent_prefix = ops$ in initsdi.ora

Reagan
Advisor
Advisor
0 Kudos

Follow this SAP note.

400241 - Problems with ops$ or sapr3 connect to Oracle

Again, recreate the OPS$ users using the script attahched in the above SAP note.

Regards

RB

divyanshu_srivastava3
Active Contributor
0 Kudos

Also, try to troubleshoot using 620540 - Authentication Troubleshooting Guide

I hope you will enjoy in resolving this isse

former_member188883
Active Contributor
0 Kudos

Hi Amal,

Please execute below command with SAPSR3 password as "sap"

brconnect -u system/<password> -f chpass -o <sapowner> -p <password>


Check R3trans -d and post the results.


Regards,

Deepak Kori

Former Member
0 Kudos

Dear Deepak;

The content of sqlnet.ora is

################

# Filename......: sqlnet.ora

# Created.......: created by SAP AG, R/3 Rel. >= 6.10

# Name..........:

# Date..........:

# @(#) $Id: //bas/720_REL/src/krn/tpls/ora/SQLNET.ORA#1 $

################

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

NAMES.DEFAULT_DOMAIN = WORLD

# 05.01.06 unsorported parameter now

#NAME.DEFAULT_ZONE = WORLD

# 05.01.06 set the default to 10

SQLNET.EXPIRE_TIME = 10

SQLNET.AUTHENTICATION_SERVICES = (NTS)

# 05.01.06 set to default

#TCP.NODELAY=YES

# 05.01.06 set to 32768

DEFAULT_SDU_SIZE=32768

Shall I add the parameter os_authent_prefix = ops$ in initsdi.ora to my sqlnet.ora ?

Best Regards

~Amal Aloun

former_member188883
Active Contributor
0 Kudos

Hi Amal,

Do not perform any change in sqlnet.ora .

Execute the brconnect command with SAPSR3 password as "sap".

Regards,

Deepak Kori

Former Member
0 Kudos

Hi Amal,

Try using  this command

brconnect -u system/<your_system_password> -c -f chpass –u SAPSR3 -p <new_password>

Thanks,

Pavan

Reagan
Advisor
Advisor
0 Kudos

Why don't you just download the script attached to this SAP note and recreate the OPS$ users.

50088 - Creating OPS$ users on Windows NT/Oracle

Regards

RB

former_member188883
Active Contributor
0 Kudos

Hi Amal,

I used brconnect -u / -f chpass -o PSAPSR3 -p sap so i can reset the password, but still I am getting the same invalid username or password error.

Have you changed password for  PSAPSR3 or SAPSR3 ?

Is the schema for QAS and PRD same before refresh ?

Regards,

Deepak Kori

Former Member
0 Kudos

Dear Deepak;

I used the command Brconnect -u system/Amcan1234 -f chpass -o SAPSR3 -p

as you can see below:

in the previous command that was a miss type from my side.

Both schemas on both systems are the same.

Best Regards

~Amal Aloun