Last week I got pinged with an interesting question by a user: why do I see kpolnb: Logon failed because the client is missing the KZTVOV_KPCLOG_O9L_LP capability flag. in my Oracle Database alert.log and cannot log in? This is indeed a curious message in the alert log and warrants a post as to what’s going on. To keep it short for the impatient:
Either change your user password to <=30 bytes or upgrade your database client to 23c+ and you will be good to go.
Background
For the folks of you who are curious to know more, here are the details:
Starting with Oracle Database 23c, user passwords can be up to 1024 bytes long while up until then (i.e. prior to 23c), they could only be 30 bytes long. Likewise, database clients prior to 23c could only send passwords up to 30 bytes long over the authentication protocol to the database server.
Now that leads to an interesting question. If a 23c+ database instance is receiving a 30-byte long password for a user, is the client sending an incorrect, too-short password to the database, or is the client not able to deal with passwords greater than 30 bytes yet? In either case, the authentication will not go through as the password is wrong. But there is a difference between “I have provided you with the wrong password” and “I cannot give you such a long password”.
To know the difference, Oracle Database 23c+ has a new client capability flag called, you guessed it, KZTVOV_KPCLOG_O9L_LP. 23c clients and above will send this flag to the database to inform it that the client is capable of sending passwords that are longer than 30 bytes. On the other hand, if the authentication fails because of the missing new capability flag, the database will print the following diagnostic message into the database alert log: kpolnb: Logon failed because the client is missing the KZTVOV_KPCLOG_O9L_LP capability flag.
So, when you see that message in your alert logs, you know that you have:
- a user that has a password greater than 30 bytes and
- you have older clients trying to connect with that user but are failing to log on because they cannot deal with passwords longer than 30 bytes
Reproducing the issue
You can easily reproduce/test this yourself:
Just log in to the database with a 23c+ client (say the SQL*Plus version that ships with the database) and create a new user with a password longer than 30 bytes:
bash-4.4$ sqlplus system@//localhost/freepdb1
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon May 15 22:33:41 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Enter password:
Last Successful login time: Mon May 15 2023 21:56:05 +00:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> create user test identified by TheseAreMoreThan_30_BytesThatWePassOn;
User created.
SQL> grant connect to test;
Grant succeeded.
Now login with that user with the same SQL*Plus session, the login will be successful:
SQL> connect test/TheseAreMoreThan_30_BytesThatWePassOn@//localhost/freepdb1
Connected.
SQL> select 'Im here';
'IMHERE
-------
Im here
Then try to log in with an older client with the same username and password and you will get the following:
bash-4.4$ sqlplus -version
SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
bash-4.4$ sqlplus test/TheseAreMoreThan_30_BytesThatWePassOn@//localhost/freepdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 15 15:38:38 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Enter user-name:
The moment you hit enter, you will see the following message appear in the alert log:
2023-05-20T22:38:39.202148+00:00
FREEPDB1(3):kpolnb: Logon failed because the client is missing the KZTVOV_KPCLOG_O9L_LP capability flag.
Remedy
You really only have two choices here to remedy the situation:
- Upgrade your client to 23c+ and the authentication will work again. This is the recommended and correct solution, however, sometimes you don’t have the luxury of being able to upgrade the client, e.g., when it’s packaged with the app that you are using. In this case, you only have one option
- Change the password to be <= 30 bytes
The database server is still compatible with older clients and hence an older client connecting with a user that has a password of up to 30 bytes will work as it did before. Here a quick demonstration of that:
Changing the user password with a SQL*Plus version of 23c+:
bash-4.4$ sqlplus test/TheseAreMoreThan_30_BytesThatWePassOn@//localhost/freepdb1
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon May 15 22:43:42 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Last Successful login time: Mon May 15 2023 22:35:40 +00:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> alter user test identified by test;
User altered.
SQL> exit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
Connecting with a SQL*Plus version of 19c with a shorter than 30 bytes password:
bash-4.4$ sqlplus test/test@//localhost/freepdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 15 15:44:59 2023
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Mon May 15 2023 15:43:42 -07:00
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL>
Gerald on IT 