Ora 28040 no matching authentication protocol как исправить

When I tried to connect to 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, Oracle 9i client connect to 12c, 18c or 19c database. But I didn’t expect ORA-28040 to show up.

For those users who want to use Oracle client 11g to connect to database 19c, but failed with ORA-28040, I’ll talk about it in the last section.

Same error may occur while you’re using old Oracle JDBC drivers, say ojdbc14.jar or below, to connect a 12c or 18c 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.

Server or Clients?

According to the above explanation about ORA-28040, we should provide compatible authentication protocol for either client or server to accept. But now the question is: Should we change server configuration or just upgrade Oracle clients?

In fact, it depends on what privileges you have. For database administrators, you can change the server configuration to solve all users’ problems at once. As for developers, fixing or upgrading Oracle clients is the only choice you can do, so as to match authenticated protocol of Oracle 12c databases.

For better understanding different applicable situations, I will split the solutions against ORA-28040 into two main parts in this post, the first part is for server side, the other one is for client side.

1. Server Side Solutions
2. Client Side Solutions
3. Oralce Client 11g Connects to Database 19c

Server Side Solutions to ORA-28040

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. As a matter of fact, most developers do not have the right.

There’re 2 scenarios of interoperability between new and old versions.

1. Old Client to New Server
2. New Client to Old Server

We introduce the server-side solutions for each scenarios below.

Old Client to New 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:

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, our connections have no ORA-28040 shown up. Moreover, we can migrate data from 9i to 12c over a database link.

For some users who saw ORA-01017: invalid username/password; logon denied need DBA’s interventions.

New Client to Old 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.

Further reading: DB Link, How and Why

The connections from 9i to 12c can be worked around by the solutions provided in this post. In contrast, 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 Solutions to ORA-28040

Developers who don’t act as DBA may have no right to change SQLNET.ORA on the server side. Therefore, changing or upgrading their own clients is probably the only solution to ORA-28040.

From now on, we begin to focus on clients. Especially for those who are using Oracle JDBC drivers.

Additionally, some client tools that have authentication protocol problem also need to be configured and how we handle it.

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

Oracle JDBC Driver

Since Oracle 12.1 database claims that it is compatible with ojdbc6.jar for JDK 6 or ojdbc7.jar for JDK 7, so ojdbc14.jar for JDK 1.4 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.

SQL Developer

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

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.

Solution

Even though I see no chance to replace 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.

Further reading: How to Connect MySQL from SQL Developer

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 is, if your native Oracle client is old enough, you will get ORA-28040 like this:

Solution

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 at least 11g.

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 has been installed in your machine before using Oracle instant client 11.2.

Step 1: Open Preferences Dialog

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

Step 2: Go to “Connection” Section

Click on Oracle -> Connection to check current Oracle Home.

Step 3: Change OCI Library

Point to new unzipped instant client’s OCI. Please note that, you have to provide the whole absolute path including the filename, not just only the directory.

Step 4: Test the Connection

Restart PL/SQL developer and logon an Oracle 12c database.

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

Toad for Oracle

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

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.

Solution

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.

Please note that, Oracle client and Oracle instant client are different, the former is an install-based and full-fledged software, the later is a portable and partial-functioned package.

In this case, I downloaded and installed Oracle client 11.2.0.1 for windows 32-bit from Oracle website. Consequently, the newly installed Oracle client creates some registry parameters like ORACLE_HOME and TNS_ADMIN as environment variables for tools like Toad to recognize. So that, the restarted Toad will automatically detect the new Oracle client for us.

As we can see, the new Oracle client is found by Toad and ready to be used to match the authentication protocol of an Oracle 12c database.

If the new Oracle client is not detected and selected, you should use the drop-down list to choose the right one to use. Nevertheless, if there’s no new Oracle client in the drop-down list, you should check required registry parameters of the new Oracle client, such as ORACLE_HOME and TNS_ADMIN in Windows.

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.

Solution

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 old driver

Delete the old driver which is ojdbc14.jar.

Step 2: Add a new driver

Click on Add button.

Select ojdbc7.jar in the file browser

The new driver is selected as shown below.

Step 3: Test the connection

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

The connection is successful, no longer ORA-28040.

Oralce Client 11g Connects to Database 19c

Recently, we received some feedback from users who want to connect to database 19c from client 11g, but failed with ORA-28040.

After some investigations, we found only one release of 11g client can reach database 19c. It’s Oracle client 11.2.0.4 which is the last release of 11g.

Oracle client 11.2.0.1 —X—> Oracle database 19c
Oracle client 11.2.0.2 —X—> Oracle database 19c
Oracle client 11.2.0.4 ——-> Oracle database 19c

Either Oracle client 11.2.0.4 or instant client 11.2.0.4 is able to reach Oracle database 19c.

I am trying to connect my grails project to Oracle databse(Oracle 12c) in windows(8) system. However, whenever I run my application I get following exception :

Caused by: org.apache.commons.dbcp.SQLNestedException: 
Cannot create PoolableConnectionFactory (ORA-28040: 
No matching authentication protocol)

Caused by: 
java.sql.SQLException: ORA-28040: 
No matching authentication protocol

According to internet suggestion I also tried editing my *.ora file but it is not working.

I added following snippet in sqlnet.ora file :

SQLNET.ALLOWED_LOGON_VERSION=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

Here i tried assigning (10,11,12) but neither of them is working.

Can anyone please help me with this ?

The ORA 28040 no matching authentication protocol code exception usually affects your system due to an unsupported combination of Oracle database product components.

As a result, your application halts further procedures and terminates the matching authentication protocol, affecting other functions and inputs.

We wrote this debugging guide to answer the question of how do I fix ora-28040: no matching authentication protocol using simple debugging processes and easily replicable authentication protocols.

In addition, we will reproduce the ora-28040: no matching authentication protocol C, which is critical before changing the values in your program to prevent further obstacles.

Why Is the Ora 28040 No Matching Authentication Protocol Happening?

The ora-28040 no matching authentication protocol JDeveloper code exception usually happens due to an unsupported combination of database product components. As a result, this code exception confirms the matching authentication protocol in your system is not compatible with the RDS database after upgrading the program.

For instance, the ora-28040 no matching authentication protocol Oracle 19c JDBC warning is almost inevitable when upgrading your program and running the latest inputs.

Although the latest versions and updated programs should prevent unexpected complications and obstacles, mistakes can happen with straightforward or complex projects. In other words, the SSIS ora-28040: no matching authentication protocol message confirms your system does not support a combination of one or more product components.

However, many users complained troubleshooting and pinpointing the exact culprits for the ora-28040 no matching authentication protocol 12c is challenging and time-consuming.

Taking that into account, we will reproduce the warning using real-life examples and scripts blocking your program and halting further operations.

Similarly, we also encountered the SQLPlus ora-28040: no matching authentication protocol error when your documentation and folders include unnecessary JAR files. This inconvenience confuses the primary and secondary commands, although most other elements and values are fully functional and error-free.

So, let us recreate the ora-28040 no matching authentication protocol after upgrade to 19c mistake before listing and exemplifying the possible debugging techniques.

– Connecting to an Oracle Test Database

This article’s introductory broken chapter attempts to connect to an Oracle test database using common elements. However, as the former section confirmed, your system raises a warning due to the unsupported inputs and product components.

Therefore, we will exemplify the procedure by listing the SQL net file processes, TNS names file, and JS test script.

The following example provides the SQL net file processes:

Although the authentication service and directory’s path appear fully functional, the system fails to interpret and render their properties. In addition, running these values by connecting to the relevant line from the TNS names file blocks further functions.

You can learn more about this syntax in the following example:

Connecting to an Oracle test database is only completed after the JS test script is provided.

The following example provides the last piece of the puzzle:

This code snippet completes the procedure and fails your program.

– Migrating From Oracle 11G to Oracle 19C

Your system could experience this protocol code exception when migrating from Oracle 11g to Oracle 19c. The obstacle occurs due to compatibility issues with the product services, although the latest Oracle version runs advanced procedures.

In addition, the connection strings work fine for Oracle 11g, but the same inputs fail in Oracle 19c. As a result, your system displays an error log confirming the inconsistencies.

You can discover the complete error log in the following example:

Although we could list other indicators and error sources, these are sufficient to clarify the cause. You can also use this information to troubleshoot your program, which is critical before applying the solutions. Still, this is one of the many incorrect instances displaying this error.

– Connecting a Grails Project on Oracle Database

This guide’s last failed protocol operation connects a grails project on an Oracle database using simple commands.

Although this operation sounds straightforward, your system launches a code exception when running the functions. For instance, a few unnecessary JAR files in the main folder create the bug and terminate the application. Still, the system confirms the flaws and exemplifies the inputs.

The following example provides information when running the commands:

The code snippet indicates it cannot locate a matching authentication protocol, ruining your programming experience. Although you can try adding the SQL net ORA files and values, the error persists, and the commands malfunction.

You can learn about the SQL net ORA files in the following example:

As you can tell, this example tries assigning the latest versions and modes, which does not repair the program. Lastly, we will provide the custom parameter group that should help you troubleshoot the program, as shown below:

This instance completes the process of reproducing the mistake using typical functions and elements, which is vital before repairing the snippets.

How to Repair the Ora 28040 No Matching Authentication Protocol Bug?

You can repair the ORA 28040 authentication protocol code exception by changing the SQL net ORA file confusing your program. On the flip side, the alternative debugging techniques suggest replacing the OCI with a newer one or deleting the irrelevant files from the primary folder.

Overcoming this instance is relatively convenient because the same approach repairs all cases and does not cause further complications or obstacles. As a result, we will exemplify the correct values and the visual output your system displays after rendering the commands.

The following example teaches how to fix your program:

This code snippet confirms the operations are successful and that your application reenabled the failed commands. However, the alternative approach is as convenient as this one.

– Replacing the Old OCI With a Newer One

Replacing the old OCI with a newer one ensures your program will no longer experience this mistake. So, you must download the Oracle instant client to initiate the debugging procedure because it corresponds to the OCI library.

Ensure the version is at least 11g. Next, unzip the basic package and install your program’s Oracle instant client for Windows 32-bit.

In addition, the filename must be identical to the one in your main document. This action prevents further complications. Finally, restart your machine to save the changes, and the error should disappear. Your application should no longer display the same code exception.

Conclusion

The ORA 28040 no matching authentication protocol code exception happens due to an unsupported combination of database product components. However, we fixed your program, and here are some points that will help you in debugging this error:

• The bug confirms the matching authentication protocol is not compatible with the RDS database
• We reproduced the mistake by connecting to an Oracle test database
• You can repair the ORA 28040 authentication protocol code exception by changing the SQL net ORA file
• The alternative debugging approach suggests replacing the old OCI

You can finally enjoy your programming experience by repairing your document and reenabling all processes. In addition, our debugging methods prevent unexpected obstacles and inconsistencies.