Troubleshoot ORA-01017 for database login when Database is configured with EUS

This post cover steps on how to troubleshoot Database Login issue (ORA-01017 Invalid Username/Password) where Oracle Database is registered with LDAP Server (OID or Microsoft Active Directory) for Enterprise User Security.

  • For basic overview of Enterprise User Security click here
  • Steps to configure Enterprise User Security are here 

Steps to troubleshoot login issues in Oracle Database with EUS

1. Enable Trace
SQL> alter system set events ‘28033 trace name context forever, level 9’;

2. Try connecting from SQLPLUS using enterprise user (User in LDAP server i.e. OID/AD user)

3. Check logs in user_dump_dest location (Default location in 11g database is $ORACLE_BASE/diag/rdbms/$SID/$SID/trace )

In my case error was

_________
kzld_discover received ldaptype: OID
KZLD_ERR : failed to get cred from wallet
KZLD_ERR : Failed to bind to LDAP Server Err=28032
KZLD_ERR : 28032
KZLD is doing LDAP unbind
KZLD_ERR: found err from kzldini
_________

4. Disable trace

SQL> alter system set events ‘28033 trace name context off’;

.

Based on error message it is easy to troubleshoot (only if you know request flow and how EUS works)

.

Flow for Enterprise User Login using SQLNET for Database

Here is flow when you issue “sqlplus ldap_user/ldap_user_password@Alias” to connect to database

1. Checks entry in sqlnet.ora (under $TNS_ADMIN, if not set then uses $ORACLE_HOME/network/admin) 

Entry in ldap.ora like NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES)  – here first entry is LDAP which means ldap.ora is used as first option to find database server, port, SID/SERVICE

Entry in ldap.ora looks like
DIRECTORY_SERVERS=(ldap_server:Non_SSL_port:SSL_port)
DEFAULT_ADMIN_CONTEXT=”dc=mydomain,dc=com”

2. Uses wallet (secured file based repository to store credentials to connect to OID) . More on wallet here

3. Default wallet location in database is $ORACLE_BASE/admin/$SID/wallet and stores username as LDAP entry cn=$SID,cn=OracleContext,[dc=mydomain,dc=com] and password which is used to connect to LDAP/OID server. (based on database parameter LDAP_DIRECTORY_ACCESS)

In my case ORACLE_BASE was not set properly during startup hence database couldn’t find wallet location.

.

Steps to change database password used to connect to OID
– Start DBCA (Database Configuration Assistance )
a) select Configure Database Options in a Database, and click Next
b) Select a database and click Next.
c) Select Regenerate database password
d)Enter the LDAP server username/password and a password for the database wallet. Click OK.
e) Click Finish if you are only regenerating the password.

4. Once database is connected to OID/LDAP using  cn=$SID,cn=OracleContext,[dc=mydomain,dc=com] and password stored in wallet, it check if user exists in LDAP/OID server.

5.  User-to-Schema mapping : Once user exists in LDAP/OID then it checks user to schema mapping (user to schema can be 1 to 1 or many to 1).

6.  User-to-Group : Once users schema is identified then it searches for all groups which this user is member of.

7.  Group-to-Enterprise Role mapping : Once all groups are identified then it check group-to-enterprise role mapping to find all enterprise roles which this user is entitled.

8.Enterprise Role-to-Global Database Role mapping : Once enterprise roles assigned to this user are identified then global database role assigned to this user is identified using “Enterprise Role-to-Global Database Role mapping”

9. LDAP/OID returns user back to database with schema and global database role assigned to this user.

.

Related/References

  • 453853.1  Step by Step Guide To Troubleshooting 10g Enterprise User Security (EUS) – Password Authentication
  • 783502.1  EUS Authentication Fails With ORA-28030
  • 261178.1  Enterprise User Security Configuration: Resolving ORA-28030 Errors
  • EUS Stop working – Blog from Jacco H. Landlust 

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

4 comments
ishakfc71 says January 18, 2011

hi,

Check logs in user_dump_dest location (Default location in 11g database is $ORACLE_BASE/diag/rdbms/$SID/$SID/trace )

what about 10g database where should i look
$ORACLE_BASE /admin/$SID

in this folder
adump,bdump,cdump,dpdump,hdump,udump

what is the differences in this folders

thanks

Reply
Atul Kumar says January 18, 2011

@ ishakfc71,
Trace dumps in 10g DB are in background_dump_dest (bdump) usually (or possibly in user_dump_dest – udump )

To check this location login to database

SQL>show parameter dump

Reply
» Unable to login to Database with Enterprise (OID) User (EUS): “KZLD_ERR: Failed to bind to LDAP server Err=49″ Online Apps DBA: One Stop Shop for Apps DBA’s says February 9, 2011

[…] First thing to do is enable trace in database as explained here In my case error message is _________ kzld found pwd in wallet KZLD_ERR: Failed to bind to LDAP […]

Reply
Nallamuthu Sampantham says February 9, 2012

Excellant article on trouble shooting LDAP. Thanks

Reply
Add Your Reply

Not found