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