Basically, with 12.2 default behavior – there is NO default authentication method left after setting case sensitivity to false – just set it back to true – see Oracle Support Doc ID 2040705.1

Additionally, the following Doc IDs supply additional info:

1957995.1 and 1304142.1

alter system sec_case_sensitive_logon=true scope=both; — then use case sensitive passwords or

or

Add this to your $ORACLE_HOME/network/admin

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

and as he says below:

With this solution you will also need to change the user password again so the DBA_USERS.PASSWORD_VERSIONS will get a 10G value, however the DES based verifiers are outdated and should only be used in exceptional cases when legacy client applications still need it.

Reference (thank you) Blog from:

http://dbahou.blogspot.com/2016/07/ora-01017.html

ORA-01017

To BottomTo Bottom

Oracle Database – Enterprise Edition – Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

All database authenticated users are getting the error: ORA-01017: invalid username/password; logon denied, even if we are sure the password should be correct.
When you inspect the DBA_USERS.PASSWORD_VERSIONS you only see 11G and 12C values but not 10G.

Changes

This may happen after an upgrade to 12c version 12.1.0.2 or 12.2
In Oracle release 12.2, the default value for the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is “12”, i.e. Exclusive Mode.

Cause

Incompatible settings of database parameter sec_case_sensitive_logon = false and sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION=12 (which becomes default in 12.2)
The combination of using sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION=12 together with database parameter sec_case_sensitive_logon = false is causing this issue:
– The sqlnet.ora parameter causes the 10G value (which would need to store the case insensitive password) to remain unused.
– Parameter sec_case_sensitive_logon = false prevents the use of the case sensitive passwords (11G and 12C values in DBA_USERS.PASSWORD_VERSIONS).
This means there’s no valid authentication method left.

Solution


– Switch database parameter sec_case_sensitive_logon = true
This is the recommended solution because it will use more secure password verifiers and also does not require a password change.
or
– Unset sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION (pre 12.2)
– Or set parameter SQLNET.ALLOWED_LOGON_VERSION to a lower version (12.2)
With this solution you will also need to change the user password again so the DBA_USERS.PASSWORD_VERSIONS will get a 10G value, however the DES based verifiers are outdated and should only be used in exceptional cases when legacy client applications still need it.

Oracle Support Note:

11g and Older: How To Use the Parameter SQLNET.ALLOWED_LOGON_VERSION Correctly (Doc ID 1304142.1) To BottomTo Bottom

APPLIES TO:

Oracle Net Services – Version 9.2.0.8 to 11.2.0.4 [Release 9.2 to 11.2]
Oracle Database – Enterprise Edition – Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
This documented is limited in scope to versions below 12.1. For versions 12c and newer, please see: Note 1957995.1 12c: ORA-28040 After Upgrade: No Matching Authentication Protocol
GOAL

Understand how the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION works in 11g and older versions of Oracle.

SOLUTION

There’s a common misconception about the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION and how it affects the version of clients that are allowed to connect to a database for specific values of this parameter.

The parameter actually specifies the AUTHENTICATION PROTOCOL (for example SHA-1) that a client is allowed to use, NOT the actual VERSION of that client.

Example: SQLNET.ALLOWED_LOGON_VERSION=10

Even though the parameter value implies Oracle version 10 the internal check is really against the authentication protocol ‘SHA-1’.

In earlier Oracle versions this was a one-on-one relation, ie. a certain Oracle client version used a specific authentication protocol.
Starting with Oracle 10g, this is no longer a one-on-one relation: Oracle 10g and 11g both use the SHA-1 protocol.
Oracle 12c will use the SHA-2 protocol.

This means that there’s no way to prevent 10g client connections and allowing only 11g client connections through the use of this parameter, because both use the same authentication protocol SHA1. This also means that both client versions are ‘equally secure’ – there’s no gain in security by trying to prevent 10g clients alone.

The documentation of this parameter is unclear, so Doc
bug 11845659 – sqlnet.allowed_logon_version needs clearer documentation
has been created to fix the description of this parameter.
Important note:

The SQLNET.ALLOWED_LOGON_VERSION parameter is deprecated in Oracle Database 12c.
This parameter has been replaced with two new Oracle Net Services parameters:

SQLNET.ALLOWED_LOGON_VERSION_SERVER
SQLNET.ALLOWED_LOGON_VERSION_CLIENT

See Oracle Database Net Services Reference for information

See also: Note 1957995.1 12c: ORA-28040 After Upgrade: No Matching Authentication Protocol

REFERENCES

NOTE:402193.1 – How to Allow Login to Database Based on the Client Version
BUG:11845659 – SQLNET.ALLOWED_LOGON_VERSION NEEDS CLEARER DOCUMENTATION

Leave a Reply

Your email address will not be published. Required fields are marked *