标签归档：ORA-28040

电脑上面安装了三个版本的数据库10.2.0.3,11.2.0.1,12.1.0.2版本,使用他们分别尝试连接另外一个12.2.0.3的环境数据库发现只有12.1的版本客户端可以连接到12.2上面，其他版本报ORA-28040错误
分别测试连接，报ORA-28040错误

C:\Users\XIFENFEI>sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期三 7月 20 00:03:01 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL>
SQL>

C:\Users\XIFENFEI>D:\app\FAL\product\11.2.0\dbhome_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 7月 20 00:10:33 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-28040: No matching authentication protocol


C:\Users\XIFENFEI>D:\app\product\10.2.0\db_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 7月 20 00:09:30 2016

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-28040: 没有匹配的验证协议


请输入用户名:

ORA-28040错误说明

28040, 0000, "No matching authentication protocol"
// *Cause:  There was no acceptable authentication protocol for 
//          either client or server.
// *Action: The administrator should set the values of the
//          SQLNET.ALLOWED_LOGON_VERSION_SERVER and 
//          SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the
//          client and on the server, to values that match the minimum 
//          version software supported in the system. 
//          This error is also raised when the client is authenticating to 
//          a user account which was created without a verifier suitable for
//          the client software version. In this situation, that account's 
//          password must be reset, in order for the required verifier to
//          be generated and allow authentication to proceed successfully.

解决方法
在服务端的sqlnet.ora文件中加入上如下信息，然后重启监听

[oracle@ora1221 admin]$ vi sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

[oracle@ora1221 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.0.3 - Production on 17-JUN-2016 06:36:13

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@ora1221 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.0.3 - Production on 17-JUN-2016 06:36:17

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.2.0/db_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.0.3 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/ora1221/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1221)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.0.3 - Production
Start Date                17-JUN-2016 06:36:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora1221/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1221)(PORT=1521)))
The listener supports no services
The command completed successfully

sqlnet中参数说明
SQLNET.ALLOWED_LOGON_VERSION_SERVER 是服务端参数对于jdbc和oci都生效,该参数不是只具体数据库版本,而是指授权协议的版本
SQLNET.ALLOWED_LOGON_VERSION_CLIENT 是指作为客户端连接其他实例的时候生效,也是只授权协议版本,而且该参数只对oci生效,jdbc 需要通过在代码中类似实现

OracleDataSource ods = new OracleDataSource();
ods.setURL(jdbcURL);
ods.setUser("scott");
ods.setPassword("tiger");
Properties props = new Properties();
props.put("oracle.jdbc.allowedLogonVersion", 12);
ods.setConnectionProperties(props);
Connection con = ods.getConnection();

上述两个参数可以填写值
12a for Oracle Database 12c release 12.1.0.2 or later authentication protocols (strongest protection)
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended)
11 for Oracle Database 11g authentication protocols (default)
10 for Oracle Database 10g authentication protocols
9 for Oracle9i Database authentication protocol
8 for Oracle8i Database authentication protocol

---

具体描述请见:http://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF2010

再次测试连接

C:\Users\XIFENFEI>D:\app\FAL\product\11.2.0\dbhome_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 7月 20 00:20:21 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production 断开

C:\Users\XIFENFEI>D:\app\product\10.2.0\db_1\bin\sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 7月 20 00:20:28 2016

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL>

C:\Users\XIFENFEI>sqlplus sys/oracle@192.168.137.121/orcl12c2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on 星期三 7月 20 00:20:55 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

该问题在jdbc中也表现明显，建议参考Starting With Oracle JDBC Drivers (文档 ID 401934.1)和Client / Server Interoperability Support Matrix for Different Oracle Versions (文档 ID 207303.1)选择完全兼容性的客户端和jdbc版本,另外可以关注相关文章:
ORA-28040 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT for JDBC Thin Clients (文档 ID 2000339.1)
ORA-28040 Using JDBC Connection to 12c Database (文档 ID 2111118.1)
JDBC Version 10.2.0.4 Produces ORA-28040 Connecting To Oracle 12c (12.1.0.2) Database (文档 ID 2023160.1)
ORA-28040 and SQLNET.ALLOWED_LOGON_VERSION_CLIENT for JDBC Thin Clients (文档 ID 2000339.1)