TCPS Authentication in Oracle Database


This Document describes the detailed steps required for TCPS authentication to the oracle database from the Oracle Database Connector


Create and Configure Server Wallet:

  1. Create a directory under Oracle Home 
mkdir /home/oracle/wallet_ssl/server
  1. Navigate to the created directory
cd /home/oracle/wallet_ssl/server
  1. Create a server wallet with auto login enabled
orapki wallet create -wallet . -auto_login -pwd oracle_123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
ls -la
  1. Create server's self-signed certificate:
cn should be the hostname of the server where database is running
orapki wallet add -wallet . -dn "cn=oraclevm.internal.cloudapp.net" -self_signed -keysize 1024 -sign_alg sha256 -validity 365 -pwd oracle_123
 

Create and Configure Client Wallet:

  1. Create a directory under Oracle Home 
mkdir /home/oracle/wallet_ssl/client
 
  1. Navigate to the created directory
cd /home/oracle/wallet_ssl/client
  1. Create a client wallet with auto login enabled
orapki wallet create -wallet . -auto_login -pwd oracle_123
  1. The next step is to create a request for a user certificate and export the request
Here cn should be your full computer name which you can get it This PC -> Properties
orapki wallet add -wallet . -dn "cn=BDC7-L-5XQTPV2.xyz " -keysize 1024 -sign_alg sha256 -pwd oracle_123
  1. Next step is we have to export the wallet
orapki wallet export -wallet . -dn "cn=BDC7-L-5XQTPV2.dir.svc.accenture.com" -request req.txt -pwd oracle_123

Copy the certificate from the client to server:

  1. Copy the certificate request from the client directory to the server directory
cp req.txt /home/oracle/wallet_ssl/server/
  1. Sign the certificate of the client and also export server's CA certificate
orapki cert create -wallet . -request req.txt -cert sign.txt -validity 1000 -pwd oracle_123
  1. Export server wallet
orapki wallet export -wallet . -dn "cn=oraclevm.internal.cloudapp.net" -cert server.txt

Copy the signed certificate and the server's root certificate to the client's wallet directory and import them into client's wallet:

  1. Copy exported server.txt to client
cp /home/oracle/wallet_ssl/server/sign.txt /home/oracle/wallet_ssl/client/
  1. Add server.txt to client trusted cacerts
orapki wallet add -wallet . -trusted_cert -cert server.txt -pwd oracle_123
  1. Add sign.txt to user_cert
orapki wallet add -wallet . -user_cert -cert sign.txt -pwd oracle_123
  1. Copy sign.txt and server.txt to client directory
cp /home/oracle/wallet_ssl/server/sign.txt /home/oracle/wallet_ssl/server/server.txt /home/oracle/wallet_ssl/client/

Create the user in the database

  1. SQL> create user ssluser identified externally as 'cn=BDC7-L-5XQTPV2.xyz;
  2. User created.
  3. SQL> grant connect to ssluser;
  4. Grant succeeded.
  5. SQL> exit

Configure the server listener.ora file

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevm.internal.cloudapp.net)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCPS)(HOST = oraclevm.internal.cloudapp.net)(PORT = 1530))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
          (ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = test)
    )
  )

WALLET_LOCATION =
      (SOURCE =
        (METHOD = File)
          (METHOD_DATA =
           (DIRECTORY = /home/oracle/wallet_ssl/server)))

Configure the tnsnames.ora in the server

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevm.internal.cloudapp.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

TEST_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = oraclevm.internal.cloudapp.net)(PORT = 1530))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

Configure sqlnet.ora

 
NAMES.DIRECTORY_PATH=(TNSNAMES)
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS)
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
 (SOURCE =
  (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/wallet_ssl/server)
    )
)

Restart the listener

 
lsnrctl stop
lsnrctl start
 

Configuring the database

The database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE.
 
SQL> alter system set remote_os_authent=FALSE scope=spfile;
SQL> alter system set os_authent_prefix='' scope=spfile;
 
The instance will need to be restarted for these changes to take effect.
 
Test the Client and Server Connection
 
C:\Users\Administrator>sqlplus /@test_ssl 

Restart the database

Configuring the client

 
  1. Copy the files using FTP
 
Copy tnsnames.ora, listener.ora and sqlnet.ora and client wallet to the client machine 
 
  1. Import the client wallet into the Microsoft Certificate Store
 
Start mmc.exe -> File > add/remove snap-in… -> select "Certificates" ->  Add > select "My user account" -> Finish > OK
  
Go to: Console Root -> Certificates – Current User -> Personal -> Certificates  Right click -> All Tasks -> Import-> Next -> Browse… ->select the wallet you have just moved (ewallet.p12) -> Open -> Next
Enter Wallet password: oracle_123 -> Check "Mark this as exportable…" and "Include all extended properties" -> "Place all certificates  in the following store: Personal -> Next -> Finish 
 
  1. Configure client side sqlnet.ora
 
NAMES.DIRECTORY_PATH=(TNSNAMES)
SQLNET.AUTHENTICATION_SERVICES=(NTS,TCPS)
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
 (SOURCE =
  (METHOD = MCS)
    (METHOD_DATA =
      (DIRECTORY = C: \Oracle_wallet\Oracle_19c\wallet_ssl\client)
    )
)
 
  1. Configure client side listener.ora
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevm.internal.cloudapp.net)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCPS)(HOST = oraclevm.internal.cloudapp.net)(PORT = 1530))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
          (ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = test)
    )
  )
 

Configuring the connector

 
  1. Download the below required libraries from the below link 
 
https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
 User-added image
  1. Upload all of the jars to your AtomSphere Account Libraries and add it to custom libraries in the platform with type "Connector" -> "Oracle Database"
  2. Add the below mentioned wallet properties in the Oracle Database connector

           oracle.net.tns_admin = <full file path to tnsnames.ora>
           oracle.net.wallet_location = <full file path to the Oracle wallet folder>
           oracle.net.ssl_server_dn_match = false
 

User-added image
For example, on Windows these properties could look like:
oracle.net.tns_admin = C:/Users/Documents/tnsnames.ora
oracle.net.wallet_location = C:/Users/Desktop/Oracle_wallet/wallet_location
oracle.net.ssl_server_dn_match = false
  1. Test the connection