Brad's Blog

Wednesday, November 6, 2024

One-way TLS ODB Client/Server With Self-signed Certificates In OS Truststore

In recent months, I've helped many customers understand there options for centralizing Oracle Database authentication. One of those options requires Transport Layer Security (TLS) connection between the database client and database server in order to securely pass an access token to the database server. When customers want to evaluate this solution, one of their challenges is how to setup TLS between the database and server for a proof of concept or internal validation. There are three approaches that customers can take for setting up TLS between client and server:

1. Self-signed private certs with wallets - For demonstrations and proofs of concept

2. Self-signed private certs with host trust store - Next step toward production

3. Publicly signed certs - For production

The focus of this blog post is on the second of the three.

Here's the streamlined workflow that I use for setting up one-way TLS between Oracle Database client and server where the client wallet is distributed to Oracle Database clients. This comes from the Oracle Database security guide at https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/configuring-transport-layer-security-encryption.html#GUID-03F216A2-76E0-47C9-9751-6F2D39BD75A1

As a side note, if you are not familiar with the WALLET_ROOT database parameter, please familiarize yourself with it at https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/WALLET_ROOT.html because it plays a large role in several security configuration options of the Oracle database including configuring TLS.

When specifying the SSL_CIPHER_SUITES in the database listener.ora and client tnsnames.ora, you will want to select the strongest cipher suites from the desired SSL_Version (1.3 or 1.2) that you want to support that all of the database clients support. In this example, we will use version 1.2 and cipher suite TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384.

The full list of supported SSL_VERSION and SSL_CIPHER_SUITES values per database version are available here:

19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/oracle-net-listener-parameters-in-listener-ora.html#GUID-1BCF9D1F-76D5-456A-B3DA-EC77C1C379E2

23ai: https://docs.oracle.com/en/database/oracle/oracle-database/23/netrf/oracle-net-listener-parameters-in-listener-ora.html#GUID-1BCF9D1F-76D5-456A-B3DA-EC77C1C379E2

Step 1: Setup Certificate Authority Server

In order to simplify this setup for a large number of databases, we first setup a private certificate authority (CA) on which we will create and sign individual database certificates. This setup presumes that the Oracle Database software is installed on the host where orapki will be run.

1.1 Setup the environment variables on the dbca host.

export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="$ORACLE_BASE/dbhome_1"
export WALLET_ROOT="$ORACLE_BASE/wallet_root"
PATH=$ORACLE_HOME/bin:$PATH

1.2 Create the root certificate authority wallet with a arbitrary distinguished name that makes it clear that it is the certificate authority. For example, DN=root_ca.

mkdir -p $WALLET_ROOT/rootca_wallet/tls
orapki wallet create -wallet $WALLET_ROOT/rootca_wallet/tls -pwd Oracle123 -auto_login
orapki wallet add -wallet $WALLET_ROOT/rootca_wallet/tls -pwd Oracle123 -dn CN=root_ca -keysize 2048 -sign_alg sha256 -self_signed -validity 3500
orapki wallet export -wallet $WALLET_ROOT/rootca_wallet/tls -pwd Oracle123 -dn CN=root_ca -cert $WALLET_ROOT/rootca_wallet/root_wallet.crt

Step 2: Generate and sign DB and client certificates

2.1 Create database server wallet and sign database server (hrdb) certificate. Note that the distinguished name of the database can be completely arbitrary. In this example (CN=db_19c_hr), I encapsulated the role (db), version (19c) and application (hr). It could have just as easily been CN=db_hr.

mkdir -p $WALLET_ROOT/hrdb_wallet/tls
orapki wallet create -wallet $WALLET_ROOT/hrdb_wallet/tls -pwd Oracle123 -auto_login
orapki wallet add -wallet $WALLET_ROOT/hrdb_wallet/tls -pwd Oracle123 -trusted_cert -cert $WALLET_ROOT/rootca_wallet/root_wallet.crt
orapki wallet add -wallet $WALLET_ROOT/hrdb_wallet/tls -pwd Oracle123 -keysize 2048 -dn CN=db_19c_hr
orapki wallet export -wallet $WALLET_ROOT/hrdb_wallet/tls -pwd Oracle123 -dn CN=db_19c_hr -request $WALLET_ROOT/hrdb_wallet/db_19c_hr.csr
orapki cert create -wallet $WALLET_ROOT/rootca_wallet/tls -pwd Oracle123 -request $WALLET_ROOT/hrdb_wallet/db_19c_hr.csr -cert $WALLET_ROOT/hrdb_wallet/db_19c_hr.crt -validity 3500 -sign_alg sha256
orapki wallet add -wallet $WALLET_ROOT/hrdb_wallet/tls -pwd Oracle123 -user_cert -cert $WALLET_ROOT/hrdb_wallet/db_19c_hr.crt

2.2 Create client wallet and load with certificate authority root certificate. The purpose of the client wallet is to create a wallet that has the signing certificate authority's certificate (or certificate chain).

mkdir -p $WALLET_ROOT/client_wallet/tls
orapki wallet create -wallet $WALLET_ROOT/client_wallet/tls -pwd Oracle123 -auto_login
orapki wallet add -wallet $WALLET_ROOT/client_wallet/tls -pwd Oracle123 -trusted_cert -cert $WALLET_ROOT/rootca_wallet/root_wallet.crt

Step 3: Copy wallets to respective destinations

3.1 Copy hrdb_wallet to host of hrdb database server

export ORACLE_BASE="/u01/app/oracle/19c"
export WALLET_ROOT="$ORACLE_BASE/wallet_root"
rsync -Have ssh $WALLET_ROOT/hrdb_wallet/tls/. opc@hrdb:$WALLET_ROOT/tls

3.2 Lookup PDB GUIDs and copy hrdb wallet to each PDB GUID

ssh opc@hrdb
export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="$ORACLE_BASE/dbhome_1"
export WALLET_ROOT="$ORACLE_BASE/wallet_root"
export TNS_ADMIN="$ORACLE_HOME/network/admin"
export ORACLE_SID="hrdb"
PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/bin/sqlplus / as sysdba
SQL> select name,guid from v$containers;
SQL> quit;

for pdbguid in 262D482FB47D7B8BE0638400000A1737
do
mkdir -p $WALLET_ROOT/$pdbguid/tls
cp $WALLET_ROOT/tls/ewallet.p12 $WALLET_ROOT/$pdbguid/tls
cp $WALLET_ROOT/tls/cwallet.sso $WALLET_ROOT/$pdbguid/tls
done

3.3 Copy client_wallet.crt certificate to host of each database thick client

rsync -Have ssh $WALLET_ROOT/rootca_wallet/root_wallet.crt opc@clientdb:/u01/app/oracle/root_wallet.crt

Step 4: Configure 19c database server

4.1 Setup the database server environment variables on the hrdb host.

export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="$ORACLE_BASE/dbhome_1"
export ORACLE_SID="hrdb"
export TNS_ADMIN="$ORACLE_HOME/network/admin"
PATH=$ORACLE_HOME/bin:$PATH

4.2 Configure sqlnet.ora

cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2
SSL_CIPHER_SUITES = (TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384)

4.3 Configure listener

cat $ORACLE_HOME/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE

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

ADR_BASE_LISTENER = /u01/app/oracle/19c

4.4 Set the wallet_root parameter and restart the database and listener

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 start
sqlplus / as sysdba
SQL> startup;
SQL> alter pluggable database all open;
SQL> alter system register;
SQL> quit;

4.5 Configure host firewall of database server to allow inboud (a.k.a. ingress) connections to non-secure port (1521) and secure port (2848)

sudo firewall-cmd --permanent --zone=public --add-port=1521/tcp
sudo firewall-cmd --permanent --zone=public --add-port=2484/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

Step 5: Configure 19c database client

Instructions for downloading 19c database client at https://www.braddiggs.com/2024/11/setup-production-oracle-19c-database.html

5.1 Setup the database client environment variables

export ORACLE_BASE="/u01/app/oracle/23ai"
export ORACLE_HOME="$ORACLE_BASE/client"
PATH=$ORACLE_HOME/bin:$PATH

5.2 Extract the 19c database client

mkdir -p $ORACLE_HOME
cd $ORACLE_HOME
unzip -qo /u01/bits/V982065-01.zip

5.3 Add database configurations to tnsnames.ora setting the WALLET_LOCATION to SYSTEM

cat $ORACLE_HOME/network/admin/tnsnames.ora
HRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

HRDB_SSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.example.com)(PORT = 2484))
(SECURITY=(WALLET_LOCATION=SYSTEM))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

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

pdb1_ssl =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCPS)(HOST=hrdb.example.com)(PORT=2484))
(SECURITY=(WALLET_LOCATION=SYSTEM))
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=pdb1)
)
)

5.4 Configure sqlnet.ora

cat $ORACLE_HOME/network/admin/sqlnet.ora
WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/tls)
)
)

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2
SSL_CIPHER_SUITES=(TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384)

5.5 Append the root_wallet.crt certificate to the host wallet

cat /u01/app/oracle/root_wallet.crt | sudo tee -a /etc/pki/tls/cert.pem
rm /u01/app/oracle/root_wallet.crt

Step 6: Test 19c client connections to the database

6.1 Setup the database client environment variables

export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="$ORACLE_BASE/dbhome_1"
export LD_LIBRARY_PATH="$ORACLE_HOME/lib"
PATH=$ORACLE_HOME/bin:$PATH

6.2 Test to the container database (CDB) hrdb_ssl

sqlplus system/Oracle123@hrdb_ssl

6.3 Test to the pluggable database (PDB) pdb1_ssl

sqlplus system/Oracle123@pdb1_ssl

Step 7: Configure 23ai database client

Instructions for downloading 23ai database client at https://www.braddiggs.com/2024/11/setup-production-oracle-23ai-database.html

7.1 Setup the database client environment variables

export ORACLE_BASE="/u01/app/oracle/23ai"
export ORACLE_HOME="$ORACLE_BASE/client"
PATH=$ORACLE_HOME/bin:$PATH

7.2 Extract the 23ai database client

mkdir -p $ORACLE_HOME
cd $ORACLE_HOME
unzip -qo /u01/bits/V1044258-01.zip

7.3 Add database configurations to tnsnames.ora setting the WALLET_LOCATION to SYSTEM

7.4 Configure sqlnet.ora

7.5 Append the root_wallet.crt certificate to the host wallet

cat /u01/app/oracle/root_wallet.crt | sudo tee -a /etc/pki/tls/cert.pem
rm /u01/app/oracle/root_wallet.crt

Step 8: Test 23ai client connections to the database

Instructions for downloading 23ai database client at https://www.braddiggs.com/2024/11/setup-production-oracle-23ai-database.html

8.1 Setup the database client environment variables

export ORACLE_BASE="/u01/app/oracle/23ai"
export ORACLE_HOME="$ORACLE_BASE/dbhome_1"
export LD_LIBRARY_PATH="$ORACLE_HOME/lib"
PATH=$ORACLE_HOME/bin:$PATH

8.2 Test to the container database (CDB) hrdb_ssl

sqlplus system/Oracle123@hrdb_ssl

8.3 Test to the pluggable database (PDB) pdb1_ssl

sqlplus system/Oracle123@pdb1_ssl

For troubleshooting, see my blog post on Troubleshooting Oracle One-way TLS Connection Errors.

One-way TLS ODB Client/Server With Self-signed Certificates In Wallets

1. Self-signed private certs with wallets - For demonstrations and proofs of concept

2. Self-signed private certs with host trust store - Next step toward production

3. Publicly signed certs - For production

The focus of this blog post is on the first of the three.

The full list of supported SSL_VERSION and SSL_CIPHER_SUITES values per database version are available here:

19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/oracle-net-listener-parameters-in-listener-ora.html#GUID-1BCF9D1F-76D5-456A-B3DA-EC77C1C379E2

23ai: https://docs.oracle.com/en/database/oracle/oracle-database/23/netrf/oracle-net-listener-parameters-in-listener-ora.html#GUID-1BCF9D1F-76D5-456A-B3DA-EC77C1C379E2

Step 1: Setup Certificate Authority Server

1.1 Setup the environment variables on the dbca host.

export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="$ORACLE_BASE/dbhome_1"
export WALLET_ROOT="$ORACLE_BASE/wallet_root"
PATH=$ORACLE_HOME/bin:$PATH

1.2 Create the root certificate authority wallet with a arbitrary distinguished name that makes it clear that it is the certificate authority. For example, DN=root_ca.

Step 2: Generate and sign DB and client certificates

Step 3: Copy wallets to respective destinations

3.1 Copy hrdb_wallet to host of hrdb database server

export ORACLE_BASE="/u01/app/oracle/19c"
export WALLET_ROOT="$ORACLE_BASE/wallet_root"
rsync -Have ssh $WALLET_ROOT/hrdb_wallet/tls/. opc@hrdb:$WALLET_ROOT/tls

3.2 Lookup PDB GUIDs and copy hrdb wallet to each PDB GUID

3.3 Copy client_wallet to host of each database thick client

export ORACLE_BASE="/u01/app/oracle/19c"
export WALLET_ROOT="$ORACLE_BASE/wallet_root"
rsync -Have ssh $WALLET_ROOT/client_wallet/tls/. opc@clientdb:/u01/app/oracle/tls

Step 4: Configure 19c database server

4.1 Setup the database server environment variables on the hrdb host.

export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="/u01/app/oracle/19c/dbhome_1"
export ORACLE_SID="hrdb"
export TNS_ADMIN="/u01/app/oracle/19c/dbhome_1/network/admin"
PATH=$ORACLE_HOME/bin:$PATH

4.2 Configure sqlnet.ora

4.3 Configure listener

4.4 Set the wallet_root parameter and restart the database and listener

4.5 Configure host firewall of database server to allow inboud (a.k.a. ingress) connections to non-secure port (1521) and secure port (2848)

sudo firewall-cmd --permanent --zone=public --add-port=1521/tcp
sudo firewall-cmd --permanent --zone=public --add-port=2484/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

Step 5: Configure 19c database client

Instructions for downloading 19c database client at https://www.braddiggs.com/2024/11/setup-production-oracle-19c-database.html

5.1 Setup the database client environment variables

export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="$ORACLE_BASE/client"
PATH=$ORACLE_HOME/bin:$PATH

5.2 Extract the 19c database client

mkdir -p $ORACLE_HOME
cd $ORACLE_HOME
unzip -qo /u01/bits/V982065-01.zip

5.3 Add database configurations to tnsnames.ora setting the WALLET_LOCATION to the path of the client wallet (/u01/app/oracle/tls)

cat $ORACLE_HOME/network/admin/tnsnames.ora
HRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

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

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

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

5.4 Configure sqlnet.ora

Step 6: Test 19c client connections to the database

6.1 Setup the database client environment variables

export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="$ORACLE_BASE/client"
export LD_LIBRARY_PATH="$ORACLE_HOME/lib"
PATH=$ORACLE_HOME/bin:$PATH

6.2 Test to the container database (CDB) hrdb_ssl

sqlplus system/Oracle123@hrdb_ssl

6.3 Test to the pluggable database (PDB) pdb1_ssl

sqlplus system/Oracle123@pdb1_ssl

Step 7: Configure 23ai database client

Instructions for downloading 23ai database client at https://www.braddiggs.com/2024/11/setup-production-oracle-23ai-database.html

7.1 Setup the database client environment variables

export ORACLE_BASE="/u01/app/oracle/23ai"
export ORACLE_HOME="$ORACLE_BASE/client"
PATH=$ORACLE_HOME/bin:$PATH

7.2 Extract the 23ai database client

mkdir -p $ORACLE_HOME
cd $ORACLE_HOME
unzip -qo /u01/bits/V1044258-01.zip

7.3 Add database configurations to tnsnames.ora setting the WALLET_LOCATION to the path of the client wallet (/u01/app/oracle/tls)

cat $ORACLE_HOME/network/admin/tnsnames.ora
HRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

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

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

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

7.4 Configure sqlnet.ora

Step 8: Test 23ai client connections to the database

Instructions for downloading 23ai database client at https://www.braddiggs.com/2024/11/setup-production-oracle-23ai-database.html

8.1 Setup the database client environment variables

export ORACLE_BASE="/u01/app/oracle/23ai"
export ORACLE_HOME="$ORACLE_BASE/client"
export LD_LIBRARY_PATH="$ORACLE_HOME/lib"
PATH=$ORACLE_HOME/bin:$PATH

8.2 Test to the container database (CDB) hrdb_ssl

sqlplus system/Oracle123@hrdb_ssl

8.3 Test to the pluggable database (PDB) pdb1_ssl

sqlplus system/Oracle123@pdb1_ssl

For troubleshooting, see my blog post on Troubleshooting Oracle One-way TLS Connection Errors.

Setup Production Oracle 19c Database Client

I've done quite a bit of work with the Oracle database clients lately and decided to write up the workflow that I use for my reference. I hope it is helpful to you as well.

Step 1: Go to eDelivery at https://edelivery.oracle.com/

Step 2: Login with your Oracle credentials

Step 3: Search on: "REL: Oracle Database Client 19.3.0.0.0" and click on Continue

Step 4: Select desired platform and click on Continue

Step 5: Read license terms, check agree checkbox to terms if you agree, and click continue

Step 6: Click on the V982065-01(V982065-01.zip) gold image to download

Step 7: Copy the image to target host

Step 8: Set the environment for the desired ORACLE_HOME

export ORACLE_BASE="/u01/app/oracle/19c"
export ORACLE_HOME="$ORACLE_BASE/client"

Step 9: Extract the software

mkdir -p $ORACLE_HOME
cd $ORACLE_HOME
unzip -qo /u01/bits/V982065-01.zip

Step 10: Configure the tnsnames.ora for the data databases that you want to connect to from this database client. For example:

cat $ORACLE_HOME/network/admin/tnsnames.ora
HRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

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

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

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

Setup Production Oracle 23ai Database Client

I've done quite a bit of work with the Oracle database clients lately and decided to write up the workflow that I use for my reference. I hope it is helpful to you as well.

Step 1: Go to eDelivery at https://edelivery.oracle.com/

Step 2: Login with your Oracle credentials

Step 3: Search on: "REL: Oracle Database Client 23.5.0.0.0" and click on Continue

Step 4: Select desired platform and click on Continue

Step 5: Read license terms, check agree checkbox to terms if you agree, and click continue

Step 6: Click on the V1044258-01(V1044258-01.zip) gold image to download

Step 7: Copy the image to target host

Step 8: Set the environment for the desired ORACLE_HOME

export ORACLE_BASE="/u01/app/oracle/23ai"
export ORACLE_HOME="$ORACLE_BASE/client"

Step 9: Extract the software

mkdir -p $ORACLE_HOME
cd $ORACLE_HOME
unzip -qo /u01/bits/V1044258-01.zip

Step 10: Configure the tnsnames.ora for the data databases that you want to connect to from this database client. For example:

cat $ORACLE_HOME/network/admin/tnsnames.ora
HRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

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

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

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

Troubleshooting Oracle One-way TLS Connection Errors

While working through several iterations of one-way Transport Layer Security (TLS) connection testing between Oracle Database client and server, I captured the troubleshooting techniques and possible reasons and remediation suggestions for the errors that I encountered. This blog post catalogs those errors for my reference.

Error: ORA-28759: failure to open file

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28759

Possible Reasons:

1. The client wallet exist does not exist. Copy client wallet from dbca to local client wallet directory (/u01/app/oracle/tls). See section 2.2.

2, The WALLET_LOCATION path in tnsnames.ora does not match the actual client wallet location. For example, following two paths should match:

Actual client wallet location: /u01/app/oracle/tls

The tnsnames.ora definition for HRDB_SSL:

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

Error: ORA-12154: Cannot connect to database. Cannot find alias %s in %s.

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-12154

Possible Reasons: Either service is not specified in tnsnames.ora or the wrong service name was used in sqlplus command.

Error: ORA-12518: TNS:listener could not hand off client connection

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-12518

Possible Reasons: The WALLET_ROOT specified in the database configuration may have incorrect path. For example, the following path has a typo in the path specified:

alter system set wallet_root='/u01/app/oops_typo_here/wallet_root' scope=spfile;

Here is the correct path that resolves ORA-12518 error:

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

Note in trace log files that error ORA-12518 or TNS-12518 is often followed by ORA-12560 or TNS-12560 for the same reason. Here is sample error:

TNS-12560: Database communication protocol error

Error: ORA-12547: TNS:lost contact

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-12547

Possible Reasons: The PROTOCOL of tnsnames.ora definition is TCP instead of TCPS for SSL/TLS connection or vice versa for non-SSL/TLS connection. For example, the PROTOCOL of the following tnsnames.ora should be TCPS rather than TCP:

HRDB_SSL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 2484))
(SECURITY=(WALLET_LOCATION=/u01/app/oracle/tls))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hrdb)
)
)

Error: ORA-17954: The configured DN, "CN=db-scan.myco.com", does not match the DN "CN=hrdb12-scan.myco.com", of the server's certificate.

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-17954

Possible Reasons: The certificate requested by the client by HOST parameter does not match the certificate's subject CN value or any of the certificate's Subject Alternative Name (SAN) entries.

Error: ORA-28862: SSL connection failed

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28862

Possible Reasons:

1. If your database implementation employs SHARED_SERVERS configuration with a specified number of dispatchers, you need to make sure that the dispatchers configuration includes TCPS connection in addition to the TCP connection.

ALTER SYSTEM SET dispatchers='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.example.com))(ADDRESS=(PROTOCOL=TCPS)(HOST=xx.example.com)))(DISPATCHERS=8)';

Error: ORA-28864: SSL connection closed gracefully

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28864

Possible Reasons:

1. The PROTOCOL definition of the database server's listener.ora is set to TCP rather than TCPS. For example, changing the blue TCP to TCPS and restarting the Oracle database listener should resolve this error.

cat $ORACLE_HOME/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 2484))
(SECURITY=(WALLET_LOCATION=/u01/app/oracle/19c/wallet_root/tls))
)
)

ADR_BASE_LISTENER = /u01/app/oracle/19c

Error: Segmentation fault (core dumped)

Possible Reasons:

1. The TCPS address has the wrong port. Change to the correct port (2484).

cat $ORACLE_HOME/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.example.com)(PORT = 9999))
(SECURITY=(WALLET_LOCATION=/u01/app/oracle/19c/wallet_root/tls))
)
)

ADR_BASE_LISTENER = /u01/app/oracle/19c

2. The TCPS address has the wrong address. Change to the correct port.

cat $ORACLE_HOME/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE

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

ADR_BASE_LISTENER = /u01/app/oracle/19c

Error: ORA-28860: Fatal SSL error

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28860

Possible Reasons:

1. SSL_CLIENT_AUTHENTICATION setting in the database client or server sqlnet.ora may be set to TRUE instead of FALSE in the Oracle database server listener.ora. Wireshark/tshark analysis of the secure port (2484) while sqlplus connect is run. From the following output, we see the failed connection:

sudo yum install -y wireshark

sudo tshark -i any -d "tcp.port==2484,ssl" -V -a duration:20 2> /dev/null | egrep "Cipher Suites \(|Cipher Suite:|^ Version: |SSL Record Layer: Handshake Protocol: Client Hello|Handshake Protocol: Server Hello|Record Layer"| uniq | sed -e "s/(0x.*)//g" -e "s/.*SSL Record Layer: Handshake Protocol: Client Hello/Client requested:/g" -e "s/.*Version:/ Protocol Version:/g" -e "s/.*Cipher Suites / Cipher Suites Requested:/g" -e "s/.*Handshake Protocol: Server Hello/Server replied with:/g" |egrep -v "Server replied with: Done" &
[1] 91106

sqlplus system/Oracle123@hrdb_ssl

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 6 10:54:52 2024
Version 19.25.0.0.0

Copyright (c) 1982, 2024, Oracle. All rights reserved.

ERROR:
ORA-28860: Fatal SSL error

Enter user-name: ^C
TLSv1.2 Record Layer: Handshake Protocol: Client Hello
Protocol Version: TLS 1.2
Cipher Suites Requested:(25 suites)
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA
Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA
Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256
Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA
Cipher Suite: TLS_RSA_WITH_AES_256_GCM_SHA384
Cipher Suite: TLS_RSA_WITH_AES_256_CBC_SHA256
Cipher Suite: TLS_RSA_WITH_AES_256_CBC_SHA
Cipher Suite: TLS_RSA_WITH_AES_128_GCM_SHA256
Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA256
Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA
Cipher Suite: TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
Cipher Suite: TLS_DHE_RSA_WITH_AES_128_GCM_SHA256
Cipher Suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA256
Cipher Suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256
Cipher Suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA
Cipher Suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA
Cipher Suite: TLS_EMPTY_RENEGOTIATION_INFO_SCSV
Server replied with:
Server replied with:
Protocol Version: TLS 1.2
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
TLSv1.2 Record Layer: Handshake Protocol: Certificate
TLSv1.2 Record Layer: Handshake Protocol: Server Key Exchange
TLSv1.2 Record Layer: Handshake Protocol: Multiple Handshake Messages
TLSv1.2 Record Layer: Handshake Protocol: Certificate
TLSv1.2 Record Layer: Handshake Protocol: Client Key Exchange
TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
TLSv1.2 Record Layer: Alert (Level: Fatal, Description: Handshake Failure)
TLSv1 Record Layer: Handshake Protocol: Client Hello
Protocol Version: TLS 1.2
Cipher Suites Requested:(6 suites)
Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
Cipher Suite: TLS_RSA_WITH_AES_128_GCM_SHA256
Cipher Suite: TLS_RSA_WITH_AES_256_GCM_SHA384
TLSv1.2 Record Layer: Handshake Protocol: Multiple Handshake Messages
Server replied with:
Protocol Version: TLS 1.2
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
TLSv1.2 Record Layer: Handshake Protocol: Client Key Exchange
TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
TLSv1.2 Record Layer: Application Data Protocol: http-over-tls

2. The cryptographic cipher suite does not match in the $ORACLE_HOME/network/admin/sqlnet.ora of the database client and server. For example, the SSL_CIPHER_SUITES in the following slqnet.ora on both client and server should iniclude TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 but one does not:

The wireshark/tshark analysis of this connection reveals that the client and server could not agree on the terms of the cryptographic handshake.

sudo tshark -i any -d "tcp.port==2484,ssl" -V -a duration:5 2> /dev/null | egrep "Cipher Suites \(|Cipher Suite:|^ Version: |SSL Record Layer: Handshake Protocol: Client Hello|Handshake Protocol: Server Hello|Record Layer"| uniq | sed -e "s/(0x.*)//g" -e "s/.*SSL Record Layer: Handshake Protocol: Client Hello/Client requested:/g" -e "s/.*Version:/ Protocol Version:/g" -e "s/.*Cipher Suites / Cipher Suites Requested:/g" -e "s/.*Handshake Protocol: Server Hello/Server replied with:/g" |egrep -v "Server replied with: Done" &
[1] 92079

sqlplus system/Oracle123@hrdb_ssl

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 6 11:16:21 2024
Version 19.25.0.0.0

Copyright (c) 1982, 2024, Oracle. All rights reserved.

ERROR:
ORA-28860: Fatal SSL error

Enter user-name: ^C
TLSv1.2 Record Layer: Handshake Protocol: Client Hello
Protocol Version: TLS 1.2
Cipher Suites Requested:(2 suites)
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
Cipher Suite: TLS_EMPTY_RENEGOTIATION_INFO_SCSV
Server replied with:
Server replied with:
Protocol Version: TLS 1.2
Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
TLSv1.2 Record Layer: Handshake Protocol: Certificate
TLSv1.2 Record Layer: Handshake Protocol: Server Key Exchange
TLSv1.2 Record Layer: Handshake Protocol: Client Key Exchange
TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
TLSv1.2 Record Layer: Application Data Protocol: Application Data
TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
TLSv1.2 Record Layer: Alert (Level: Fatal, Description: Handshake Failure)

3. The SSL_VERSION setting in the sqlnet.ora of the database client and server do not match. For example, if SSL_VERSION is set to 1 rather than 1.2. You would get very similar wireshark/tshark result as the previous example.

4. The TLS wallet exists but does not contain a valid server cert. The wallet may be present and may include a trusted cert but does not include user (e.g. server) cert.

Error: ORA-12560: Database communication protocol error.

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-12560

Possible Reasons:

1. The SSL_VERSION setting in the sqlnet.ora of the database client and server do not match. For example, if SSL_VERSION of the client sqlnet.ora is set to 1.3 when the server sqlnet.ora is set to 1.2.

2. The length of the TLS certificate common name (CN) value exceeds 64 characters. This is a constraint imposed by OpenSSL conformance with RFC 5280.

Error: ORA-28759: failure to open file

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28759

Possible Reasons: The WALLET_LOCATION of the connect string or tns entry or the sqlnet.ora does not match the actual location of the wallet.

Error: ORA-28845: No certificate

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28865

Possible Reasons:

1. The database server wallet specified by WALLET_ROOT in sqlnet.ora may not have auto_login enabled. Use orapki to enable auto_login. The database server trace file should include something like the following error:

nztwRetrievePersonaCopy: failed because of no certs in wallet: 28845

For example for CDB:

cd $WALLET_ROOT/tls
orapki wallet create -wallet . -auto_login

For example for specific PDB GUID:

cd $$WALLET_ROOT/26471EC7099D0530E0635D00000A3BC3/tls
orapki wallet create -wallet . -auto_login

2. If you see this error when connecting securely to a PDB, this means that the wallet has not yet been copied to PDB wallet_root/tls directory. For example:

mkdir -p $WALLET_ROOT/26471EC7099D0530E0635D00000A3BC3/tls
cp $WALLET_ROOT/tls/ewallet.p12 $WALLET_ROOT/26471EC7099D0530E0635D00000A3BC3/tls
cp $WALLET_ROOT/tls/cwallet.sso $WALLET_ROOT/26471EC7099D0530E0635D00000A3BC3/tls

Additional information on Oracle database support of TLS is available at Oracle Support Document 2980134.1:

FAQ: TCPS / TLS / SSL / UTL_HTTP / UTL_SMTP Configuration and Queries in Oracle Database

https://support.oracle.com/epmos/faces/DocumentDisplay?id=2980134.1

Error: ORA-28865: SSL connection has closed

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28865

Possible Reasons: Same reasons for reason ORA-28845.

Error: ORA-29002: SSL transport detected invalid or obsolete server certificate.

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-29002

Possible Reasons:

1. The client connect string either does not specify SSL_SERVER_CERT_DN it is set but not set to subject CN value of the database server certificate.

2. The length of the TLS certificate common name (CN) value exceeds 64 characters. This is a constraint imposed by OpenSSL conformance with RFC 5280.

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

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-29003

Possible Reasons:

1. HOST value of client connect string does not match the certificate subject or Subject Alternative Name (SAN) values of the certificate.

2. 23ai client needs to be version 23.26 (a.k.a. 26ai) or newer

3. The length of the TLS certificate common name (CN) value exceeds 64 characters. This is a constraint imposed by OpenSSL conformance with RFC 5280.

Error: ORA-29024: Certificate validation failure

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-29024

Possible Reasons:

1. The certificate of the database listener does not match the certificate specified by SSL_SERVER_CERT_DN of the client connect string or tns entry. For example:

Presume that the SSL_SERVER_CERT_DN value is scanvip.dbauthdemo.com. However, the certificate subject is reported to be hrdb.dbauthdemo.com as seen by openssl s_client:

$ 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:

2. The certificate signing authority does not match certificate chain of the client wallet.

Monday, November 4, 2024

Workaround For VMWare Fusion Network Stack Seizure

About two weeks ago after a big Microsoft 11 update, VMWare Fusion 13 on MacOS Ventura (13.7) network stack started randomly seizing. From within the Windows 11 VM guest OS, you could not ping or otherwise connect to any outside network addresses or hosts. Initially, the only way to resolve was to reboot the Windows 11 VM guest OS. Whenever this issue occurred, thousands of the following error were added to the vmware.log file:

vmx VMXNET3 hosted: Cannot retrieve the buffer descriptors per rx packet.

After researching extensively, I learned that the issue could be remediated by restarting the VM network stack rather than rebooting the Windows 11 VM guest OS.

I wrote the following script to periodically check the status of the VM network stack. If any of the network stack services changed from "running" to "not running", I restarted the stack. This seems to be working for now as a workaround until VMWare resolves the issue.

#!/bin/bash
# Restart the network stack if for some reason that it stops
while true
do
cknet=$(sudo /Applications/VMware\ Fusion.app/Contents/Library/vmnet-cli --status 2>&1 |egrep -v "Some|disabled"|grep "not running")
if [ -n "${cknet}" ]
then
sudo /Applications/VMware\ Fusion.app/Contents/Library/vmnet-cli --stop
sudo /Applications/VMware\ Fusion.app/Contents/Library/vmnet-cli --start
fi
sleep 120
done

The runtime user of this script will need to add the following for un-prompted use of sudo where you replace <user> with your runtime username:

<user> ALL = (ALL) NOPASSWD: /Applications/VMware\ Fusion.app/Contents/Library/vmnet-cli

Hope this helps others if they encounter the same.

Blessings

Brad