SQLcl and wallet authentication
Sven Illert -
In the recent months I was involved in a large project that is run in Oracle Cloud Infrastructure and of course includes some Oracle databases on Exadatas. As per best practice I have set up an operations compute instance that is used to run maintenance scripts against the databases running on these machines. To use that I have installed SQLcl from the official Oracle Linux repositories because the tool would get updated automatically when the host is updated via os management. I also installed the instant client via the repositories for the same reason.
% sudo dnf config-manager --set-enabled ol8_oci_included-x86_64
% sudo dnf install sqlcl
% which sql
/usr/bin/sql
Of course I have created a TNS_ADMIN directory with a proper tnsnames.ora and sqlnet.ora including a wallet which contains the password for the SYS password for some aliases. The usage of the aliases works perfectly, but using the wallet does not work.
% export TNS_ADMIN=/home/remote/grp-database-use/misc/wallet
% sql sys@DBALIAS as sysdba
SQLcl: Release 23.3 Production on Fri Nov 10 09:36:40 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Password? (**********?) **************
Last Successful login time: Fri Nov 10 2023 09:36:47 +01:00
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL>
% sql /@DBALIAS as sysdba
SQLcl: Release 23.3 Production on Fri Nov 10 09:40:13 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
USER =
URL = jdbc:oracle:thin:@DBALIAS
Error Message = ORA-01017: invalid username/password; logon denied
SQL*Plus of course does work as expected.
% sqlplus /@ESEDGTMIG as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 10 19:17:24 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL>
After playing around with variables I found out, that SQLcl needs to load the OCI JDBC driver to authenticate an SQL*Net connection using a wallet. Of course this is driver is included in the database admin client. After the ORACLE_HOME was set, SQLcl uses this automatically as a source for the OCI driver. But unfortunately the latest release from the repositories as of today (23.3) doesn’t support the usage of release 19c, neither the admin nor the instant client.
% export ORACLE_HOME=/usr/lib/oracle/19.20/client64/lib
% sql -verbose /@DBALIAS as sysdba
SQLcl: Release 23.3 Production on Fri Nov 10 19:19:49 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
2023-11-10 19:19:50.629 INFO oracle.dbtools.raptor.newscriptrunner.commands.connector.OracleStandardFormConnector logConnectionURL Attempting to connect using URL= "jdbc:oracle:oci8:@DBALIAS"
2023-11-10 19:19:50.653 INFO oracle.dbtools.raptor.newscriptrunner.commands.connector.OracleStandardFormConnector logConnectionURL Attempting to connect using URL= "jdbc:oracle:thin:@DBALIAS"
USER =
URL = jdbc:oracle:thin:@DBALIAS
Error Message = Could not initialize class oracle.jdbc.datasource.impl.OracleDataSource
But using quite a recent version of the 21c client doesn’t help either although we get another error message.
% export ORACLE_HOME=/u01/app/oracle/product/21.0/client_1
% $ORACLE_HOME/OPatch/opatch lspatches
35428978;Database Release Update : 21.11.0.0.230718 (35428978)
% sql -verbose /@DBALIAS as sysdba
SQLcl: Release 23.3 Production on Fri Nov 10 19:37:10 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
2023-11-10 11:37:11.234 INFO oracle.dbtools.raptor.newscriptrunner.commands.connector.OracleStandardFormConnector logConnectionURL Attempting to connect using URL= "jdbc:oracle:oci8:@DBALIAS"
2023-11-10 11:37:11.545 INFO oracle.dbtools.raptor.newscriptrunner.commands.connector.OracleStandardFormConnector logConnectionURL Attempting to connect using URL= "jdbc:oracle:thin:@DBALIAS"
2023-11-10 11:37:12.904 WARNING oracle.dbtools.raptor.newscriptrunner.commands.connector.OracleConnector createOracleStandardFormConnection 'OCI' jdbc connection failed, trying jdbc thin...
USER =
URL = jdbc:oracle:oci8:@DBALIAS
Error Message = Incompatible version of libocijdbc[Jdbc:211100230512, Jdbc-OCI:2111000
USER =
URL = jdbc:oracle:thin:@DBALIAS
Error Message = ORA-01017: invalid username/password; logon denied
Since I can’t figure out which of the two version strings is the one that SQLcl wants to have, either 211100230512 or 2111000, I installed the latest available patch for the 21c admin client to see if the output differs.
% sql -verbose /@ESEDGTMIG as sysdba
SQLcl: Release 23.3 Production on Fri Nov 10 19:46:07 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
2023-11-10 13:26:08.390 INFO oracle.dbtools.raptor.newscriptrunner.commands.connector.OracleStandardFormConnector logConnectionURL Attempting to connect using URL= "jdbc:oracle:oci8:@ESEDGTMIG"
2023-11-10 13:26:08.846 INFO oracle.dbtools.raptor.newscriptrunner.commands.connector.OracleStandardFormConnector logConnectionURL Attempting to connect using URL= "jdbc:oracle:thin:@ESEDGTMIG"
2023-11-10 13:26:10.220 WARNING oracle.dbtools.raptor.newscriptrunner.commands.connector.OracleConnector createOracleStandardFormConnection 'OCI' jdbc connection failed, trying jdbc thin...
USER =
URL = jdbc:oracle:oci8:@ESEDGTMIG
Error Message = Incompatible version of libocijdbc[Jdbc:211100230512, Jdbc-OCI:2112000
USER =
URL = jdbc:oracle:thin:@ESEDGTMIG
Error Message = ORA-01017: invalid username/password; logon denied
And as you might see the second part Jdbc-OCI changes from 2111000 to 2112000 which corresponds to the database release update version. The aforementioned version 211100230512 seems to be inbetween of the available software resources. Since that version can’t be found anywhere at the well known Oracle resources I tried another path.
It seems that there’s a special repository for version 21c instant client in the OCI repositories which you can miss easily if you just look for the default instant client repository that is named “Oracle Instant Client for Oracle Linux 8 (x86_64)”. The 21c version of that repository is named “Oracle Instant Client 21 for Oracle Linux 8 (x86_64)” and might give us the client version that would work with the repository variant of SQLcl. So I first enabled the mentioned repository in the OS management. If you use terraform, you can get the corresponding OCID with the following command.
% oci os-management software-source list --compartment-id ocid1.tenancy.oc1..ttt --display-name 'Oracle Instant Client 21 for Oracle Linux 8 (x86_64)'
{
"data": [
{
"compartment-id": "ocid1.tenancy.oc1..ttt",
"defined-tags": {},
"description": "",
"display-name": "Oracle Instant Client 21 for Oracle Linux 8 (x86_64)",
"freeform-tags": {},
"id": "ocid1.osmssoftwaresource.oc1..yyy",
"lifecycle-state": "ACTIVE",
"packages": 64,
"parent-id": "ocid1.osmssoftwaresource.oc1..xxx",
"parent-name": "Oracle Linux 8 BaseOS Latest (x86_64)",
"repo-type": "yum",
"status": "NORMAL"
}
]
}
So now let’s see if the prepatched version of the instant client provided via repository will help us use SQLcl with the OCI driver with wallet integration.
% export ORACLE_HOME=/usr/lib/oracle/21/client64/lib
% sudo dnf info oracle-instantclient-basic
This system is receiving updates from OSMS server.
Last metadata expiration check: 0:04:44 ago on Fri 10 Nov 2023 19:54:53 PM CET.
Installed Packages
Name : oracle-instantclient-basic
Version : 21.11.0.0.0
Release : 1.el8
Architecture : x86_64
Size : 238 M
Source : oracle-instantclient-basic-21.11.0.0.0-1.el8.src.rpm
Repository : @System
From repo : ol8_oracle_instantclient21-x86_64
Summary : Oracle Instant Client Basic package
URL : https://www.oracle.com/
License : Oracle
$ sudo dnf install oracle-instantclient-basic
$ export ORACLE_HOME=/usr/lib/oracle/21/client64/lib
$ sql /@DBALIAS as sysdba
SQLcl: Release 23.3 Production on Fri Nov 10 20:00:39 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL>
And that’s it! Sometimes the easiest manner to use something is found on a pretty rocky path. So in the end all you need to do is install both SQLcl and Oracle Instant Client 21c from the correct Oracle Linux repositories. If you are using Windows or some other Linux distribution, you may also try the instant client download instead of a patched admin client. Also don’t forget to set the ORACLE_HOME directory to the lib subdirectory of the instant client.