Oracle 12c: SEC_CASE_SENSITIVE_LOGON and ORA-1017: invalid username/password; logon denied

In which situations you may receive an ORA-1017?

This is outlined in the Oracle 12.1 documentation already:

• “Ensure that the SEC_CASE_SENSITIVE_LOGON parameter is not set to FALSE if the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 or 12a. This is because the more secure password versions used for this mode only support case-sensitive password checking. For compatibility reasons, Oracle Database does not prevent the use of FALSE for SEC_CASE_SENSITIVE_LOGON when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a. Setting SEC_CASE_SENSITIVE_LOGON to FALSE when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a causes all accounts to become inaccessible.”

The key is the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER. And here’s the difference between Oracle Database 12.1 and Oracle Database 12.2:

• Oracle Database 12.1: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 11 out of the box
• Oracle Database 12.2: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 12 out of the box

Behavior difference Oracle 12.1 vs Oracle 12.2

See this simple example after switching SEC_CASE_SENSITIVE_LOGON=FALSE in both databases (as shown above):

Oracle Database 12.1.0.2:

SQL> alter user hr identified by hr; 

User altered. 

SQL> connect hr/hr Connected.

Oracle Database 12.2.0.1:

SQL> alter user hr identified by hr; 

User altered. 

SQL> connect hr/hr

 

ERROR: ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

How to resolve the ORA-1017 error?

The solution is very simple, first you need to edit your sqlnet.ora adding (or lowering) the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a value below 12. But if you try to connect directly after restarting your listener you will receive the same ORA-1017 again. The secret is mentioned in the above documentation link as well: you will have to restart the database along with listner and then recreate the user’s passwords if you need the logon process to work as it did work before Oracle Database 12.2.

sqlnet.ora:

# sqlnet.ora Network Configuration File: Oracle_Home/product/12.2.0.1/network/admin/sqlnet.ora 

# Generated by Oracle configuration tools.  

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11


Check in DBA_USERS:

SQL> select username, password_versions from DBA_USERS where username='HR'; 

USERNAME                              PASSWORD_VERSIONS 

-------------                         --------------------- 

HR                                    11G 12C

There’s no “10G” mentioned. This will prevent the connection.

SQL> connect hr/hr 

ERROR: ORA-01017: invalid username/password; logon denied 

Warning: You are no longer connected to ORACLE.

Still you are getting the same error.

Solution: First restart the database service along with listener service and then after restart, You will have to change the password again using ALTER command:

SQL> alter user hr identified by hr; 

User altered. 

SQL> select username, password_versions from DBA_USERS where username='HR'; 

USERNAME               PASSWORD_VERSIONS 

--------------         ---------------------- 

HR                     10G 11G 12C 

SQL> connect hr/hr 

Connected.