Linux, Oracle, Security, Windows

Setup tcps / ssl encrypted database connections using orapki and wallets

by jb

This post shows how to setup and use tcps / ssl encrypted database connections. Self signed certificates created by orapki and stored in a Oracle Wallet will be used on the database 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 client sqlnet.ora and listener.ora on the database server.

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. Implement the client wallet in the SQL Developer + use the client wallet with SQL*Plus

cd ~/clientwallet && zip ../cwallet.zip *
Copy cwallet.zip to the client machine
Create a SQL Developer connection of type 'Cloud Wallet' and select cwallet.zip. 
Select the tns alias for the connection
test the tcps connection with: select sys_context('USERENV', 'NETWORK_PROTOCOL') from dual;    (must be tcps)

Test the connection with sqlplus from the client machine:
 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)