how to setup SSL in oracle

chandra_1986

Hi Experts,

Can you please update how to setup SSL in oracle env.

I have setup in Both windows and Linux env Oracle SSL configuration.

chandra_1986

I Have Created below steps for SSL setup is it correct?

Environment 

Oracle DB Hosted on Linux 

Replication client hosted on windows.  

 

Steps 

 

## - STEP (1) : Create Server Wallet - (Activities to be performed at Linux Database Server ) 

  > Login to the Oracle Server 

   orapki help 

   orapki wallet help 

   

  # Define variables prior to creating new wallet 

   mkdir -p $ORACLE_BASE/wallet 

   export WALLET=$ORACLE_BASE/wallet 

   echo $WALLET 

   

  # Create a new wallet with auto-login (Don't use auto-login-local) 

   orapki wallet create -wallet $WALLET -auto_login -pwd PASSWORDHERE 

   

  # Check what is inside the Wallet 

   ls -lrt $WALLET 

   

  # Create Wallet related Self-Signed certificate valid for 5 years(1825) - 10 years (3650) - (20 years(7300)) 

  orapki wallet add -wallet $WALLET -pwd PASSWORDHERE -dn "CN=`hostname -s`" -keysize 2048 -self_signed -validity 1825 

   

  # Querying the wallets contents, verify the certificate we created is present 

   orapki wallet display -wallet $WALLET -pwd PASSWORDHERE 

   

  # Extract the Self-Signed certificate to a file 

   orapki wallet export -wallet $WALLET -pwd PASSWORDHERE -dn "CN=`hostname -s`" -cert $WALLET/`hostname -s`.cert 

   

  # Check whether certficate file has been created under $WALLET 

   ls -lrt $WALLET 

   cat $WALLET/`hostname -s`.cert 

   

   

## - STEP (2) : Create Client Wallet & Certificate (Activities to be performed on WINDOWS SERVER) 

 

  # Define variables prior to creating new wallet on the client 

   set WALLET=C:\app\client\wallet 

   md %WALLET% 

   dir %WALLET% 

   

  # Open MS-Dos Command Prompt. 

   Click on START > Type cmd (run as Administrator) 

    

  # Create a new wallet with auto-login (Don't use auto-login-local) 

   orapki wallet create -wallet %WALLET% -auto_login -pwd PASSWORDHERE 

   

  # Check what is inside the Wallet 

   DIR %WALLET% 

   

  # Create Wallet related Self-Signed certificate valid for 5 years(1825) - 10 years (3650) - (20 years(7300)) 

   orapki wallet add -wallet %WALLET% -pwd PASSWORDHERE -dn "CN=******" -keysize 2048 -self_signed -validity 1825 

   

  # Querying the wallets contents, verify the certificate we created is present 

   orapki wallet display -wallet %WALLET% -pwd PASSWORDHERE 

   

  # Extract the Self-Signed certificate to a file 

   orapki wallet export -wallet %WALLET% -pwd PASSWORDHERE -dn "CN=*******" -cert %WALLET%\*******.cert 

   

  # Check whether certficate file has been created under $WALLET, verify its contents 

   more %WALLET%\*****.cert 

   

   

## -- STEP (3) : Exchange certificates from vice-versa (Server Cert => Client &&& Client Cert => Server ) 

 

 # (3a) - Get Server Certificate on to the Client (Use either WinSCP/FizeZilla/XFTP tools to get this thing done) 

 SFTP "$WALLET/****.cert" Server Certificate on to WindowsServer "%WALLET%" folder (C:\app\client\wallet) 

  

  # Verify it's contents 

   cd %WALLET% 

   dir ***.cert 

   

  # Import Server Certificate into Client Wallet 

   orapki wallet add -wallet %WALLET% -pwd PASSWORDHERE -trusted_cert -cert %WALLET%\******.cert 

   

  # Display the contents of the wallet 

   orapki wallet display -wallet %WALLET% -pwd PASSWORDHERE 

  

   Note: By now, the above output will result with a new Trusted Server added from Client 

  

 # (3b) - Second, Import Client Certificate into Server Wallet - (Use either WinSCP/FizeZilla/XFTP tools to get this thing done) 

 cd %WALLET% 

 SFTP - Windows Client Cert - To the Oracle Database Server 

 cp -p /tmp/*******.cert $WALLET 

   

  # On DB Server, add Client Cert to the Server 

   orapki wallet add -wallet $WALLET -pwd PASSWORDHERE -trusted_cert -cert $WALLET/******.cert 

   

  # Display the contents of the wallet  

   orapki wallet display -wallet $WALLET -pwd PASSWORDHERE 

  

  Note: By now, the above output will result with a new Trusted Server added from Server itself. 

 

 

## -- STEP (4) : Enable SSL/TLS on Server-Side (Activites to be performed at Linux Database Server ) 

 

  cd $ORACLE_HOME/network/admin 

  ls -lrt sqlnet.ora 

  cp sqlnet.ora sqlnet.ora.bk 

  cp listener.ora listener.ora.bk 

  cp tnsnames.ora tnsnames.ora.bk 

  vi sqlnet.ora 

 

   WALLET_LOCATION = 

    (SOURCE = 

     (METHOD = FILE) 

     (METHOD_DATA = 

      (DIRECTORY = /u01/app/oracle_base/wallet) 

     ) 

    ) 

 

  :wq (Save the Document) 

 

  vi listener.ora (add it to the last) 

 

  # Add TCPS line inside listener.ora 

  LISTENER = 

   (DESCRIPTION_LIST = 

    (DESCRIPTION = 

     (ADDRESS = (PROTOCOL = TCP)(HOST = *****) (PORT = 1521)) 

     (ADDRESS = (PROTOCOL = TCPS)(HOST = *****) (PORT = 2484)) 

    ) 

   ) 

   

  WALLET_LOCATION = 

   (SOURCE = 

    (METHOD = FILE) 

    (METHOD_DATA = 

     (DIRECTORY = /u01/app/oracle_base/wallet) 

    ) 

   ) 

  

  :wq (Save the Document) 

   

  # Stop & Start the listener at the server   

   lsnrctl stop 

   lsnrctl status 

 

  # Verify the ports are in LISTEN state 

   netstat -tlpn | grep 1521 

   netstat -tlpn | grep 2484 

  

 

## -- STEP (5) : Enable SSL/TLS on Client-Side (Windows) 

 

  # Backup all TNS_ADMIN files 

  cd C:\app\client\product\12.2.0\client_1\network\admin 

  copy sqlnet.ora sqlnet.ora.bk 

  copy listener.ora listener.ora.bk 

  copy tnsnames.ora tnsnames.ora.bk 

 

  # Edit Client SQLNET.ora file 

  WALLET_LOCATION = 

   (SOURCE = 

    (METHOD = FILE) 

    (METHOD_DATA = 

     (DIRECTORY = C:\app\client\wallet) 

    ) 

   ) 

    

  NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) 

  SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,NONE) 

  SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED 

  SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256) 

  SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1) 

 

  # Edit Client TNSNAMES.ora file  

   

  << Existing Ones:>> 

  *****= 

  (DESCRIPTION= 

   (ADDRESS=(PROTOCOL=TCP)(HOST=Oracle DB IP)(PORT=1521)) 

   (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=****)) 

  ) 

   

  << New Entry Added:>> 

  ****_SSL= 

  (DESCRIPTION= 

   (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=Oracle DB IP)(PORT=2484))) 

   (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=*****)) 

  )   

   

  Note: Save the file 

   

## -- STEP (6) : Open the newly added TCPS/TLS Port on the Linux Database Server 

 

  # Login to the DB SERVER 

  sudo su - oracle 

   

  # Verify the firewal state whether ACTIVE & RUNNING / STOPPED  

  sudo firewall-cmd --state 

 

  # Verify what are the active zones configured on the Linux host 

  sudo firewall-cmd --get-active-zones 

 

  # List all the ports configured so far 

  sudo firewall-cmd --list-all 

 

  # Add the required TCPS/TLS port '2484' to the public zone. 

  sudo firewall-cmd --zone=public --add-port=2484/tcp --permanent 

 

  # After adding, reload the configuration 

  sudo firewall-cmd --reload 

   

  # By now, you should see the newly added port 

  sudo firewall-cmd --list-ports 

   

  # Bounce the SSHD services to reflect the changes at the Kernel level 

  sudo /bin/systemctl restart sshd.service 

   

  # Ensure to liaise with Azure - Infra team to open the required port "2484" from SOURCE to the DESTINATION. 

  # Once completed, test a simple TELNET 

  telnet <Target DB Server> 2484  

   

## -- STEP (7) : Testing the connectivity from the CLient (Windows Server) 

 

  Start > Command Prompt (Open with NON-Administrative Rights) 

   

  # Test TNS entry 

  tnsping **** -or-  tnsping **** 

   ==> This should resolve !! 

    

  # Test TCPS/TLS entry 

  tnsping ****_SSL -or- tnsping ****_ssl 

   ==> Even this should also resolve !! 

    

  # Try connecting to a sample schema with Non-TLS & TLS 

  sqlplus [email protected]***   ==> SUCCESSFUL 

  sqlplus [email protected]****_ssl ==> SUCCESSFUL 

                                         

## -- End of the Document ----------------------------------------------------##