Brad's Blog: 2025

Wednesday, May 28, 2025

TLS for Oracle Real Application Cluster (RAC)

In recent months, I've helped many customers understand Oracle's two new cloud native architectures for centralizing authentication including multi-factor, authorization, and user life cycle management of Oracle database users. These two new architectures enable integration with identity providers Oracle Cloud Infrastructure Identity and Access Management (OCI IAM) and Microsoft's Entra ID.

The interactive SAML flow of this architecture outsources authentication to the identity provider by popping up a browser to the identity provider. Once the authentication browser with the identity provider is complete, an authorization code is returned to the database client. The client then securely sends the access token to the database server over a TLS encrypted connection between the database client and database server. Finally, the database confirms the validity of the access token from the public key that it retrieves from the identity provider. The following diagram illustrates this interactive flow with Entra ID.

The configuration options for the TLS configuration for a standalone database is covered in the following previous blog posts:

Oracle Real Application Cluster (RAC) database system is quite a bit more sophisticated and has unique needs where TLS is concerned compared to a standalone database instance. The RAC cluster is composed of 2 or more hosts (nodes) configured for high availability including built in load distribution through the Single Client Access Name (SCAN) listener.

When a database client connects to the RAC cluster through SCAN listener, the SCAN listener re-directs the client to the local listener specified by LOCAL_LISTENER of the RAC node to which he database client is then connected. The LOCAL_LISTENER is managed by RAC to take into consideration fail-overs and fall-backs which is most easily accomplished by using IP addresses rather than host names.

Given that the client connects via TLS to both the SCAN listener hostname and then to the IP address of the RAC node, the certificate(s) will need to either use a single host cert for all of the RAC nodes and SCAN listener, or a Subject Alternative Name (SAN) certificate that includes the SCAN listener name and all possible RAC node names and the RAC node IP addresses or use a wildcard certificate.

Most customers avoid wildcard certificates like the plague because it of the security risk that it represents by not having unique certificates per host. Therefore, we will distill the RAC certificate options down to the following three options.

Option 1: Single Host Certificate

This option is possible because the database client can validate the certificate by the SSL_SERVER_CERT_DN alone rather than matching the host value to the CN value of the certificate subject or one of the certificate Subject Alternative Name (SAN) entries. Let's consider the following TNS entry:

Let's start with looking up the certificate subject from database server with orapki:

$ orapki wallet display -complete -wallet $WALLET_ROOT/tls -pwd Oracle123|egrep "Certificates:|Subject:"
Requested Certificates:
User Certificates:
Subject: C=US,ST=Texas,L=Austin,O=MyCo,CN=scan.myco.com
Trusted Certificates:
Subject: CN=Certificate Authority,O=Company,L=Austin,ST=Texas,C=US

Alternatively, you can retrieve the certificate subject with openssl form a UNIX/Linux host:

The client TNS entry would look like the following where key TLS properties are highlighted in blue and the SSL_SERVER_CERT_DN is set to the exact subject DN of the certificate.

PDB1 =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCPS)(HOST=scan.myco.com)(PORT=2484))
)
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(SSL_SERVER_CERT_DN="C=US,ST=Texas,L=Austin,O=MyCo,CN=scan.myco.com")
(WALLET_LOCATION=SYSTEM)
(TOKEN_AUTH=AZURE_INTERACTIVE)
(TENANT_ID=7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1)
(CLIENT_ID=e5124a85-ac3e-14a4-f2ca-1ad635cf781a)
(AZURE_DB_APP_ID_URI=https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621)
)
(CONNECT_DATA=
(SERVICE_NAME=pdb1)
)
)

Option 2: Single SAN Certificate

The single Subject Alternative Name (SAN) certificate is slightly more secure because you incorporate all of the possible names and IP address of the RAC cluster into the SAN entries of a single certificate where the subject of the certificate includes the SCAN listener host name for it's canonical name (CN) value.

Let's start with looking up the certificate subject from database server with orapki:

Alternatively, you can retrieve the certificate subject and SAN entries with openssl form a UNIX/Linux host:

echo | openssl s_client -connect scan.myco.com:2484 2>&1 | openssl x509 -noout -text | egrep "DNS:|IP:|Alternative|Subject:"
Subject: C=US,ST=Texas,L=Austin,O=MyCo,CN=scan.myco.com
X509v3 Subject Alternative Name:
DNS:node1.myco.com, DNS:node2.myco.com, DNS: node1, DNS: node2 IP: 10.0.0.10, IP 10.0.0.20

Note in the above openssl output, that the DNS and IP SAN entries are listed as well and that they include the fully qualified host names, unqualified host names and IP addresses of the RAC nodes. This is important because clients can sometimes connect directly to a RAC node rather than through the SCAN listener. In this case, you wan the certificate to find a match in the SAN entries to complete the TLS connection.

The client TNS entry would look like the following where key TLS properties are highlighted in blue and the SSL_SERVER_CERT_DN is not present because the DN matching is just looking to find a match of ADDRESS HOST value with the CN value of the certificate subject name or any of the SAN entries.

PDB1 =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCPS)(HOST=scan.myco.com)(PORT=2484))
)
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(WALLET_LOCATION=SYSTEM)
(TOKEN_AUTH=AZURE_INTERACTIVE)
(TENANT_ID=7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1)
(CLIENT_ID=e5124a85-ac3e-14a4-f2ca-1ad635cf781a)
(AZURE_DB_APP_ID_URI=https://dbauthdemo.com/16736175-ca41-8f33-af0d-4616ade17621)
)
(CONNECT_DATA=
(SERVICE_NAME=pdb1)
)
)

Option 3: Individual SAN Certificates Per RAC Node

The individual SAN certificate is again more secure because you specify a host-specific set of SAN entries for that host's names and IP address.

Let's start with looking up the certificate subject and SAN entries from the first RAC node with openssl from a UNIX/Linux host:

Next, lets lookup the certificate subject and SAN entries from the second RAC node with openssl from a UNIX/Linux host:

A similar RAC node specific certificate would be created and installed on each respective RAC node and respective listener.

The client TNS entry would be the same as the Option 2 because the difference in the two options is the contents of the certificate.

If you encounter issues with any of these configurations or EntraID integration, check out my blog posts on troubleshooting these areas:

Those are the three certificate options for RAC clusters.

I hope you found this information useful.

Blessings!

Brad

Oracle Net Service Name Resolution Options

Oracle database name resolution is provided by Oracle Net Services. Oracle Net Services connect string (a.k.a. connect descriptor) resolution is to Oracle database clients what Domain Name Services (DNS) is to TCP/IP clients for host name to IP address resolution. For example, with DNS a TCP/IP client would resolve a hostname like hrdb.dbauthdemo.com to an IP address like 10.10.0.1. In the same way, an Oracle database client would resolve through Oracle Net services a service name alias like pdb1 to a connect string like the following:

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

In the above sample connect string, the pdb1 service name resolves to a connect string for enabling a database client to connect to the pdb1 service name via TCP to host hrdb.dbauthdemo.com on port 1521.

As customers expand their Oracle database estate and transition Oracle databases from on premises to one or more clouds and strengthen their security posture through enabling TLS encryption or enabling multi-factor authentication, the number and complexity of connect strings will increase dramatically over time. Therefore, it is important to ensure accurate and up-to-date name resolution through centralizing Oracle database server name resolution.

Net Service Name Resolution Options

The most common net service naming resolution configuration approaches include the following.

1. Independent Client Configs

Every database client independently specifies a connect string or maintains name service entry it's own local tnsnames.ora file. Using previous example, here is a sample connect string for the service name alias pdb1:

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

Here is an equivalent net service connect descriptor entry from a tnsnames.ora file:

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

2. Unified tnsnames.ora File

With this option, the database administrative team creates and maintains a single tnsnames.ora file that is either published for internal reference or distributed to all database clients. This method is better than independently managed connect strings and tnsnames.ora files but is also fraught with possible data inconsistency over time because the clients don't necessarily have to refresh their tnsnames.ora files with the most current version. There can also be security concerns with method used to propagate the tnsnames.ora file to database clients.

3. Centralized Directory Naming

This is one of the most common options employed by customers with a large number of database servers and clients that need to be kept up-to-date. In this solution, you setup one or more LDAP-based directory service instances according to your high availability requirements and load the directory service with your TNS entries using either dbca tool that comes with the Oracle database or create your own custom TNS entries.

The main directory service products employed for this architecture include Oracle Unified Directory (OUD) and Oracle Internet Directory (OID) though others could be used as well. One important note on directory server product selection is that Active Directory (AD) can technically be used for this purpose but Microsoft strongly discourages extending AD schema or loading non-Microsoft data in AD because it can put AD upgrades at risk. I've worked with hundreds of customers on this use case and I've yet to meet a single large customer that allows this data to be loaded into their production AD service.

Regarding high availability, the recommended maximum high availability architecture includes use local and geographic load balancers that support proper LDAP service tests. From a security perspective, note that exclusive use of encrypted LDAPS connections is accomplished by not including non-encrypted port in the DIRECTORY_SERVERS ldap.ora. Further, authenticated lookups is available as well using additional properties for basic authentication are supported for LDAP-based name resolution as well.

An example configuration using OUD 12cPS4 is covered at the following blog post:https://www.braddiggs.com/2023/10/oud-12cps4-use-case-oracle-database.html

The equivalent net service connect descriptor entry for the pdb1 alias from a directory services looks like the following:

dn: cn=pdb1,cn=OracleContext,dc=hrdb.dbauthdemo,dc=com
objectclass: orclApplicationEntity
objectclass: orclDBServer_92
objectclass: orclService
objectclass: orclDBServer
objectclass: top
orclNetDescString: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hrdb.dbauthdemo.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1))

4. Centralized Configuration Provider [New with 23ai]

This is a new option was introduced with Oracle database version 23ai that enables connect descriptors to be loaded into HTTPS based object storage in JSON format.

There are several restrictions namely that it only works with 23ai and newer version clients and that the WALLET_LOCATION only supports SYSTEM. Only supporting SYSTEM for the WALLET_LOCATION means that all certificates must be signed by a public certificate authority or your private certificate authority chain must be loaded into the host trust store where the database client is run. This new feature along with the full set of restrictions is covered at the following link: https://docs.oracle.com/en/database/oracle/oracle-database/23/netrf/centralized-configuration-provider-naming-parameters.html

The equivalent net service connect descriptor entry of the pdb1 alias in JSON format for centralized configuration provider looks like the following:

{
"connect_descriptor": "(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=hrdb.dbauthdemo.com)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=pdb1))
)"
}

A JDBC reference to the above entry might look like:

jdbc:oracle:thin:@config-https://objectstorage.us-phoenix-1.oraclecloud.com/n/orasenatdpltintegration03/b/azure/o/pdb1.json

A more robust list of examples of the available configuration providers are available at the following blog post: https://blogs.oracle.com/developers/post/jdbc-config-providers

I hope you find this information helpful.

Blessings!

Braed

Wednesday, May 7, 2025

Entra ID Integration for TOAD

Shout out to Jason Gay for figuring out and sharing the TOAD configuration for adding an Entra ID enabled Oracle database.

The Tools for Oracle Database Administration and Development (TOAD) application is a frequently used SQL tool used by developers and database administrators for browsing and managing Oracle database data. TOAD appears to leverage the Oracle Call Interface (OCI) libraries included with the Oracle full and instant database clients. These are the same libraries leveraged by the very popular SQL*Plus command line tool that is includes with the Oracle database client.

To add an Entra ID integration enabled Oracle database to TOAD, us the following configuration parameters:

User: EXTERNAL
TNS: Select database alias from pull-down
Connect as: Normal
Connect using Oracle Client: <ODB 23.5+ full or instant client>
Check: Use this client by default

Here is a screen shot of what it looks like filled out except the selection of the database alias from the tnsnames.ora file.

I hope you found this post helpful and informative.

Blessings!

Brad

Wednesday, April 23, 2025

ODB Entra ID Integration Troubleshooting Guide

I've spent several weeks working through a wide variety of Oracle database integration use cases with Entra ID for the express purpose of methodically deriving all possible error codes that that a customer might encounter during an evaluation. I will list each error followed by the one or more potential causes.

Before getting started with the errors, know that you can enable client and server side tracing to reveal underlying root causes of errors.

Client-side Trace for SQL*Plus

Either add the following to sqlnet.ora or set as environment variables the following before attempting to connect to the database server:

EVENT_25701=15
EVENT_25703=15

Note that 15 is for high level tracing and 14 for low level tracing.

Database Server Side Trace for non-autonomous databases

Set the following in the CDB:

SQL> ALTER SYSTEM SET EVENTS 'TRACE[TOKEN] DISK=HIGHEST';

Database Server Side Trace for autonomous databases

Review the alert log. For example:

SQL> SELECT FROM V$DIAG_ALERT_EXT ORDER BY originating_timestamp desc;

SQL> SELECT * FROM UNIFIED audit_trail ORDER BY event_timestamp desc fetch first 100 rows only;

SQL> CREATE AUDIT POLICY ORA LOGON_SUCCESSES ACTIONS LOGON;

SQL> AUDIT POLICY ORA_LOGON_SUCCESSES WHENEVER SUCCESSFUL;

Error Number Documentation

Note that most errors provide additional help at the documentation page for the respective error code.

For example : https://docs.oracle.com/error-help/db/ora-<number>

Database Name Resolution

Database name resolution is typically provided by the explicit connect string, exclusively tnsnames.ora for JDBC-thin driver clients, a combination of sqlnet.ora and tnsnames.ora for JDBC-thick and OCI (C-based) client drivers, or LDAP-based name services. Information on LDAP-based database name services are available at the following blog post: https://www.braddiggs.com/2023/10/oud-12cps4-use-case-oracle-database.html

Best Practice: Regardless of the name resolution repository, the best practice is to include all elements of connection in either the explicit connect string or TNS entry.

Here is a human friendly formatted TNS entry for database PDB1. The blue font items are required for the encrypted TCPS TLS connection. TLS connections between client and server are required for Entra ID integration. The red font items are unique to Entra ID connections.

PDB1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))
(SECURITY=
(SSL_SERVER_DN_MATCH=TRUE)
(WALLET_LOCATION=SYSTEM)
(TOKEN_AUTH=AZURE_INTERACTIVE)
(TENANT_ID=7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1)
(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)
)
)

Here is the equivalent explicit connect string being called with the connect SQL command:

SQL> connect (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))(SECURITY=(SSL_SERVER_DN_MATCH=TRUE)(WALLET_LOCATION=SYSTEM)(TOKEN_AUTH=AZURE_INTERACTIVE)(TENANT_ID=7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1)(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)))

Error Messages:

Here are the list of errors that I've encountered while troubleshooting Entra ID integration issues.

Error: ORA-01017: invalid username/password; logon denied

Potential reasons for this error:

Invalid username or password provided
The server side configuration of the target database of the client connection (CDB or PDB) may not yet be configured for Entra Integration including setting IDENTITY_PROVIDER_TYPE to AZURE_ID and applying Entra ID integration to IDENTITY_PROVIDER_CONFIG
The connection string or TNS entry of the client may not include all requisites for Entra ID integrated database

Error: ORA-12154: Cannot connect to database. Cannot find alias <db> in

<dir>\tnsnames.ora.

Potential reasons for this error:

The tnsnames.ora file does not exist in <odb_client_base>\network\admin or that the specified database does. Note that you can specify TNS_ADMIN=\<path> to specify the folder path to the tnsnames.ora file
The requested database does not exist in the tnsnames.ora file.

Error: ORA-12170: Cannot connect. - timeout of -s for host <ip_address> port <secure_port>.

(CONNECTION_ID=<connection_id>)

Potential reasons for this error:

The SQL*Plus command cannot connect to the host specified in the TNS or connect string entry
Check network firewall or host-based firewall ingress rules allow the host of the database client to connect to the host of the database server
Check the network firewall or host-based firewall egress rules do not allow the database client or server to connect to the database server's host

ORA-12541: Cannot connect. No listener at host <ip> port <port>
Potential reasons for this error:

Oracle database listener is not running or ingress firewall rules (network or host-based) are blocking access to the database port.

Error: ORA-12545: Connect failed because target host or object does not exist

Potential reasons for this error:

Can’t lookup specified connect string. Check name service resolution in tnsnames.ora or directory service
Make sure TNS_ADMIN is set to the directory where tnsnames.ora exists
You get the following Microsoft Entra ID error when you specify an invalid client ID.

Error: ORA-12547: TNS:lost contact

Potential reasons for this error:

Occurs if you specify PROTOCOL of TCP instead of TCPS in connect string or TNS entry

Error: ORA-12514: Cannot connect to database. Service <service_name> is not registered with the

listener at host <hostname_or_ip_address> port <tcps_tls_port>

(CONNECTION_ID=Kittn+/2RPGXnZPxZAV9Hw==)

Potential reasons for this error:

Occurs when incorrect SERVICE_NAME value is specified in connect string or TNS entry

Error: ORA-18726: Failed to get a value from an OracleResourceProvider: No implementation of oracle.jdbc.spi.AccessTokenProvider with name: "ojdbc-provider-azure-token" can be located. Implementations of oracle.jdbc.spi.AccessTokenProvider that can be located have the following names : []

Potential reasons for this error:

In the case of SQLDeveloper, this can occur if the SQLDeveloper product.conf configuration file does not have all of the requisite OJDBC extension libraries required for Entra ID integration. See this blog post (https://www.braddiggs.com/2025/04/entra-id-integration-for-sqldeveloper.html) for details on how to configure SQLDeveloper for EntraID integration
This can result from using a version of the SQL*Plus client earlier than 23.7.
Note that if the connect string or TNS entry specifies TOKEN_AUTH=AZURE_DEVICE_CODE , this may indicate that AZURE_DEVICE_CODE is not yet supported

Here is the equivalent Entra ID error when logging in with SQLcl 25.1:

To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code ARSX62NBJ to authenticate.

2025-04-16 09:20:46.198 SEVERE com.azure.core.util.logging.ClientLogger performDeferredLogging Azure Identity => ERROR in getToken() call for scopes [https://dbauthdemo.com/89746215-ea34-4a83-ac0e-46a68de97935/.default]: Failed to acquire token with device code.

Connection failed

USER =

URL = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.dbauthdemo.com)(PORT=2484))(SECURITY=(SSL_SERVER_DN_MATCH=TRUE)(WALLET_LOCATION=SYSTEM)(TOKEN_AUTH=AZURE_INTERACTIVE)(TENANT_ID=7f4c6e3e-a1e0-43fe-14c5-c2f051a0a3a1)(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)))

Error: ORA-18951: NL exception was generated: TNS-04610: No literals left, reached end of NV pair

Potential reasons for this error:

Occurs when attempting to connect via thin client with TOKEN_AUTH=AZURE_DEVICE_CODE and the previous authentication was successful but failed to get token (Error: ORA-18726)

Error: ORA-25704: Token-based authentication requires client parameter SSL_SERVER_DN_MATCH=TRUE.

Potential reasons for this error:

All Entra ID integration connections between Oracle database client require encrypted TLS connection with SSL_SERVER_DN_MATCH=TRUE

Error: ORA-25707: The token is invalid

Potential reasons for this error:

If using file-based access token acquisition and refreshing, the token is not present, invalid or expired
If using memory-based access token acquisition and refreshing, the token may not have been passed because of an issue with TLS configuration setup between the database client and database server.
Use client and server side tracing to ascertain root cause.

Error: ORA-25714: invalid value of PASSWORD_AUTH or TOKEN_AUTH in the client configuration

Potential reasons for this error:

OCI driver (e.g. sqlplus) or JDBC-thick driver version used is not supported. SQL*Plus 19.18 or newer is required
JDBC-thin driver appears to only work with 23.7 or newer

Error: ORA-25718: Invalid value of CLIENT_ID in the client configuration

Potential reasons for this error:

Occurs when CLIENT_ID is invalid format or bad value in connect string or TNS entry

You may also see Entra ID Error message AADSTS90016:

Error: ORA-25719: Invalid value of TENANT_ID in the client configuration

Potential reasons for this error:

Occurs if the TENANT_ID is incomplete or invalid format in the connect string or TNS entry

Error: ORA-25726: Invalid value of AZURE_CREDENTIALS in the client configuration

Potential reasons for this error:

Occurs when no client credentials have been entered for TOKEN_AUTH=AZURE_SERVICE_PRINCIPAL

Error: ORA-25724: Azure AD authentication timeout and Vendor Code: 25724

Potential reasons for this error:

Occurs when pop-up browser connection to https://login.microsoftonline.com times out after 60 seconds
Test connection multiple times from your computer to Microsoft login with:

Linux: curl -ksL https://login.microsoftonline.com/
Windows Powershell: Invoke-WebRequest -Uri https://login.microsoftonline.com

Note that web connection test may require going through a web proxy if direct outbound connections are not permitted
If connection is successful some of the time, there may be a routing or firewall issue between your computer and Microsoft's login page
If connection fails every time, there is likely either a firewall constraint, routing issue or Microsoft login may be temporarily down
In Microsoft Terminal Services environments like Microsoft RDP or Citrix, the ODB client redirect to localhost (127.0.0.1) may not be connecting to the user's session real localhost (127.0.0.2-n). The respective terminal services solution will need to be configured to connect the two for the requesting client application. In this case determine what port that the web browser used to connect to localhost after successful EntraID authentication (typically localhost:8400 but can be other number) and then use (netstat -ano | findstr "<port>") to find the process that is trying to connect to that port. Note that both the web browser (Edge or Internet Explorer by default in Windows Server depending on version) and the identified process will need to be added to the Windows or Citrix policy for connecting the virtual localhost (127.0.0.1) and real user session localhost (127.0.0.n). Here is a screenshot from the Citrix policy:

Error: ORA-28547: connection to server failed, probable Oracle Net admin error

Potential reasons for this error:

Combination ORA and Entra ID Error message AADSTS90009:

or Entra ID Error message AADSTS90002:

This error occurs if you have not specified the correct TENANT_ID in the connect string or TNS entry
Entra ID authentication attempt either timed out or failed to authenticate properly
Occurs when incorrect CLIENT_ID value is specified in connect string or TNS entry

Error: ORA-28759: failure to open file

Potential reasons for this error:

WALLET_LOCATION of the connect string or TNS entry has specified a wallet path for which no wallet exists

Error: ORA-28864: SSL connection closed gracefully

Potential reasons for this error:

If you’ve specified the non-secure port number (Default: 1521) instead of the secure port number (Default: 2484) in the connect string or TNS entry
Note that the default autonomous database uses port 1521 for secure port

Error: ORA-28865: SSL connection closed

Potential reasons for this error:

The database server may have not yet set the wallet_root directory
Check to make sure that the wallet has been copied to the PDB GUID directory ($WALLET_ROOT/$pdbguid/tls) on the database server

Set the database server wallet with:

sqlplus / as sysdba
SQL> alter system set wallet_root='/u01/app/oracle/19c/wallet_root' scope=spfile;
SQL> shutdown immediate;
SQL> quit;
lsnrctl stop
lsnrctl stop
sqlplus / as sysdba
SQL> startup;
SQL> alter pluggable database all open;
SQL> alter system register;
SQL> quit;

Error: ORA-29003: SSL transport detected mismatched server certificate.

Potential reasons for this error:

Occurs the SSL_SERVER_CERT_DN value of the connect string or TNS entry does not match the certificate subject or subject alternative name (SAN) entries of the certificate
You can use the openssl command of a Linux host to ascertain the certificate and SAN entries of the database server with:

$ echo | openssl s_client -connect hrdb.dbauthdemo.com:2484 2>&1 | openssl x509 -noout -text | egrep -i "DNS|subject"
Subject: CN=hrdb.dbauthdemo.com
Subject Public Key Info:
X509v3 Subject Alternative Name:
DNS:hrdb.dbauthdemo.com, DNS:www.hrdb.dbauthdemo.com
X509v3 Subject Key Identifier:

Error: ORA-29024: Certificate validation failure

Potential reasons for this error:

This error can be caused by the signing certificate authority not being present in the client host certificate authority trust store
Use orapki on the database host to display the trusted certificate subject and confirm that this trusted certificate is in the client host

orapki wallet display -complete -wallet $WALLET_ROOT/tls -pwd <wallet_password>

In Windows client, run mmc, add “Certificates” snap-in, look up “Trusted Root Certification Authorities” to see if the DN o the certificate authority matches the subject of the same certificate authority from orapki display
In Linux, return all of the CA certificate subjects with: openssl crl2pkcs7 -nocrl -certfile /etc/pki/tls/certs/ca-bundle.trust.crt | openssl pkcs7 -print_certs -noout

Thursday, April 17, 2025

Entra ID Integration For SQL*Plus

SQL*Plus is the most commonly used command line tool for the Oracle database. This tool uses the Oracle Call Interface (OCI) libraries and APIs to connect to the database. The SQL*Plus comes in two distributions. First, it is included with the full Oracle Database Server and full Oracle Database Client, which is available through Oracle Software Delivery Cloud. Second, SQL*Plus is an optional package of the Oracle instant client distribution that can be downloaded and installed along with the Oracle instant client. The Oracle instant client typically has a newer version of SQL*Plus that the full client. However, two important things not included with the Oracle instant client include a JDK or the orapki command for certificate and wallet management. Fortunately, there are options for working around these limitations as my blog on the missing orapki tool reveals.

Install SQL*Plus Instant Client

1. Download the Basic Package of the Oracle Instant Client and the SQL*Plus Package from here

2. Extract the Oracle Instant into your preferred path. In my lab, I extracted it into "C:\u01\app\instantclient_23_7"

3. Extract the SQL*Plus Package into the same path as the previous step

Install SQL*Plus Full Client

1. Download the through full Oracle Database client from Oracle Software Delivery Cloud

2. Extract the the full database client zip file (V1045016-01.zip for Windows) into the desired location. In my lab, I extracted the full database client into "C:\u01\app\fullcient_23.5"

3. Run the setup tool in the <odb_client_base> directory. In my case, "C:\u01\app\fullcient_23.5\setup.exe"

4. Complete the setup process plugging in your preferred location for the client installation. Here are the screenshots from my setup where C:\u01\app is the Oracle base that I chose for my install.

Configure SQL*Plus Full Client For Entra ID Integration

1. Setup TLS client-side configuration for secure TLS encrypted connection to the Oracle database server. This requires that the SQL*Plus have a trust store loaded with the self-signed, privately signed or publicly signed certificate of the Oracle database server. If the Oracle database server certificate is signed by a public certificate authority, then your TNS entry or connect string for the oracle database server can use WALLET_LOCATION=SYSTEM because SQL*Plus is outsourcing the trust store to the trust store of the operating system of the host where SQL*Plus is running. Otherwise, a wallet containing the self-signed or privately signed certificate will need to be setup on this host and WALLET_LOCATION will be set to the full path to that wallet folder. For example, I use the following in my WALLET_LOCATION=C:\Users\DBClient\Desktop\tls. Note that this wallet will need auto-enable setup on the wallet so that SQL*Plus won't be prompted for the wallet password every time that SQL*Plus uses that wallet. Here is the sample invocation of orapki to enable auto_login on the wallet.

orapki wallet create -wallet $WALLET_ROOT/tls -pwd Oracle123 -auto_login

More detailed instructions on how to create the client wallet for TLS communication are available on my One-way TLS ODB Client/Server With Self-signed Certificates In Wallets blog post.

2. Add TNS entries into the "<odb_client_base>\network\admin\tnsnames.ora" configuration file for the Entra ID entabled Oracle database servers. The key distinction of Entra ID enabled entries are that they will have TLS configured requiring certificate DN matching and will include the Entra ID specific properties for identifying the SAML authentication flow, tenancy, client, and server IDs.

For both the instant and full clients, the tnsnames.ora is located in the <client_base>\network\admin folder.

In my case for instant client:

C:\u01\app\instantclient_23_7\network\admin\tnsnames.ora

In my case for full client:

C:\u01\app\fullclient_23.5\network\admin\tnsnames.ora

For your reference, here is a sample tnsname.ora entry to illustrate the requisite requirements for an Entra ID integrated connection string.

The key differences to note from a normal service description are the requirement for encrypted (e.g. TLS based TCPS) connection and Entra ID configuration.

The TLS encryption is required between the database client and server to securely pass the access token between the client and server. These settings are highlighted in blue.

The Entra ID configuration is highlighted in red where:

TOKEN_AUTH specifies the SAML authentication flow, which is interactive in this case
TENANT_ID specifies the ID of the Azure/Entra ID tenancy
CLIENT_ID specifies the registered Entra ID web application for the database client
AZURE_DB_APP_ID_URI specifies the URI of the registered Entra ID web application of the database server

Note that WALLET_LOCATION is set to SYSTEM. This means that the database server's TLS certificate is signed by a public certificate authority for which the operating system already contains a copy of the certificate authorities certificate chain in the operating system trust store. Therefore, a local wallet file containing the certificate authority's certificate chain or a self-signed certificate from the database server.

If the database server's certificate is self-signed or signed by a private certificate authority, then you will instead need to specify the path to the wallet that includes this information. Here is an example of what the WALLET_LOCATION would look like in the TNS entry if you had to specify a path to the wallet folder:

(WALLET_LOCATION=C:\Users\DBClient\Desktop\tls)

Also, in addition to specifying that the TLS certificate subject or one of the subject alternative name entries (SAN) must match the HOST value of the service description (e.g. SSL_SERVER_DN_MATCH=TRUE), you can also specify the full DN of the certificate with the following where the value of SSL_SERVER_CERT_DN highlighed in red would need to exactly match the subject of the server's certificate:

(SSL_SERVER_CERT_DN="CN=hrdb.dbauthdemo.com")

If you are not certain what the value of the server's certificate is, you can determine it from a UNIX/Linux system with the openssl tool. Here is a sample invocation for your reference:

Use SQL*Plus With Entra ID Integration

1. Connect to a database server through a SQL prompt or command invocation of SQL*Plus. For example:

cd C:\u01\app\instantclient_23_7
.\sqlplus /nolog
SQL> conn /@pdb1

or alternatively with a full command invocation for the instant client install:

cd C:\u01\app\instantclient_23_7
.\sqlplus /@pdb1

or alternatively with a full command invocation for the full client install:

cd C:\u01\app\fullclient_23.5
.\bin\sqlplus /@pdb1

2. A web browser window will open to the Microsoft login. You select the desired account or add a new one. If you don’t already have an authenticated session, it will prompt you for your password followed by entering in the code from the Microsoft authenticator application.

3. Once successfully authenticated, the web browser window will indicate that authentication is complete and that you can close the web browser window.

At this point, the structure of database in SQL*Plus will be revealed in the Connections panel in accordance with the permissions granted to your user.

I hope you found this post helpful and informative.

Blessings!

Brad

Entra ID Integration For SQLDeveloper

SQLDeveloper has been one of the preferred Integrated Development Environment (IDE) for Oracle developers, database administrators and power users for decades. This powerful tool has been recently supplemented with the ability to centralize authentication, authorization, and user life cycle database management of Oracle database users through cloud native integration with Microsoft Entra ID. This integration is made possible through the Azure (Entra ID) resource extensions available from the ojdbc-extensions libraries. The setup of SQLDeveloper for Entra ID integration is straight forward. Much of what you see in this blog post was a distillation and simplification of the work produced by Ty Stahl from his blog post of the same topic.

Setup SQLDeveloper For Entra ID Integration

1. The first step is to download SQLDeveloper and extract SQLDeveloper into your desired location. In my lab, this is C:\Program Files\sqldeveloper.

2. The next step is to build ojdbc-extensions with Apache Maven or download them and place them in a subdirectory of <sqldeveloper_base> folder. For example, I extracted the downloaded extensions into C:\Program Files\sqldeveloper such that the resulting ojdbc-extensions libraries are in the "C:\Program Files\sqldeveloper\sdks\jdbc-azure" folder.

3. Next, start and then stop SQLDeveloper in order to create the SQLDeveloper product.conf configuration file in <home>\AppData\Roaming\sqldeveloper\<version>.

4. Update the "<home>\AppData\Roaming\sqldeveloper\<version>\product.conf" file with individual AddJavaLibFile reference for each of the ojdbc-extensions jar files. In my case, here are the following entries added:

AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\accessors-smart-2.5.1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\asm-9.6.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-core-1.54.1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-core-http-netty-1.15.7.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-data-appconfiguration-1.7.3.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-identity-1.14.2.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-json-1.3.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-security-keyvault-secrets-4.9.1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\azure-xml-1.1.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\content-type-2.3.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-annotations-2.13.5.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-core-2.13.5.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-databind-2.13.5.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-datatype-jsr310-2.15.2.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jackson-module-jaxb-annotations-2.12.2.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jcip-annotations-1.0-1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jna-5.13.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\jna-platform-5.13.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\json-smart-2.5.1.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\lang-tag-1.7.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\msal4j-1.17.2.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\msal4j-persistence-extension-1.3.0.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-buffer-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-dns-4.1.112.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-http-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-http2-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-codec-socks-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-common-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-handler-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-handler-proxy-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-resolver-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-resolver-dns-4.1.112.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-resolver-dns-classes-macos-4.1.112.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-resolver-dns-native-macos-4.1.112.Final-osx-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.62.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-linux-aarch_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-linux-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-osx-aarch_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-osx-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-boringssl-static-2.0.69.Final-windows-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-tcnative-classes-2.0.69.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-classes-epoll-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-classes-kqueue-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-native-epoll-4.1.115.Final-linux-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-native-kqueue-4.1.115.Final-osx-x86_64.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\netty-transport-native-unix-common-4.1.115.Final.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\nimbus-jose-jwt-9.40.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\oauth2-oidc-sdk-11.18.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\ojdbc-provider-azure-1.0.4.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\ojdbc-provider-common-1.0.4.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\ojdbc8-23.4.0.24.05.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\reactive-streams-1.0.4.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\reactor-core-3.4.41.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\reactor-netty-core-1.0.48.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\reactor-netty-http-1.0.48.jar
AddJavaLibFile C:\Program Files\sqldeveloper\sdks\jdbc-azure\slf4j-api-1.7.36.jar

Use SQLDeveloper With Entra ID Integration

Once SQLDeveloper has been setup for Entra ID integration, the next step is to add Entra ID enabled Oracle databases to the configuration and use them.

1. Open SQLDeveloper

2. Add an Entra ID enabled database by clicking on the green plus symbol in the upper lefthand corner of the SQLDeveloper app and fill out the fields as follows:

Name: <datbase_name>
Database Type: Oracle
Authentication Type: OS
Connection Type: TNS
Network Alias: <TNS_Alias_Name>

Here is a sample screenshot from my lab environment:

3. Once the database has been added, double-click on the database in the "Connections" panel to initiate the authentication process. In my case, PDB1.

4. A web browser window will open to the Microsoft login. You select the desired account or add a new one. If you don’t already have an authenticated session, it will prompt you for your password followed by entering in the code from the Microsoft authenticator application.

5. Once successfully authenticated, the web browser window will indicate that authentication is complete and that you can close the web browser window.

At this point, the structure of database in SQLDeveloper will be revealed in the Connections panel in accordance with the permissions granted to your user.

Troubleshooting

For your reference, here is a sample tnsname.ora entry to illustrate the requisite requirements for an Entra ID integrated connection string.

The key differences to note from a normal service description are the requirement for encrypted (e.g. TLS based TCPS) connection and Entra ID configuration.

The TLS encryption is required between the database client and server to securely pass the access token between the client and server. These settings are highlighted in blue.

The Entra ID configuration is highlighted in red where:

TOKEN_AUTH specifies the SAML authentication flow, which is interactive in this case
TENANT_ID specifies the ID of the Azure/Entra ID tenancy
CLIENT_ID specifies the registered Entra ID web application for the database client
AZURE_DB_APP_ID_URI specifies the URI of the registered Entra ID web application of the database server

(WALLET_LOCATION=C:\Users\DBClient\Desktop\tls)

(SSL_SERVER_CERT_DN="CN=hrdb.dbauthdemo.com")

If you are not certain what the value of the server's certificate is, you can determine it from a UNIX/Linux system with the openssl tool. Here is a sample invocation for your reference:

I hope you found this post helpful and informative.

Blessings!

Brad