Prerequisites and Environment Setup

To enable SSL access for an Oracle database, you need a functioning server and client environment. In this example:

• Server: CentOS 7.9 running Oracle Database 11.2.0.4.0
• Client: Windows Server 2008 R2 running Oracle Client 11.2.0.3.0

Start by creating wallets and certificates on both sides.

---

Step 1: Configure the Server Wallet and Generate Certificates

1. Create an auto-login wallet directory:

   Log in as the Oracle user and create a wallets directorry.

   su - oracle
   mkdir $ORACLE_BASE/wallets
   

2. Create the wallet with auto_login enabled:

   orapki wallet create -wallet $ORACLE_BASE/wallets -pwd WalletPasswd123 -auto_login
   

3. Generate a self-signed certificate:

   Replace hostname with the actual server hostname. The certificate is valid for 10 years (3650 days).

   orapki wallet add -wallet $ORACLE_BASE/wallets -pwd WalletPasswd123 \
       -dn "CN=$(hostname)" -keysize 2048 -self_signed -validity 3650 -sign_alg sha512
   

4. Display the wallet content to verify the certificate:

   orapki wallet display -wallet $ORACLE_BASE/wallets -pwd WalletPasswd123
   

5. Export the server certificate:

   orapki wallet export -wallet $ORACLE_BASE/wallets -pwd WalletPasswd123 \
       -dn "CN=$(hostname)" -cert $ORACLE_BASE/wallets/$(hostname)-certificate.crt
   

6. Inspect the exported certificate details (optional):

   keytool -printcert -file $ORACLE_BASE/wallets/$(hostname)-certificate.crt
   

---

Step 2: Configure the Client Wallet and Generate Certificates

1. Create a wallets directory on the client machine:

   mkdir D:\app\wallets
   

2. Create an auto-login wallet:

   orapki wallet create -wallet "D:\app\wallets" -pwd WalletPasswd123 -auto_login
   

   (Replace WalletPasswd123 with a secure password.)

3. Generate a self-signed certificate for the client:

   orapki wallet add -wallet "D:\app\wallets" -pwd WalletPasswd123 \
       -dn "CN=client_hostname" -keysize 2048 -self_signed -validity 3650 -sign_alg sha512
   

4. Display and export the client certificate:

   orapki wallet display -wallet "D:\app\wallets" -pwd WalletPasswd123
   orapki wallet export -wallet "D:\app\wallets" -pwd WalletPasswd123 \
       -dn "CN=client_hostname" -cert "D:\app\wallets\client-certificate.crt"
   

---

Step 3: Exchange and Import Certificates

1. Import the server certificate into the client wallet:

   On the client:

   orapki wallet add -wallet "D:\app\wallets" -pwd WalletPasswd123 \
       -trusted_cert -cert "path_to_server_certificate.crt"
   

2. Verify the server certificate is in the client wallet:

   orapki wallet display -wallet "D:\app\wallets" \
       -pwd WalletPasswd123 | findstr /i "server_hostname"
   

3. Import the client certificate into the server wallet:

   On the server:

   orapki wallet add -wallet $ORACLE_BASE/wallets -pwd WalletPasswd123 \
       -trusted_cert -cert /path/to/client-certificate.crt
   

4. Confirm the client certificate appears in the server wallet:

   orapki wallet display -wallet $ORACLE_BASE/wallets -pwd WalletPasswd123
   

---

Step 4: Server-Side Configuration

1. Edit $ORACLE_HOME/network/admin/sqlnet.ora and add:

   WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/wallets)))
   SSL_CLIENT_AUTHENTICATION = TRUE
   SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)
   SQLNET.AUTHENTICATION_SERVICES = (TCPS)
   

2. Edit $ORACLE_HOME/network/admin/listener.ora to include a TCPS endpoint:

   LISTENER =
     (DESCRIPTION_LIST =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = server_hostname)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCPS)(HOST = server_hostname)(PORT = 2484))
       )
     )
   
   WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=$ORACLE_BASE/wallets)))
   SSL_CLIENT_AUTHENTICATION = TRUE
   

3. Reload the listener:

   lsnrctl reload
   

---

Step 5: Client-Side Configuration

1. Edit $ORACLE_HOME/network/admin/sqlnet.ora on the client:

   WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\app\wallets)))
   SSL_CLIENT_AUTHENTICATION = TRUE
   SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)
   SQLNET.AUTHENTICATION_SERVICES = (TCPS)
   

2. Edit $ORACLE_HOME/network/admin/tnsnames.ora to add a TCPS entry:

   ORCLSSL =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCPS)(HOST = server_hostname)(PORT = 2484))
       )
       (CONNECT_DATA =
         (SERVICE_NAME = orcl)
       )
     )
   

---

Step 6: Testing the SSL Connection

From the client, attempt a connection using the SSL service name:

sqlplus user/password@ORCLSSL

If the connection succeeds, SSL is corrcetly configured. You can also verify by checking the listener log for TCPS connections and confirming that the session is encrypted.