Does SQLNET.ALLOWED_LOGON_VERSION_SERVER Parameter Affect the Password Version?

That is a great question! But before answering it, let’s look at what the password version is, and what the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter supposed to do?

The PASSWORD_VERSIONS column can be found in the DBA_USERS view, and it shows the list of password versions that exist for a specific user.
Based on Oracle’s documentation, 10G refers to case-insensitive Oracle password version, 11G refers to the SHA-1-based password version, and 12C refers to the SHA-2-based SHA-512 password version.

When you check out the column in DBA_USERS view, you might find different password versions: “10G” or “10G 11G 12C” or “11G 12C” or “12C”. If you are in a 19c database, you would expect to see only 12c password versions, but this is not always the case.

SQLNET.ALLOWED_LOGON_VERSION_SERVER is a parameter in the sqlnet.ora file, that sets the minimum authentication protocol allowed when connecting to an Oracle Database. Based on Oracle’s documentation, 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. In other words, a 10g client might not be able to connect to a 19c database, depending on the value of this parameter in the sqlnet.ora file.

So I did an experiment, I exported a user (my userid) from an 11g database, and imported the same user into a 19c database. I did two takes on this.

Before jumping into the exercise, let’s look at the password_version in the 11g database:

-- this is 11g database
   my id has a password version of 10G,
   meaning I could connect with a case insensitive password.

select username , password_versions 
from dba_users where username like 'DR%' 
order by 2;

USERNAME         PASSWORD_VERSIONS
---------------- -----------------
DROBETE          10G

In my first attempt to export/import the user from 11g database to 19c database, the user was imported and created without a password, and the password_versions column was empty.
This was because the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter was at its default value of 12, which is also exclusive mode. A user with the 10G password version cannot connect to the 19c database, as the password doesn’t comply with the new security. In order for the user to connect, the password must be changed.

Here is what happened during the import:

...
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  ...
Processing object type SCHEMA_EXPORT/USER
ORA-39384: Warning: User DROBETE has been locked and the password expired.
...

In my second attempt, of the import into 19c, I modified or added the SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10 to the sqlnet.ora file.
This change made security more permissive and allowed 10g clients or later to connect to the database. On the import of the user, the password was kept at version 10G, and the account was not locked and expired.

What was even more interesting, I was able to connect with a case insensitive password to a 19c database.

select username , password_versions 
from dba_users where username like 'DR%' order by 2;

USERNAME         PASSWORD
---------------- --------
DROBETE          10G

--I tested connecting with a case insensitive password and it worked!

conn DROBETE/10gPASSWD123
Connected.
conn DROBETE/10gpasswd123
Connected.

Lesson here is, to be careful of the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter setting, it can make the database less secure, and more permissive with the passwords.
In today’s world you do not want to be able to connect to the database with a case insensitive password!
That being said, the question comes, when would you want to make the security more permissive, and set this parameter to a lower value than the default in 19c?

In my case, it was for an upgrade. The client wanted the database upgrade to be seamless from the users’ perspective, meaning they did not want to force the users to change their passwords. They wanted to let the users use their old passwords, whether or not those were secure.

With this parameter set to a value of 10, when creating a new user in the 19c database, their password version will be “10G 11G 12C”. So be careful when setting this parameter to a non default value!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

---

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com

---