database connection issue caused by wrong TNS_ADMIN setting

Users reported their applications could not connect to databases, and thus stopped working after their databases were patched last weekend. They got “ORA-28040: No matching authentication protocol” error either from the front end or the log files.  This error is related with SQLNET.ALLOWED_LOGON_VERSION, which is used to specify which authentication protocols are allowed by the client or database. If the client and database server do not have at least one matching version, then authentication fails with an error. For more information, please refer to: http://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm#BIIEAFHA

However, no one touched the sqlnet.ora in both client and server side during patching.  TNS_ADMIN  is not supposed to be set on database server side,  and the file sqlnet.ora in $ORACLE_HOME/network/admin doesn’t include SQLNET.ALLOWED_LOGON_VERSION parameter. So things look weird.

Luckily, Oracle provides a procedure called get_env in  dbms_system package , which can be used to assist the investigation under such situation.  Environment variables including TNS_ADMIN and ORACLE_HOME can be retrieved from the server  by dbms_system.get_env.

sqlplus  'sys/xxx@xxx</a> as sysdba'

SQL> var tmp_str varchar2(255);
SQL> exec dbms_system.get_env('ORACLE_HOME',:tmp_str);

PL/SQL procedure successfully completed.

SQL> print tmp_str

TMP_STR
--------------------------------------------------------------------------------
/orabin/rdbms/10203

SQL> exec dbms_system.get_env('TNS_ADMIN',:tmp_str);

PL/SQL procedure successfully completed.

SQL> print tmp_str

TMP_STR
--------------------------------------------------------------------------------
/orabin/rdbms/11.2.0.3/network/admin

From above output, we can see the TNS_ADMIN was set to
/orabin/rdbms/11.2.0.3/network/admin while oracle_home was set to /orabin/rdbms/10203, however, we are not supposed to use this configuration. Obviously someone set TNS_ADMIN by mistake.

I did see SQLNET.ALLOWED_LOGON_VERSION is set to 10 in the sqlnet.ora under /orabin/rdbms/11.2.0.3/network/admin,  which is the root cause of our database connection issue.  After correcting the settings, all applications are able to login the databases.