Troubleshooting an enterprise user security database login and sudden ORA-12154

I encountered a new problem last week while trying to log in into LDAP-enabled Oracle Database (Enterprise User Security). I entered my SQL*Plus credentials that usually work fine, but received an error ending with ORA-12154.

$ sqlplus aprokopjevs@EBSPROD

SQL*Plus: Release 12.1.0.2.0 Production on Fri May 3 06:47:29 2019

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

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name: ^C

$

All client-side TNS configuration and server-side SCAN listener verifications were successful and did not show any problems. When I entered a dummy password for my user ID, I got an expected "ORA-01017: invalid username/password; logon denied".

Event "28033 trace name context forever, level 9" trace (usually is used to troubleshoot EUS/LDAP problems in the database) was not generating any trace file. But for the dummy password attempt I got:

*** 2019-05-03 07:20:22.678
*** SESSION ID:(1400.37219) 2019-05-03 07:20:22.678
*** CLIENT ID:() 2019-05-03 07:20:22.678
*** SERVICE NAME:(SYS$USERS) 2019-05-03 07:20:22.678
*** MODULE NAME:(sqlplus@ebsdb.domain.com (TNS V1-V3)) 2019-05-03 07:20:22.678
*** CLIENT DRIVER:() 2019-05-03 07:20:22.678
*** ACTION NAME:() 2019-05-03 07:20:22.678

KZLD_ERR: 0
kzld_search -s sub -b cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=domain,dc=com
search filter: (&(objectclass=orcldbenterprisedomain_82)(uniqueMember=cn=ebsprod,cn=OracleContext,dc=domain,dc=com))
KZLD_ERR: 0
kzldsp found policy ALL
kzld_search -s base -b cn=Common,cn=Products,cn=OracleContext,dc=domain,dc=com
search filter: objectclass=*
KZLD_ERR: 0
kzld found uid for orclCommonNicknameAttribute
kzldsearch_ext -s sub -b cn=Users,dc=domain,dc=com
search filter: uid=aprokopjevs
number of entries: 1
KZLD_ERR: 0
KZLD_ERR: Got Refresh Account control
kzld found user entry: cn=aprokopjevs,cn=Users,dc=domain,dc=com
KZLD is doing LDAP unbind

As you can see, everything was fine with the LDAP layer setup. But all attempts to get a proper trace for a valid password input failed. It looked like I was not reaching the DB session establishment at all.

But everything worked with SQL Developer JDBC based connections.

I started to review possible recent changes that could have affected this, and the only change that occurred was my password update due to expiration. Also, my teammate suggested that there are known issues related to special character use in database credentials. So I started to research this. And, initially, confirmed that it was a special character problem - everything worked as expected without them.

But I had special characters before (hello security policies). So what happened? I changed it using the password generator, and "@" showed up.

The first guess - crypto:O3LOGON and its limitations (< 9iR2 standard). We have to use this old hashing because of some former clients/databases that are still in use by the end users.

$ ldapsearch -h oidhost -p 1389 -D "cn=orcladmin" -w "${ldap_password}" -L -b "cn=DBSecurityProfileEntry,cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=domain,dc=com" -s sub "(objectclass=*)" "*"
dn: cn=DBSecurityProfileEntry,cn=OracleDBSecurity,cn=Products,cn=OracleContext
 ,dc=domain,dc=com
cn: DBSecurityProfileEntry
objectclass: top
objectclass: orclpwdverifierprofile
orclappid: orcldbsecurity
orclpwdverifierparams;orclpasswordverifier: crypto:O3Logon
$

Pre-11g database versions do not support non-ASCII passwords and have limitations for special characters supporting only _, # and $. Starting with 11g, there is broader support for introducing SHA crypto hashing, and for non-ASCII NLS_LANG to be set either to UTF8 either AL32UTF8.

I have tried to change the crypto to SHA, set NLS_LANG to AL32UTF8, but still, our 12c database responded only with ORA-12154 TNS:could not resolve the connect identifier specified. So nothing has changed.

Next, I figured out and confirmed that only $#_ are allowed. I tried all special characters that I could find on the keyboard. And, surprise, problems occurred only with @ returning ORA-12154 and / returning SQL*Plus syntax errors. All other special characters worked without any problems. It looks like the $#_ limit is not a problem for external users.

I tried to use quotes - and it worked. So, if your password, following the security restrictions, has to have special characters and you use @ or /, and you use SQL*Plus, you have to enter your password input in quotes.

$ sqlplus user@db

Enter password: " + <your password with @ or /> + "

OR

$ sqlplus user/\"<your password with @ or />\"@db

It is a pure SQL*Plus client issue. After some discussions with Maris Elsins I felt a bit dumb that I went into a horrible troubleshooting issue, ignoring an obvious fact - SQL*Plus doesn't like both @ and / as it is part of the syntax construct. And ORA-12154 is absolutely expected, as @ is used to point the TNS alias we want to connect to. I know this for a fact and somehow was expecting this to be a problem if we passed these two special characters in the command line, just like the password. I know that "Enter user-name:" prompt is sensitive too. But I wasn't expecting that the "Enter password:" prompt is also parsed internally as part of one single command line. And this is really odd, especially, to see this using 12cR1 Oracle database client.

I don't have a 19c database ready for a similar test (sorry, I am an Apps DBA, and EBS is not yet certified), and it will take some time to build the full stack of OID in a lab environment and configure the Enterprise User Security. Maybe a blog reader has had a similar stack and can comment on this?