Brad's Blog: TLS

Showing posts with label TLS. Show all posts

Monday, August 3, 2026

Oracle AI Database Walletless TLS

Over the last few years, I've educated enough customers on the concept of walletless Transport Layer Security (TLS) that I think a blog post is warranted to unpack this feature. In short, the term walletless means that you do not need to create, distribute and update Oracle wallets (or Java Trust store for JDBC thin clients) to all Oracle AI Database clients for validating the authenticity of the server's TLS signed certificate. The client instead, leverage's the client host operating system's existing certificate trust store to confirm the authenticity of a server's certificate. The client configuration conveys the intent to use the client's host operating system trust store by setting the WALLET_LOCATION=SYSTEM parameter in either sqlnet.ora or the connect string/name service (TNS) record stored in either a local tnsnames.ora file or remotely in the LDAP-based name service.

This feature was initially introduced between 2021 and 2022 with 21c and was backported to 19c and is supported going forward including 26ai.

This one-way TLS framework is composed of certificate authority (CA), a TLS server (Oracle AI Database Server) with a certificate that is signed by the CA, and a TLS client (Oracle AI Database Client such as SQL*Plus, SQLcl, SQL Developer, ... and programatic clients like java, python, ruby, ...) with local trust store. The client connects to the server over TLS, retrieves the server's certificate and then confirms the authenticity with the certificate chain of trust that is stored in a local trust store.

With self-signed certificates, a wallet (or java trust store) must be created and populated with the certificate chain of trust, which is just the client's certificate. Then the trust store(s) must be distributed to all database clients that need to securely connect over TLS to the server. This can be an arduous and operationally inefficient repetitive maintenance task that may not always be possible because the database server administrators do not always know what clients are connecting to the server.

Mutual two-way TLS authenticity framework is the same as one-way TLS plus the client has a signed TLS certificate that the server must confirm as well. Oracle's walletless solution does not include support for mutual TLS.

Let's look at two examples: Privately signed certificates by Active Directory Certificate Service (AD CS) and publicly signed certificates by a public CA.

Active Directory Certificate Service Signed Certificates

In the case of the privately signed certificates by AD CS, Microsoft automatically propagates the AD CS certificate chain to the trust store of managed computers. For enterprise environments, that covers all of the enterprise managed laptop and desktop computers. For database client software running on any of the managed hosts, the client's sqlnet.ora (parameter defaults file) or tnsnames.ora can set WALLET_LOCATION=SYSTEM to indicate that the client can confirm the authenticity of the server's certificate.

Here is a sample sqlnet.ora file entry:

cat sqlnet.ora
WALLET_LOCATION=SYSTEM

Here is a sample tnsnames.ora file entry:

PDB1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(WALLET_LOCATION=SYSTEM)
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=pdb1)
)
)

Public Certificate Authority Signed Certificates

In the case of publicly signed certificates, the operating system vendor maintains a trust store that contains the current certificate chain of trust for all major public certificate authorities. This trust store is periodically updated through operating system updates.

The sqlnet.ora and tnsnames.ora examples from the privately signed AD CS example would similarly set WALLET_LOCATION=SYSTEM to use the local operating system trust store.

For reference, here is information on each of the major operating system trust stores.

Note that Advanced Interactive Executive (AIX) is the only operating system for which the trust store provider that may not by default include any certificate chains of trust. The AIX link above provides instructions on how add certificate chains of trust to the AIX trust store.

Additional documentation references for client specific configurations available below:

I hope you find this helpful!

Blessings!

Monday, June 22, 2026

RAC TLS: How To Complete Self-signed Cert Setup

With Oracle Exadata Cloud Service and Base Database Service, the encrypted TCPS port 2484 is partially setup with a self-signed certificate stored in the remote grid computing (GRID) listener wallet and TCPS port 2484 is enabled on both the remote GRID listener and the local database server listener.

IMPORTANT: Please note that use of self-signed certificates are intended for non-production environments. Production environments should use certificates that are signed by a public or private certificate authority. Using certificates signed by public and/or private certificate authorities are not only more secure but also easy to maintain operationally because no client wallet needs to be created, periodically updated or distributed to client hosts because with public and/or private certificate authorities, the client WALLET_LOCATION can be set to SYSTEM. WALLET_LOCATION=SYSTEM means, use the local host's certificate chain trust store.

In this blog, I demonstrate the steps to complete the TCPS setup on a Real Application Cluster (RAC) Real Application Cluster (RAC) node of a container database (CDB) of a Base Database Service. Therefore, all references to the "local database server" are using the CDB TLS Dir path. If the database that you want to complete self-signed certificate configuration is a pluggable database (PDB), you would use the PDB TLS Dir outlined below. Also, in the Base Database Service, all of the wallets are owned by the oracle user. In the Exadata Cloud Service server, the GRID wallet may be owned by the grid user. Therefore, you may need to switch between users and in the case of copying the GRID wallet, you may need to sudo to root to copy the wallet and change the target ownership to oracle:oinstall.

The default wallet directories for the Base Database Service are as follows:

GRID Wallet: /opt/oracle/dcs/commonstore/tcps_wallet
WALLET_ROOT: /opt/oracle/dcs/commonstore/wallets/<DB_NAME>
CDB TLS Dir: /opt/oracle/dcs/commonstore/wallets/<DB_NAME>/tls
PDB TLS Dir: /opt/oracle/dcs/commonstore/wallets/<DB_NAME>/<PDB_GUID>/tls

The default wallet directories for Exadata Cloud Service are as follows:

GRID Wallet: /var/opt/oracle/dbaas_acfs/grid/tcps_wallets
WALLET_ROOT: /var/opt/oracle/dbaas_acfs/<DB_NAME>/wallet_root
CDB TLS Dir: /var/opt/oracle/dbaas_acfs/wallet_root/<DB_NAME>/tls
PDB TLS Dir: /var/opt/oracle/dbaas_acfs/<DB_NAME>/wallet_root/<PDB_GUID>/tls

For this example, the database connection string that I use to connect to the CDB database server from within the RAC cluster node is:

DEVDB_SSL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=devdb-scan.mysubnet.odswest.oraclevcn.com)(PORT=2484))
(SECURITY=
(WALLET_LOCATION=/opt/oracle/dcs/commonstore/wallets/devdb/tls)
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="CN=devdb-scan.mysubnet.odswest.oraclevcn.com")
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=devdb.mysubnet.odswest.oraclevcn.com)
)
)

You can confirm that the remote GRID listener is configured to listen on TCPS port 2484 with:

sudo su - oracle
srvctl config scan_listener
SCAN Listeners for network 1:
Registration invited nodes:
Registration invited subnets:
Endpoints: TCP:1521/TCPS:2484
SCAN Listener LISTENER_SCAN1 exists
SCAN Listener is enabled.
SCAN Listener LISTENER_SCAN2 exists
SCAN Listener is enabled.
SCAN Listener LISTENER_SCAN3 exists
SCAN Listener is enabled.

You can confirm that the local database server listener is configured to listen on TCPS port 2484 with:

sudo su - oracle
srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: grid
Home: <CRS home>
End points: TCP:1521/TCPS:2484
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:

And you can confirm that the GRID listener wallet has a self-signed certificate loaded with:

sudo su - oracle
cd /opt/oracle/dcs/commonstore
orapki wallet display -nologo -summary -wallet tcps_wallet
Requested Certificates:
User Certificates:
Subject: CN=devdb-scan.mysubnet.odswest.oraclevcn.com
Trusted Certificates:
Subject: CN=devdb-scan.mysubnet.odswest.oraclevcn.com

The steps required to complete the TCPS setup are simply to setup the local database server listener wallet and ensure it includes the remote GRID listener certificate. There are two paths toward this end.

EASY METHOD

The easy method is simply to copy the existing local GRID listener wallet to the local database server listener wallet and ensure the proper permissions are set on the local CDB database server listener wallet.

For Base Database Service:

sudo su - oracle
cd /opt/oracle/dcs/commonstore
mkdir -p wallets/devdb/tls

rsync -Ha tcps_wallet/. ./wallets/devdb/tls/.

chmod g+rx ./wallets/devdb/tls
chmod g+r ./wallets/devdb/tls/*

For Exadata Cloud Service:

First, lookup the wallet_root directory.

sudo su - oracle
sqlplus / as sysdba
SQL> show parameter wallet_root;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root string /var/opt/oracle/dbaas_acfs/wallet_root

Then, create/replace the local CDB database server wallet according to the above wallet_root path.

sudo su - oracle
cd /var/opt/oracle/dbaas_acfs/grid
mkdir -p wallet_root/devdb/tls
exit

sudo /bin/bash
rsync -Ha /var/opt/oracle/dbaas_acfs/grid/tcps_wallets/. /var/opt/oracle/dbaas_acfs/wallet_root/devdb/tls/.
chown -R oracle:oinstall /var/opt/oracle/dbaas_acfs/wallet_root/devdb/tls
chmod g+rx /var/opt/oracle/dbaas_acfs/wallet_root/devdb/tls
chmod g+r /var/opt/oracle/dbaas_acfs/wallet_root/devdb/tls/*
exit

SLIGHTLY MORE SECURE METHOD

The slightly more secure method prescribed by knowledge article KB837637 entails creating a new separate self-signed certificate for the local CDB database server listener wallet and loading it with the remote GRID listener certificate as well.

First, create the new separate local CDB database server listener wallet with auto_login enabled.

sudo su - oracle
orapki wallet create -nologo -wallet /opt/oracle/dcs/commonstore/wallets/devdb/tls -pwd "Oracle123" -auto_login

Then, create a new self-signed certificate in the local CDB database server listener wallet.

sudo su - oracle
orapki wallet add -nologo -wallet /opt/oracle/dcs/commonstore/wallets/devdb/tls -pwd "Oracle123" -dn "CN=devdb.mysubnet.odswest.oraclevcn.com" -asym_alg RSA -keysize 2048 -self_signed -validity 3650

Lastly, export the self-signed remote GRID listener certificate and load it into the local CDB database server listener wallet.

CLIENT WALLET

If you test from a remote host, you will need a client wallet containing both the certificate from the local CDB database server wallet and the remote GRID listener wallet. Here is how to create a client wallet on the database server and package it up for use on a remote database client computers.

sudo su - oracle
cd /opt/oracle/dcs/commonstore
orapki wallet export -nologo -wallet tcps_wallet -dn CN=devdb-scan.mysubnet.odswest.oraclevcn.com -cert grid_wallet.pem
orapki wallet export -nologo -wallet wallets/devdb/tls -dn CN=devdb.mysubnet.odswest.oraclevcn.com -cert cdb_wallet.pem
orapki wallet create -nologo -wallet client_wallet -pwd "Oracle123" -auto_login
orapki wallet add -nologo -wallet client_wallet -pwd "Oracle123" -dn "CN=devdb-scan.mysubnet.odswest.oraclevcn.com" -trusted_cert -cert grid_wallet.pem
orapki wallet add -nologo -wallet client_wallet -pwd "Oracle123" -dn "CN=devdb.mysubnet.odswest.oraclevcn.com" -trusted_cert -cert cdb_wallet.pem

cat > client_wallet/tnsnames.ora <<EOF
DEVDB_SSL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=devdb-scan.mysubnet.odswest.oraclevcn.com)(PORT=2484))
(SECURITY=
(WALLET_LOCATION=.)
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="CN=devdb-scan.mysubnet.odswest.oraclevcn.com")
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=devdb. mysubnet.odswest.oraclevcn.com)
)
)
EOF

cat > client_wallet/sqlnet.ora <<EOF
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=.))
SSL_CLIENT_AUTHENTICATION=FALSE
EOF

zip -r client_wallet.zip client_wallet

TEST

At this point, if you connect and login through port 2484, the login should be successful and once logged in confirm that the connection is over secure encrypted TCPS network protocol.

sqlplus system@DEVDB_SSL
Enter password:
Last Successful login time: Mon Jun 22 2026 21:24:04 +00:00

Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.31.0.0.0

SQL> SELECT sys_context ('userenv','NETWORK_PROTOCOL') FROM DUAL;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

TROUBLESHOOTING

If when initially attempting to connect securely and login to port 2484 with a SQL client BEFORE setting up the local CDB database server listener wallet, the following error would be returned because the database server wallet for the local listener had not yet been setup or didn't yet have auto_login enabled.

ORA-28759: failure to open file

Here is how to create the wallet where devdb is the name of the CDB database server and Oracle123 is the password used to protect the wallet from changes. You should set the wallet password to whatever you desire.

sudo su - oracle
orapki wallet create -nologo -wallet /opt/oracle/dcs/commonstore/wallets/devdb/tls -pwd "Oracle123" -auto_login

If the local CDB database server listener wallet existed and had auto_login enabled but had not yet been loaded with a signed certificate, the following error would be returned to the SQL client:

ORA-28845: No certificate

Here is how to add a self-signed certificate to the local listener's CDB database server wallet where devdb is the CDB database server name, Oracle123 is the wallet password and devdb.mysubnet.odswest.oraclevcn.com is CDB database name used for the local listener wallet certificate subject CN value. The certificate subject CN value can be arbitrary but for consistency's sake, I typically use the database name and the domain name.

Once the local listener CDB database server wallet is loaded with the new self-signed certificate, if you attempt to login locally to the CDB database server, the following error is returned:

ORA-29024: Certificate validation failure

This is because the self signed certificate of the remote GRID listener has not yet been loaded into the local CDB database server listener wallet. This is accomplished by exporting the self-signed certificate from the remote GRID listener wallet and loading that certificate into the wallet of the local CDB database server wallet.

I hope you find this helpful.

Blessings!

Monday, May 11, 2026

Upgrading ODB 23.26.1 to 23.26.2 and adding TCPS

Last week I was troubleshooting an Oracle AI 26 Database unified audit log project that required upgrading from 23.26.1 to 23.26.2 I had to iterate through the setup multiple times to make sure that I had the flow just right. I did not know that stand alone Oracle AI 26 Databases use an out-of-place Gold Image release updates (RUs) method rather than the traditional "opatch apply". Given that this was new to me, I thought that I would helpful to write up my notes from that journey for my own edification but hopefully help others as well. In addition, since this project was oriented around Entra ID integration with TCPS/TLS connections required been database client and server, those steps are included as well.

1. Make sure /etc/hosts entry is preceded by TLS cert FQDN:

grep hrdb /etc/hosts
10.0.0.33 hrdb.dbauthdemo.com hrdb.sub10241351260.odswest.oraclevcn.com hrdb

2. Make sure hostname is fully qualified using the TLS cert FQDN:

sudo hostnamectl set-hostname $(hostname -f)

3. Upload Oracle AI Database 23.26.1 (V1054592-01.zip from https://edelivery.oracle.com) and 23.26.2 (p39099680_230000_Linux-x86-64.zip from https://updates.oracle.com/download/39099680.html) to /u01/bits:

ls -1 /u01/bits
p39099680_230000_Linux-x86-64.zip (23.26.2)
V1054592-01.zip (23.26.1)

4. Extract and install software and setup 19c database server:

Note: manage_db.sh in OUD POC Kit at https://github.com/oudlabs/oudpockit

/u01/manage_db.sh setup --dbv 26ai

5. Set LOCAL_LISTENER because I’m running 26ai on default port and 19c on alternate port in my lab.

$ORACLE_HOME/bin/sqlplus / as sysdba
SHOW PARAMETER LOCAL_LISTENER;

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=hrdb.dbauthdemo.com)(PORT=1521))' SCOPE=BOTH;

ALTER SYSTEM REGISTER;

6. Update 26 AI database to most recent release update (23.26.2) using the out-of-place Gold Image release update method found in the patch README file:

unzip -qo -d /u01/app/oracle/26ai/dbhome_2 /u01/bits/p39099680_230000_Linux-x86-64.zip

/u01/app/oracle/26ai/dbhome_2/runInstaller -silent -setupDBHomeAs /u01/app/oracle/26ai/dbhome_1

sudo /u01/app/oracle/26ai/dbhome_2/root.sh

/u01/app/oracle/26ai/dbhome_2/bin/dbca -silent -moveDatabase -sourceDB hrdb

7. Alter db26ai.env to change dbhome_1 (23.26.1 home) to dbhome_2 (23.26.2 home):

cat /u01/cfg/db26ai.env

export ORACLE_BASE="/u01/app/oracle/26ai"
export ORACLE_HOME="/u01/app/oracle/26ai/dbhome_2"
export TNS_ADMIN="/u01/app/oracle/26ai/dbhome_2/network/admin"
export ORACLE_SID="hrdb"
export ORACLE_INSTANCE="/u01/app/oracle/26ai/dbhome_2"
export ORACLE_UNQNAME="hrdb"
export WALLET_ROOT="/u01/app/oracle/26ai/wallet_root"

8. Re-load the db26ai env and restart database:

. /u01/cfg/db26ai.env
/u01/manage_db.sh stop --dbv 26ai
/u01/manage_db.sh start --dbv 26ai

9. Set wallet_root and then lookup PDB1 GUID:

. /u01/cfg/db26ai.env
$ORACLE_HOME/bin/sqlplus / as sysdba
show parameter wallet_root;

alter system set wallet_root='/u01/app/oracle/26ai/wallet_root' scope=spfile;

select guid from v$containers where name = 'PDB1';

GUID
--------------------------------

512D5CFBAB67AA47E0632100000A0A24

10. Make tls directories (replace PDB GUID with value you retrieve from previous step):

mkdir -p $WALLET_ROOT/tls $WALLET_ROOT/<PDB_GUID>/tls

11. Extract wallet_root from backup with:

unzip -qo -d "$WALLET_ROOT/.." /u01/hrdb_wallet_root.zip

12. Copy wallet to PDB1 tls directory (replace PDB GUID with retrieved PDB GUID value):

rsync -Hav $WALLET_ROOT/tls/. $WALLET_ROOT/<PDB_GUID>/tls/.

13. Display wallet root contents to make sure they are valid:

$ORACLE_HOME/bin/orapki wallet display -nologo -complete -wallet $WALLET_ROOT/tls

14. Create or update sqlnet.ora:

cat $TNS_ADMIN/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)

SSL_CLIENT_AUTHENTICATION = FALSE

15. Create or update listener.ora:

cat $TNS_ADMIN/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.dbauthdemo.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.dbauthdemo.com)(PORT = 2484))
(SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
)
)

16. Create or update tnsnames.ora:

cat $TNS_ADMIN/tnsnames.ora
HRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.dbauthdemo.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

HRDB_SSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.dbauthdemo.com)(PORT = 2484))
(SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

pdb1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=hrdb.dbauthdemo.com)(PORT=1521))
(CONNECT_DATA=
(SERVER = DEDICATED)
(SERVICE_NAME=pdb1)
)
)

pdb1_ssl =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
(SECURITY=(WALLET_LOCATION=/u01/app/oracle/26ai/wallet_root/tls))
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=pdb1)
)
)

17. Update /etc/oratab hrdb entry because I’m running 19c and 26ai on two separate home’s on the same host:

From:

hrdb:/u01/app/oracle/26ai/dbhome_1:N

To:

hrdb26ai:/u01/app/oracle/26ai/dbhome_2:N

18. Restart database server:

/u01/manage_db.sh stop --dbv 26ai
/u01/manage_db.sh start --dbv 26ai

19. Test that you can login to each alias in tnsnames.ora:

$ORACLE_HOME/bin/sqlplus system/Oracle123@hrdb

$ORACLE_HOME/bin/sqlplus system/Oracle123@hrdb_ssl

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1_ssl

20. Configure PDB1 for Entra ID integration and add necessary users and roles:

$ORACLE_HOME/bin/sqlplus system/Oracle123@pdb1_ssl

ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE=AZURE_AD SCOPE=BOTH;
SHOW PARAMETER IDENTITY_PROVIDER_TYPE;
ALTER SYSTEM SET IDENTITY_PROVIDER_CONFIG =
'{
"application_id_uri" : "https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621",
"tenant_id" : "7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1",
"app_id" : "16736175-ca41-8f33-af0d-4616ade17621"
}' SCOPE=BOTH;
SHOW PARAMETER IDENTITY_PROVIDER_CONFIG;

drop USER alldbusers;
CREATE USER alldbusers IDENTIFIED GLOBALLY AS 'AZURE_ROLE=pdb.users';
SELECT USERNAME, EXTERNAL_NAME from DBA_USERS where username='ALLDBUSERS';

drop ROLE entra_dba;
CREATE ROLE entra_dba IDENTIFIED GLOBALLY AS 'AZURE_ROLE=dba.role';
GRANT pdb_dba TO entra_dba;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'ENTRA_DBA';

drop ROLE dbsession;
CREATE ROLE dbsession IDENTIFIED GLOBALLY AS 'AZURE_ROLE=session.role';
GRANT CREATE SESSION TO dbsession;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'DBSESSION';

drop USER hrapp;
CREATE USER hrapp IDENTIFIED GLOBALLY AS 'AZURE_ROLE=hr.app';
GRANT CREATE SESSION, CONNECT, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE to hrapp;
SELECT EXTERNAL_NAME FROM DBA_ROLES WHERE ROLE = 'HRAPP';

21. Create TNS entry for PDB1 over TLS/SSL on database client (23.4 or newer). Note that WALLET_LOCATION is set to SYSTEM, which means that the database client uses the client operating system's trust store to confirm the authenticity of the TLS certificate because the TLS certificate had been signed by a public certificate authority.

PDB1_26ai_ENTRA =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="CN=hrdb.dbauthdemo.com")
(WALLET_LOCATION=SYSTEM)
(TOKEN_AUTH=AZURE_INTERACTIVE)
(TENANT_ID=8d2aef2e-f5e0-46fc-86c2-82604520aea9)
(CLIENT_ID=e5124a85-ac3e-14a4-f2ca-1ad635cf781a)
(AZURE_DB_APP_ID_URI=https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621)
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=pdb1)
)
)

22. Test Entra ID integration authentication to the database. In this example, I use SQLcl 26.1 from my MacOS laptop.

sql -thin /@pdb1_entra

Initiating the connection to the database using the interactive OAuth2 flow pops up a browser window to Entra ID to login. Here you either enter user email and password or select an existing user.

If you selected an existing user, the next step will be dictated by the Entra ID conditional access policies based on several factors including from where your connecting. In my case, it just prompts for the user's password.

The default Entra ID tenancy conditional access policy requires a one-time PIN.

Upon successfully authentication, Entra ID returns an access token through the browser's localhost to the client, with which the client makes another call to Entra ID with the authorization token to request an access token.

Upon receipt of the access token, the database client sends the access token over a TLS encrypted connection to the databaase server. The Oracle database server downloads the current set of keys from Entra ID and confirms the authenticity of the access token and returns the authenticated SQL prompt to the client application. Here, I show the returned SQL prompt along with a few queries to show what it looks like for an authenticated user of a shared user schema.

I hope you find this helpful.

Blessings!