Wednesday, June 29, 2022

ORA-01017: incorrect username/password, login denied when using a wallet



If you find yourself in the situation where you cannot make a connection using your wallet because of the error
ORA-01017: incorrect username/password, login denied when using a wallet
It might be worth checking that the user you have created an entry in your wallet for, also is granted the sysdba role.
Example:
I want to use a database user called "dbamaster" to performa a clone from active database over the network.
Since I do not wish to reveal the password of the "dbamaster" user, I add the user to a wallet, and then use the notation
connect target /@proddb
connect auxiliary /@testdb
to connect.

Listing the content of the wallet shows that everything is in order:
mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
2: testdb dbamaster
1: proddb dbamaster
You can connect successfully to both databases using the "dbamaster" user, using TNS:
-- From auxiliary to target
sqlplus dbamaster@proddb

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 20:03:09 2022
Version 19.15.0.0.0

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

Enter password:

Koblet til:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

DBAMASTER@proddb>

-- From target to auxiliary
sqlplus dbamaster@testdb

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 20:43:43 2022
Version 19.15.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

DBAMASTER@testdb SQL>


Everything seems in order, but incorrect password nevertheless.

Check the v$pwfile_users view, which holds the current users that have sysdba privilege:
col account_status format a20
col username format a20
col password_profile format a20
select username,sysdba,sysoper,account_Status,password_profile from v$pwfile_users;

USERNAME             SYSDB SYSOP ACCOUNT_STATUS       PASSWORD_PROFILE
-------------------- ----- ----- -------------------- --------------------
SYS                  TRUE  TRUE  OPEN                 NOEXP

In this case user "dbamaster" was missing.

Add it:
grant sysdba to dbamaster;

Check the list of sysdbas again. You should now have two entries there:
 select username,sysdba,sysoper,account_Status,password_profile from v$pwfile_users;

USERNAME             SYSDB SYSOP ACCOUNT_STATUS       PASSWORD_PROFILE
-------------------- ----- ----- -------------------- --------------------
SYS                  TRUE  TRUE  OPEN                 NOEXP
DBAMASTER            TRUE  FALSE OPEN                 NOEXP

Your connections should now work:
RMAN> connect target /@proddb

connected to target database: proddb (DBID=253631174)

RMAN> connect auxiliary /@testdb

connected to auxiliary database: TESTDB (DBID=296823958)

No comments:

Post a Comment