CompatibilityJDBCSQLNET

How to Resolve ORA-28040: No matching authentication protocol

ORA-28040

When I tried to connect a 12.1 database from an Oracle client 9.2, it failed with ORA-28040: No matching authentication protocol.

I knew there could be some interoperability problems between versions, especially when the version gap is big like this case, 9i to 12c. But I didn’t expect ORA-28040 to show up.

Same error may occur while you’re using old Oracle JDBC drivers, say ojdbc14.jar or below, to connect a 12c database. I’ll talk more about Oracle JDBC driver with ORA-28040 later in the post.

Let’s see the content of ORA-28040.

  • Description
  • ORA-28040: 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 ORA-28040 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.

The above explanation about ORA-28040 is pretty straightforward. But now the question is: Should we change server configuration or upgrade clients? My answer in short, either way works.

In this post, I will split the solutions to ORA-28040 into two main parts, one is for server side, the other is for client side.

  1. Server Side Solutions
  2. Client Side Solutions

Server Side Solutions

If you have the right to change the network configuration on the server side, then you have chances to solve ORA-28040 for all users in minutes.

Scenario 1: New Database is Acting as a Server

Oracle 9i Clients to 12c Server
Oracle 9i Clients to 12c Server

When your users refused to upgrade their old clients to connect a higher version of database, say 12c, ORA-28040 would become a frequent and typical error in your daily job.

Said clients could be an Oracle 9i server which acts as a client to connect to an Oracle 12c database server like below. ORA-28040 was thrown by sqlplus as usual:

ORA-28040 SQL*Plus Connect from 9i to 12c
ORA-28040 SQL*Plus Connect from 9i to 12c

After searching for some other solutions, I found an Oracle documentation about “Parameters for the sqlnet.ora File” is very helpful to explain ORA-28040. In which, it explains in what situation we should use SQLNET.ALLOWED_LOGON_VERSION_SERVER to be compatible with both ends of authentication protocol.

Purpose

To set the minimum authentication protocol allowed when connecting to Oracle Database instances.

Usage Notes

The term VERSION in the parameter name refers to the version of the authentication protocol, not the Oracle Database release.

If the client version does not meet or exceed the value defined by this parameter, then authentication fails with an ORA-28040: No matching authentication protocol error or an ORA-03134: Connections to this server version are no longer supported error.

Here is my solution to ORA-28040: Adding SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 to sqlnet.ora in 12.1 database server (not the old client).

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora
...
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Please note that, you should use sqlnet.ora at database-level, not grid-level if you’re in a RAC environment according to MOS Doc ID 562589.1. And I have confirmed that.

As for taking effect, you don’t have to restart listener, the new incoming connections will apply the new values. Just make sure the setting is correct.

This time, no ORA-28040 showed up.

Scenario 2: New Database is Acting as a Client

Oracle 12c Clients to 9i Server
Oracle 12c Clients to 9i Server

From the opposite direction, if you want to connect from a 12.1 to a 9.2 database, say database link connections, you should also set: SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

in sqlnet.ora to prevent unmatched authenticated protocol error ORA-28040.

For your reference, I quote some text about SQLNET.ALLOWED_LOGON_VERSION_CLIENT below. Same reason here for ORA-28040, it’s a compatible issue:

Purpose

To set the minimum authentication protocol allowed for clients, and when a server is acting as a client, such as connecting over a database link, when connecting to Oracle Database instances.

The connections from 9i to 12c can be worked around by the solutions provided in this post. In contrary, 12c to 9i usually fails due to ORA-03134, not ORA-28040.

ORA-03134: Connections to this server version are no longer supported.

I talked about the solution to ORA-03134 in another post:
How to Resolve ORA-03134: Connections to this server version are no longer supported.

Client Side Solution

Developers who don’t act as DBA may have no right to change SQLNET.ORA on the server. Therefore, changing or upgrading their own clients is probably a solution to ORA-28040. From now, we start to focus on clients. Especially for those who are using Oracle JDBC drivers.

Oracle JDBC Driver

Since Oracle 12.1 database claims that it is compatible with ojdbc6.jar or ojdbc7.jar, so ojdbc14.jar or below is no longer matching for clients to connect through the authenticated protocol of a 12.1 database. That’s why you see ORA-28040 in java.sql.SQLException error stack.

More information for JDBC developers can be found at Oracle Database JDBC Developer’s Guide: Version Compatibility for Oracle JDBC Drivers.

Now, let’s see some clients that generate ORA-28040 and how we handle it.

  1. SQL Developer
  2. PL/SQL Developer
  3. TOAD for Oracle
  4. SQuirreL SQL Client

SQL Developer

If you were using old SQL developer like this, you will see ORA-28040 whenever you connect to a 12.1 database.

SQL developer - Connection Test Found ORA-28040: No matching authentication protocol
SQL developer – Connection Test Found ORA-28040: No matching authentication protocol

Basically, SQL developer is a self-contained software, you can unzip the software and start to use it. That is to say, SQL developer usually uses its own JDK including JDBC driver to run itself.

Even though I saw no chance to swap Oracle JDBC driver in the same old SQL developer, you can always download the newest SQL developer with newer Oracle JDBC driver from Oracle website. So as to solve ORA-28040.

Don’t worry about the connection settings, the new SQL developer will prompt you the migration option in your first time open.

PL/SQL Developer

PL/SQL developer is an install-based software. Generally, it leverages your native Oracle client to find necessary configuration file. Furthermore, it uses Oracle Call Interface (OCI) of your native Oracle client to connect Oracle databases.

That said, if your native Oracle client is old enough, you will get ORA-28040 like this:

ORA-28040 in PL/SQL Developer
ORA-28040 in PL/SQL Developer

The solution to ORA-28040 in PL/SQL developer is to replace the old OCI with a newer one. First of all, you have to download an Oracle instant client which contains corresponding OCI library. The proper version should be 11.2. In our case, I downloaded and unzipped a basic package of Oracle instant client for windows 32-bit to C:\oracle, the filename is instantclient-basic-nt-11.2.0.4.0.zip for instance.

Please make sure that at least Microsoft Visual Studio 2005 Redistributable is installed in your machine before using Oracle instant client 11.2.

Step 1: Click on the function menu and search for Tools -> Preferences to open the dialog.

PL/SQL Developer - Function Menu - Tools -> Preferences
PL/SQL Developer – Function Menu – Tools -> Preferences

Step 2: Click on Oracle -> Connection to check current Oracle Home.

PL/SQL Developer - Preferences - Connection
PL/SQL Developer – Preferences – Connection

Step 3: Point to new unzipped instant client’s OCI.

PL/SQL Developer - Preferences - Connections - Change OCI Library Location
PL/SQL Developer – Preferences – Connections – Change OCI Library Location

Step 4: Restart PL/SQL and logon an Oracle 12c database.

PL/SQL Developer - Logon an Oracle 12c Database
PL/SQL Developer – Logon an Oracle 12c Database

That’s how we solve ORA-28040 in PL/SQL Developer.

Toad for Oracle

Toad for Oracle is also an installer-based software for Oracle database administration or sometimes development. Same error ORA-28040 may occur in Toad for Oracle, if the underlying network substrate is the same old Oracle 9.2.

ORA-28040 Toad for Oracle - From 9i to 12c
ORA-28040 Toad for Oracle – From 9i to 12c

As we can see, the tool utilized the underlying Oracle client 9.2 to connect a 12c database. That’s why we saw ORA-28040 alert in Toad for Oracle 9.7.

The solution to ORA-28040 in Toad for Oracle is pretty straightforward, just install a newer Oracle client, at least 11g for Toad to utilize of. In this case, I downloaded and installed Oracle client 11.2.0.1 for windows 32-bit from Oracle website. After the new Oracle client has been installed, Toad will automatically detect the new Oracle client for us after software restarted.

New Oracle Client Detected by Toad for Oracle
New Oracle Client Detected by Toad for Oracle

As we can see, the new Oracle client will be used to connect an Oracle 12c database.

SQuirreL SQL Client

SQuirreL SQL Client is a pure Java-based software that can access databases given proper JDBC drivers. In this tool, I used ojdbc14.jar to connect a 12c database, the error is the same, ORA-28040: No matching authentication protocol.

SQuirreL SQL Client - ORA-28040: No matching authentication protocol
SQuirreL SQL Client – ORA-28040: No matching authentication protocol

How we handle it? This time, we don’t have to upgrade the software like SQL developer to solve ORA-28040, because this tool allows us to replace Oracle JDBC Driver with a newer version. In this case, we replace the old driver ojdbc14.jar with newer ojdbc7.jar.

First of all, you have to choose and download a proper JDBC driver that matches the authenticated protocol of 12c database at Oracle JDBC and UCP Downloads page. For 12c databases, either ojdbc6.jar or ojdbc7.jar Oracle JDBC driver is proper to solve ORA-28040.

Step 1: Delete the old driver which is ojdbc14.jar.

SQuirreL SQL Client - Delete ojdbc14.jar Oracle JDBC driver
SQuirreL SQL Client – Delete ojdbc14.jar Oracle JDBC driver

Step 2: Add a new driver which is ojdbc7.jar.

SQuirreL SQL Client - Add driver
SQuirreL SQL Client – Add driver
SQuirreL SQL Client - Add ojdbc7.jar Oracle JDBC driver
SQuirreL SQL Client – Add ojdbc7.jar Oracle JDBC driver
SQuirreL SQL Client - ojdbc7.jar Oracle JDBC driver added
SQuirreL SQL Client – ojdbc7.jar Oracle JDBC driver added

Step 3: Test the connection.

After replacing the old Oracle JDBC driver with a newer one, we can now test the connection again.

SQuirreL SQL Client - Connect to 12.1 Successfully
SQuirreL SQL Client – Connect to 12.1 Successfully

The connection is successful, no longer ORA-28040.

Interoperability Matrix

Various kinds of interoperability problems like ORA-28040 may occur during data migrations or database upgrades. To figure out the relationship between versions, we need a more understandable way to guide us.

Oracle unofficially releases a matrix that describes certified interoperability among database versions. Here I redraw the matrix so as to get it clearer.

Oracle Database Interoperability Matrix
Oracle Database Interoperability Matrix

In the matrix, interoperabilities like “Was” and “MDS” are partially or conditionally certified between client and server. You can go to the page for more information.

Although we can add compatible parameters into SQLNET.ORA to resolve ORA-28040, it is definitely not the only error you may encounter between versions. For example, ORA-03134 is another blocker that you might fight against in some cases.

Leave a Reply

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