Shulou(Shulou.com)05/31 Report--

This article focuses on "how to solve ORA-28040 and ORA-01017 errors in Oracle low-version client connections". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "how to solve Oracle low-version client connection reported ORA-28040 and ORA-01017 errors" bar!

The life cycle of Oracle 11g has been released, and 18c has been officially released. After installing Oracle 18c, if a lower version of the client connects to 18c, the following two errors will be reported:

ORA-28040: No matching authentication protocolORA-01017: invalid username/password; logon denied

They will appear one after another, and when the ORA-28040 error is resolved, the ORA-01017 error will occur. Here reproduce the error and provide a solution.

1. Problem recurrence

Database server version:

[oracle@www.cndba.cn dbs] $sqlplus / as sysdbaSQL*Plus: Release 18.0.0.0.0-Production on Mon Aug 27 06:42:49 2018Version 18.3.0.0.0Copyright (c) 1982, 2018, Oracle. All rights reserved.Connected to:Oracle Database 18c Enterprise Edition Release 18.0.0.0.0-ProductionVersion 18.3.0.0.0

Client 11.2.0.4, connection is normal:

C:/Users/Dave > sqlplus system/oracle@192.168.56.168:1522/daveSQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 31 09:24:53 2018Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 18c Enterprise Edition Release 18.0.0.0.0-ProductionSQL >

But 11.2.0.1 does not work:

D:/instantclient_11 > sqlplus system/oracle@192.168.56.168:1522/daveSQL*Plus: Release 11.2.0.1.0 Production on Friday August 31 10:51:52 2018Copyright (c) 1982, 2010, Oracle. All rights reserved.ERROR:ORA-28040: No matching authentication protocol2. Handling ORA-28040 errors

According to the MOS document (ID 755605.1), the ORA-28040 error needs to be added to the sqlnet.ora file of Oracle users (non-grid users):

SQLNET.ALLOWED_LOGON_VERSION=8

Or use a later version of the client.

But in fact, according to the MOS documentation (ID 2111876.1), versions later than Oracle 12c

The SQLNET.ALLOWED_LOGON_VERSION parameter has been deprecated and the following two parameters should be used instead:

SQLNET.ALLOWED_LOGON_VERSION_SERVER = n

SQLNET.ALLOWED_LOGON_VERSION_CLIENT = n

The default n here is 11. The first parameter is when the client connects to the server, and the second is when the client connects to other databases. For example, create a db link.

Other optional values are as follows:

12afor Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later12for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended) 11for Oracle Database 11g authentication protocols (default) 10for Oracle Database 10g authentication protocols8for Oracle8i authentication protocol

This is modified as follows:

[oracle@www.cndba.cn admin] $cat sqlnet.ora# sqlnet.ora Network Configuration File: / u01/app/oracle/product/18.3.0/db_1/network/admin/sqlnet.ora# Generated by Oracle configuration tools.NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8SQLNET.ALLOWED_LOGON_VERSION_SERVER= 8 [Oracle @ www.cndba.cn admin] $

After modification, it will take effect. The error in the connection is as follows:

C:/Users/Dave > sqlplus system/oracle@192.168.56.168:1522/daveSQL*Plus: Release 11.2.0.1.0 Production on Friday August 31 14:49:53 2018Copyright (c) 1982, 2010, Oracle. All rights reserved.ERROR:ORA-01017: invalid username/password; logon denied3. Handling ORA-01017 errors

From the error prompt, it seems that the user name or password is wrong, in fact, there is no problem with the user name and password here. The problem here is that the sqlnet we configured is not valid for pre-existing accounts, and they are still compatible with each other.

SQL > set pages 100SQL > select username,password_versions from dba_users USERNAME PASSWORD_VERSIONS-- SYS 11G 12CSYSTEM 11G 12COUTLN 11G 12CSYS$UMF 11G 12CDBSNMP 11G 12CAPPQOSSYS 11G 12CDBSFWUSER 11G 12CGGSYS 11G 12C

The solution here is to change the password for the user:

SQL > alter user sys identified by oracle;User altered.SQL > alter user system identified by oracle;User altered.

View the password version:

SQL > select username,password_versions from dba_users;USERNAME PASSWORD_VERSIONS-- SYS 11G 12CSYSTEM 10G 11G 12C

Note that although SYS has not changed here, 10G has been added to the version of SYSTEM. In fact, both users can now connect:

C:/Users/Dave > sqlplus system/oracle@192.168.56.168:1522/daveSQL*Plus: Release 11.2.0.1.0 Production on Friday August 31 14:58:35 2018Copyright (c) 1982, 2010, Oracle. All rights reserved. Connect to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0-ProductionSQL > C:/Users/Dave > sqlplus sys/oracle@192.168.56.168:1522/dave as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Friday August 31 14:58:54 2018Copyright (c) 1982, 2010, Oracle. All rights reserved. Connect to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0-ProductionSQL > here, I believe you have a deeper understanding of "how to solve Oracle low-version client connection ORA-28040 and ORA-01017 errors". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.