AWS RDS upgrade: how to solve the 'Oracle RDS ORA-28040: No matching authentication protocol' issue
Kerim Kodalci@KerimKdlc
Haven’t we all been there? Wondering if an application will continue to work with the upcoming Database upgrade? In our case, it didn’t at first. This blog post describes how we resolved an ORA-28040 error after upgrading an 12c Oracle RDS Database to the latest 19c version.
Our headache began with the AWS notification that Oracle would no longer support Oracle Database 12c Release 1 (12.1.0.2) as of July 31, 2022. In other words, our AWS RDS database would undergo an automated upgrade shortly if we didn’t take action ourselves.
Upgrading the RDS database
To test the new database version and its compatibility with an application an RDS database upgrade is necessary. Needless to say, you shouldn’t upgrade your production or non-production database. Because once upgraded, you cannot downgrade to the previous version. To revert a database upgrade, you need to use Oracle Data Pump, AWS Database Migration Service (DMS), or any supported logical replication tool.
In our case, taking RDS backups (snapshots) is very helpful to test things. To do so we bootstrap a new Oracle RDS database from a snapshot and upgrade it to the latest version of Oracle (in our case 19c). This means that all our live environments can stay online and are unimpacted if something goes wrong with the upgrade.
Upgrading an AWS RDS is straightforward. You specify a new engine version in your IaC and deploy it, triggering an RDS Database update. Also, note that a major version upgrade can take quite some time.
const engineVersion = OracleEngineVersion.VER_19_0_0_0_2021_04_R1
const oracleRdsInstance = new DatabaseInstanceFromSnapshot(this, `${props.environmentName}-${props.appName}-19c`,{
snapshotIdentifier: props.snapshotIdentifier,
engine:DatabaseInstanceEngine.oracleSe2({version: engineVersion}),
......
Testing the upgraded RDS database
After the upgrade, disconnect the non-production application from the non-production database and redeploy it, specifying the new database we just upgraded to the more recent version. At that moment, we ran into the following error while connecting to the Oracle 19c database: Error: ORA-28040: No matching authentication protocol.
After some digging, we found that this was due to a mismatch between the authentication protocol used by the Oracle database server and the JDBC client. More precisely, it isn’t hashing your password to the latest, most secure standard.
Because upgrading the JDBC client was not an option, we had to find another way to tackle this issue. This is where we discovered that Amazon RDS for Oracle supports new customer modifiable sqlnet.ora parameters - ALLOWED_LOGON_VERSION_SERVER and ALLOWED_LOGON_VERSION_CLIENT.
You can use the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter and set it to the minimum authentication protocol allowed when connecting to Amazon RDS for Oracle DB instances. The SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameter can be set to the minimum authentication protocol allowed for clients, used when a server is acting as a client when connecting to Amazon RDS for Oracle DB instances.
Custom parameter group
The parameter group trick is amazing since it allows you to continue using the current JDBC client until a newer version is installed. You need to create a custom parameter group with the allowed_logon_version_client and allowed_logon_version_server parameters to enable this option in AWS RDS. The database will be restarted to implement the settings from the parameter group when the new parameter group is deployed.
const engineVersion = OracleEngineVersion.VER_19_0_0_0_2021_04_R1
const parameterGroup = new ParameterGroup(this, 'parameter-group',{
engine: DatabaseInstanceEngine.oracleSe2({version: engineVersion}),
description: 'Custom parameter group for the new oracle db',
parameters: {
"sqlnetora.sqlnet.allowed_logon_version_client": "10",
"sqlnetora.sqlnet.allowed_logon_version_server": "10"
}
})
const oracleRdsInstance = new DatabaseInstanceFromSnapshot(this, `${props.environmentName}-${props.appName}-19c`,{
snapshotIdentifier: props.snapshotIdentifier,
engine:DatabaseInstanceEngine.oracleSe2({version: engineVersion}),
parameterGroup: parameterGroup,
......
The deployment of the custom parameter group and a database restart resulted in a successful database connection and solved our Oracle connection error.
Summary
All things considered, fixing the ORA-28040 error was not that difficult. We found that the solution to this issue is as simple as introducing a custom parameter group with the authentication protocol defined.
I hope my blog post was helpful to you in some way. Please don’t hesitate to contact us if you have any questions or comments.
Enjoy and see you soon!