Oracle Database Server/Client certificate/TCPS (Oracle 19c)

This tutorial explains the steps to communicate Oracle Server/Client with TCPS certificate.

• Oracle Database Server name: obrdb1
• Oracle Client Server name: obrdb2
• Password: oracle_4U
• Wallet Server Directory: /u01/app/oracle/ssl/wallet
• Wallet Client Directory: /u01/app/oracle/ssl-client/wallet
• Key size: 2048
• Validity: 10 Years
• Port (TCPS): 1443

Steps:

• Step 1: Create Server Wallet and Certificate
• Step 2: Create Client Wallet and Certificate
• Step 3: Oracle Database Exchange Certificates Server/Client for TCPS
• Step 4: Create a Server Network configuration
• Step 5: Create a client Network configuration
• Step 6: Test communication

Step 1: Create Server Wallet and Certificate

Create Server Wallet and Certificate (Oracle 19c)

Step 2: Create Client Wallet and Certificate

Create Client Wallet and Certificate (Oracle 19c)

Step 3: Oracle Database Exchange Certificates Server/Client for TCPS

Note: Both Server/Client should trust each other .

A. Load the client certificate into the server wallet.

$ scp obrdb2-certificate.crt obrdb1:~/

$ orapki wallet add -wallet /u01/app/oracle/ssl/wallet -pwd oracle_4U -trusted_cert -cert ~/obrdb2-certificate.crt
 Oracle PKI Tool Release 19.0.0.0.0 - Production
 Version 19.4.0.0.0
 Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.
 Operation is successfully completed.

B. Load the server certificate into the client wallet.

$ scp obrdb1-certificate.crt obrdb2:~/

$ orapki wallet add -wallet /u01/app/oracle/ssl-client/wallet -pwd oracle_4U -trusted_cert -cert ~/obrdb1-certificate.crt
 Oracle PKI Tool Release 19.0.0.0.0 - Production
 Version 19.4.0.0.0
 Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.
 Operation is successfully completed.

Step 4: Create a Server Network configuration:

A. Add Wallet location in the Listener profile file: sqlnet.ora

$ vi $ORACLE_HOME/network/admin/sqlnet.ora
 WALLET_LOCATION =
    (SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
        (DIRECTORY = /u01/app/oracle/ssl/wallet)
      )
    )
 SQLNET.AUTHENTICATION_SERVICES = (TCPS,BEQ,NTP)
 SSL_CLIENT_AUTHENTICATION = FALSE

Note:

• TCPS: Secure TCPS
• NTP: Windows NT
• BEQ: Linux Local Connection

B. Add Wallet location in the Listener profile file: listener.ora and the Port Number 1443 for TCPS.

 $ vi $ORACLE_HOME/network/admin/listener.ora
 SSL_CLIENT_AUTHENTICATION = FALSE
 WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/ssl/wallet)
     )
   )
 LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = obrdb1)(PORT = 1521))
     )
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = obrdb1)(PORT = 1443))
     )
   ) 

C. Restart the Listener

$ lsnrctl stop
$ lsnrctl start

D. Add a Firewall rules (With root user):

# firewall-cmd --add-port=1443/tcp
 success
# firewall-cmd --add-port=1443/tcp --permanent
 success

Step 5: Create a Client Network configuration:

A. Add Wallet location in the Client profile file: sqlnet.ora

$ vi $ORACLE_HOME/network/admin/sqlnet.ora
 WALLET_LOCATION =
    (SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
        (DIRECTORY = /u01/app/oracle/ssl-client/wallet)
      )
    )
 SQLNET.AUTHENTICATION_SERVICES = (TCPS,BEQ,NTP)
 SSL_CLIENT_AUTHENTICATION = FALSE

B. Add Connection name in tnsnames.ora file: Port Number 1443 for TCPS.

cat $ORACLE_HOME/network/admin/tnsnames.ora
 OBRDB_tcps =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCPS)(HOST = obrdb1)(PORT = 1443))
     )
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = obrdb)
     )
   )

Step 6: Test Communication

$ tnsping obrdb_tcps
 TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-MAY-2023 20:45:42
 Copyright (c) 1997, 2023, Oracle.  All rights reserved.
 Used parameter files:
 /u01/app/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora
 Used TNSNAMES adapter to resolve the alias
 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = obrdb1)(PORT = 1443))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = obrdb)))
 OK (50 msec)

$ sqlplus system/oracle_4U@obrdb_tcps
 SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 29 20:45:55 2023
 Version 19.19.0.0.0
 Copyright (c) 1982, 2022, Oracle.  All rights reserved.
 Last Successful login time: Thu May 11 2023 12:22:03 +02:00
 Connected to:
 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Version 19.19.0.0.0
 SQL>

Enjoy!