12.2: ORA-28040 Followed by ORA-1017 When Client is Under Version 12

╰半夏微凉° 2022-05-31 06:06 173阅读 0赞

## Applies to: ##

Oracle Net Services - Version 12.2.0.1 and later  
Information in this document applies to any platform.

## Symptoms ##

Following a recent database upgrade to 12.2, an 11gR2 client might suddenly start to fail to connect to the database with an error ORA-28040.

This is a common problem with 12c and newer instances.  In this case, the ORA-28040 is easily resolved with the server side  
sqlnet.ora file setting for SQLNET.ALLOWED\_LOGON\_VERSION\_SERVER=10 or 11.  
It's important to note that the default setting for this parameter at the 12.2 server has been changed from 11 in version 12.1.  
The default in version 12.2 is now '12'.  So a client that might have been able to connect to 12.1 may start to fail after an  
upgrade to 12.2.

However, once the ORA-28040 error is cleared, the client still cannot connect and a persistent ORA-1017 is returned.  
This is despite full confidence in the credentials being supplied by the user.  
  
ORA-28040: No matching authentication protocol

ORA-01017: invalid username/password

## Changes ##

This is likely a new installation of version 12.2 Oracle database.

## Cause ##

ORA-28040 is thrown because the default setting for allowed logon version in 12.2 has been changed from 11 to 12.  
(see above)  
Note:  An 11.2.0.1 client is unable to use the algorithm for 12 or 11 so a lower setting (10) is necessary.  
If the client is version 11.2.0.3 or higher, the server setting of 11 should work.  The setting of 12 will not.  
  
In addition to this new value for ALLOWED LOGON, the 12.2 database no longer allows case insensitive passwords.

SQL>show parameter SEC\_CASE\_SENSITIVE\_LOGON;

Should NOT be true in 12.2.

See also:  
   [Note: 2040705.1][Note_ 2040705.1] Lockout of all database authenticated users getting error ORA-01017: invalid username/password; logon denied   
    
  
With this solution you will also need to change the user password again so the DBA\_USERS.PASSWORD\_VERSIONS will get a 10G value.  
Note that the DES based verifiers are outdated and should only be used in exceptional cases when legacy client applications still need it.  
  
   
See [Note: 2075401.1][Note_ 2075401.1]  The new Exclusive Mode default for password-based authentication in Oracle 12.2 conflicts with case-insensitive password configurations. All user login fails with ORA-1017 after upgrade to 12.2

## Solution ##

1) Set SQLNET.ALLOWED\_LOGON\_VERSION\_SERVER at server side sqlnet.ora file to 10 for 11.2.0.1 client.  
(11.2.0.3 client should be able to use a setting of 11)

**The default location for the sqlnet.ora file that is referenced by the database is not GRID.  It's RDBMS\_HOME/network/admin.**  
  
2) Make sure the CASE\_SENSITIVE\_PASSWORD parameter is set to TRUE (FALSE is no longer allowed in 12.2)

SQL> alter system set SEC\_CASE\_SENSITIVE\_LOGON=true;  
  
3) Change password for the user AFTER this setting is in place.  If userid/password was created PRIOR to this setting,  
changing it again may be necessary as this setting impacts the algorithm.  
  
SQLNET.ALLOWED\_LOGON\_VERSION\_SERVER=10  
(11 if client version is 11.2.0.3 or newer)

[Note_ 2040705.1]: https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=2296947.1&id=2040705.1
[Note_ 2075401.1]: https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=2296947.1&id=2075401.1