mTLS setup using orapki

This post shows the mTLS setup using orapki for the encryption of database connections. Self signed certificates created by orapki and stored in a Oracle Wallet will be used on the db server and on the client machine.

The database server is running Oracle 19.25 on RHEL 8.1. The client machine is running Windows 10.

1. Create the server wallet and a self-signed (root) certificate

Run as the oracle user on the database server.

# Create a wallet
mkdir ~/wallet && cd ~/wallet
orapki wallet create -wallet `pwd` -pwd test123# -auto_login
# Create a self signed certificate (this creates a CA root certificate and creates a user certificate that is signed by it)
orapki wallet add -wallet `pwd` -dn "CN=`hostname`" -keysize 4096 -self_signed -validity 20 -pwd test123#
# Display the contents of the wallet
orapki wallet display -wallet `pwd`

2. Adjust sqlnet.ora and listener.ora on the database server and start the ssl listener

sqlnet.ora:
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/wallet)))
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS)
SSL_CLIENT_AUTHENTICATION=FALSE

listener.ora:
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION=(SOURCE=(METHOD = FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/wallet)))
LISTENER_SSL=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCPS)(HOST=lin2.fritz.box)(PORT=1522))))
SID_LIST_LISTENER_SSL=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orcl.fritz.box)
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME=orcl)))

lsnrctl start listener_ssl

# Adjust the database so that it registers its services with the listener
alter system set local_listener='LISTENER_ORCL,(ADDRESS=(PROTOCOL=TCPS)(HOST=lin2.fritz.box)(PORT=1522))';  (LISTENER_ORCL already existed)
alter system register; (registers the services to the new listener. Can be checked with lsnrctl services listener_ssl)

3. Create a wallet which can be copied and used on a client

Run as the oracle user on the database server:

mkdir ~/clientwallet && cd ~/clientwallet
orapki wallet create -wallet `pwd` -pwd clienttest123# -auto_login
# Export the server certificate and import in the client wallet
orapki wallet export -wallet ~/wallet -dn "CN=`hostname`" -cert server.cer
orapki wallet add -wallet `pwd` -dn "CN=`hostname`" -trusted_cert -cert server.cer -pwd clienttest123#
# Display the contents of the wallet
orapki wallet display -wallet `pwd`

4. Preparation of the sqlnet.ora and tnsnames.ora on the client machine.

cd ~/clientwallet
sqlnet.ora:
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="C:\sw\cwallet")))
SSL_CLIENT_AUTHENTICATION=FALSE
SSL_SERVER_DN_MATCH=ON

tnsnames.ora:
orcl_ssl=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCPS)(HOST=lin2.fritz.box)(PORT=1522)))
    (CONNECT_DATA=
      (SERVICE_NAME=orcl.fritz.box)
      (SERVER=dedicated))
      (SECURITY=
        (SSL_SERVER_CERT_DN="CN=lin2.fritz.box")))

5. Connecting securely to the database server

The client wallet zip file can be created now

cd ~/clientwallet && zip ../cwallet.zip *

Now copy the cwallet.zip file to the client machine.

5.1 Connecting with SQL Developer

Create a SQL Developer connection of type 'Cloud Wallet' and select cwallet.zip. 
Select the tns alias for the connection (orcl_ssl)
test the tcps connection with: select sys_context('USERENV', 'NETWORK_PROTOCOL') from dual; -- (must be tcps)

5.2 Connecting with SQL*Plus

mkdir c:\sw\cwallet && cd c:\sw\cwallet && tar -xf c:\sw\cwallet.zip
set TNS_ADMIN=C:\sw\cwallet
rem test the tcps connection with:
tnsping orcl_ssl
sqlplus sys@orcl_ssl as sysdba  or  sqlplus sys@tcps://lin2.fritz.box:1522/orcl.fritz.box as sysdba
select sys_context('USERENV', 'NETWORK_PROTOCOL') from dual;  -- (must be tcps)