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.
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
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.
Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Specialising in Design, Implement, and Trainings.