1 Configure Secure Transport Layer Security for SPMS and Oracle Database Connection

Reference Documents

Refer to the official published document shown below for detailed information on Oracle Advanced Security, where it describes in detail how to configure and use the Secure Sockets Layer (SSL) and Transport Layer Security (TLS) protocols.

For Oracle 12c database: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/configuring-secure-sockets-layer-authentication.html#GUID-6AD89576-526F-4D6B-A539-ADF4B840819F

Difference between Secure Sockets Layer and Transport Layer Security

Transport Layer Security (TLS) is an incremental version of Secure Sockets Layer (SSL) version 3.0. Although SSL was primarily developed by Netscape Communications Corporation, the Internet Engineering Task Force (IETF) took over development of it, and renamed it Transport Layer Security (TLS).

Recommended TLS Version for SPMS

TLS 1.2 and above is the recommended protocol for SPMS.

Prerequisites

  1. The Oracle database server should be Oracle Database Enterprise Edition 12c Release.

  2. On the application machine, Oracle Database 12c 32bit ODAC and 12c Client are required.

  3. The Oracle public key infrastructure (PKI), which provides Oracle Wallet and Oracle Wallet Manager (OWM), is required. The OraclePKI command is used to create keys to generate certificates. The OraclePKI command can be found in $ORACLE_HOME/bin folder.

Using TLS for SPMS Clients and Oracle Database Connection

Transport Layer Security (TLS) can be used in a multi tenant environment for SPMS applications. If you want to use Transport Layer Security (TLS) in a multi-tenant environment for an SPMS application, then you must ensure that database is able to use its own wallet with its own certificates for TLS authentication.

TLS works with the core Oracle Database features such as encryption and data access controls. By using Oracle Database SSL functionality to secure communications between clients and servers, you can:

  • use TLS to encrypt the connection between clients and servers, and

  • authenticate any client or server, to any Oracle database server that is configured to communicate over TLS

Enabling TLS 1.2 for SPMS Clients and Oracle Database Connection

You must configure TLS 1.2 on the Oracle database server, and then the SPMS clients.

  1. Configure TLS 1.2 on the Oracle Database Server.

    • During installation, Oracle sets defaults on the Oracle database server and the Oracle client for SSL parameters, except the Oracle wallet location.

  2. Configure TLS 1.2 on the SPMS clients.

    • When you configure SSL on the client, you configure the server DNs and use TCP/IP with SSL on the client.

  3. Log in to the Database Instance.

    • After you have completed the configuration, you are ready to log in to the database.

Step 1: Configure Oracle Wallet for Server (Database) Side

  1. Open a command prompt window as a normal user.

  2. Create a directory on the server machine to store the server wallet at <SERVER_WALLET>. Run the make directory command below at “C:/Oracle” folder.

    >mkdir wallets
>cd wallets
>mkdir db
>cd db

    Based on the sample above, the value for <SERVER_WALLET> is “C:\Oracle\wallets\db”.

  3. Create a wallet for the Oracle server. Create an empty wallet with auto login enabled:

    > orapki wallet create -wallet "<SERVER_WALLET>" -pwd <password> -auto_login

    Example: orapki wallet create -wallet "C:\Oracle\wallets\db" -pwd <password> -auto_login

  4. Add a self-signed certificate in the wallet (a new pair of private/public keys is created): > orapki wallet add -wallet "<SERVER_WALLET>" -pwd <password> -dn "CN=<server_machine_name>" -keysize 2048 -self_signed -validity <No. of Days>

    Example: orapki wallet add -wallet "C:\Oracle\wallets\db" -pwd <password> -dn "CN=server1" -keysize 2048 -self_signed -validity 365

  5. Check the contents of the wallet. Notice the self-signed certificate is both a user and trusted certificate. 
    > orapki wallet display -wallet "<SERVER_WALLET>" -pwd <password>
  6. Export the certificate so it can be loaded into the client wallet later. 
    > orapki wallet export -wallet "<SERVER_WALLET>" -pwd <password> -dn "CN=<server_machine_name>" -cert <SERVER_WALLET>\<server-certificate-name>.crt

    Example: orapki wallet export -wallet "C:\Oracle\Wallets\db" -pwd <password> -dn "CN=server1" -cert C:\Oracle\wallets\db\server-cert-db.crt

  7. Check whether the certificate has exported to the above directory.

Step 2: Configure Oracle Wallet for Client (Application) Side

All SPMS client machines must create a client wallet. These steps have to be repeated for each of the database client machines. Follow the steps below to create a client wallet.

  1. Open a command prompt window as a normal user.

  2. Create a directory on the client machine to store the client wallet. Let’s call it <CLIENT_WALLET>. Create it under the “C:\Oracle” folder.

    >mkdir wallets
>cd wallets
>mkdir user
>cd user

    Based on the sample above, the value for <CLIENT_WALLET> is C:\Oracle\wallets\user

  3. Create a wallet for the Oracle client. Create an empty wallet with auto login enabled: > orapki wallet create -wallet "<CLIENT_WALLET>" -pwd <password> -auto_login. Add a self-signed certificate in the wallet (a new pair of private/public keys is created):

    > orapki wallet add -wallet "<CLIENT_WALLET> " -pwd <password> -dn "CN=<client_machine_name>" -keysize 2048 -self_signed -validity <No. of Days>

    Note:

    Ensure each client certificate has a unique name, use the client machine name as the certificate name.

  4. Check the contents of the wallet. Notice the self-signed certificate is both a user and trusted certificate.> orapki wallet display -wallet "<CLIENT_WALLET>" -pwd <password>

  5. Export the certificate, so it can be loaded into the server wallet later.

    > orapki wallet export -wallet "<CLIENT_WALLET>" -pwd <password> -dn "CN=<client_machine_name>" -cert <CLIENT_WALLET>\<client-certificate-name>.crt

    Note:

    Ensure each client certificate has a unique name, use the client machine name as the certificate name.

  6. Check whether the certificate has exported to the above directory.

Step 3: Perform Clients-Server Exchange Certificate Process

These instructions are for the exchange server and client public keys. These steps have to be repeated on each of the database client machines.

  1. Copy <server-certificate-name>.crt from the server machine to the client machine <CLIENT_WALLET> folder.

  2. Copy <client-certificate-name>.crt from the client machine to the server machine <SERVER_WALLET> folder.

  3. Load the server certificate into the client wallet.

    orapki wallet add -wallet "<CLIENT_WALLET>" -pwd <password> -trusted_cert -cert <CLIENT_WALLET>/<server-certificate-name>.crt

  4. Check the contents of the client wallet. Notice the server certificate is now included in the list of trusted certificates.

    orapki wallet display -wallet "<CLIENT_WALLET>" -pwd <password>

  5. Load the client certificate into the server wallet.

    orapki wallet add -wallet "<SERVER_WALLET>" -pwd <password> -trusted_cert -cert <SERVER_WALLET>/<client-certificate-name>.crt

  6. Check the contents of the server wallet. Notice that the client certificate is now included in the list of trusted certificates.

Step 4: Configure the Oracle Database to Listen For TCPS Connection

Configure the listener.ora and sqlnet.ora files on the Database server using the following steps.

To configure the listener.ora file,

  1. Launch the Net Manager Tool.

    Figure 1-1 Net Manager


    This figure shows the Oracle Net Manager screen.

  2. Expand Local, then expand Listeners, and select the Listener folder.

  3. Click on Add Address and select TCP/IP with SSL as the protocol.

  4. Enter the hostname and port as shown in the below screen shot.

    Figure 1-2 Listener, Address Tab


    This figure shows the Listener Address tab.

  5. Click File, and then click Save Network Configuration to save the setting. Below is an example of the listener.ora file

    ...
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = example.com)(PORT = <PORT NO>))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = example.com)(PORT = <PORT NO>))
    )
  )
...

To configure the sqlnet.ora file using Oracle Net Manager:

  1. Click Profile,and then select Network Security from the drop-down list.

  2. Select the SSL tab, and then select the Server option.

  3. Enter the values as shown below:

    Configuration Method: File System
    Wallet Directory:  <SERVER_WALLET>
    Configure SSL for: Server
    Revocation Check: NoneRequire Client Authentication: FALSE

    Figure 1-3 Network Security in Net Manager


    This figure shows the Profile Network Security Settings in Net Manager.

  4. Click File, and then click Save Network Configuration to save. At this point, exit the Oracle Net Manager tool and ensure all changes are saved.

  5. Since the Oracle Net Manager does not allow for certain values to be changed, open

    <ORACLE_HOME>/network/admin/sqlnet.ora and make sure the following properties are set to

    SSL_VERSION = 1.2
SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_128_GCM_SHA256)

  6. In <ORACLE_HOME>/dbs/init.ora make sure the following property is set to

    _use_fips_mode=FALSE

  7. Restart the Database Service and listener so all the above changes to take effect. From Windows Services Administrative Tools, Services, restart the corresponding Database Service. The Listener can be restarted from either Windows services or as shown below:

    Open the command prompt and follow the below steps using Run as Administrator

    > lsnrctl stop

    > lsnrctl start

    After completing the steps, re-open the Net Manager. Below is a sample of the sqlnet.ora and listener.ora file: 
    <ORACLE_HOME>/network/admin/sqlnet.ora
...
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS,NTS)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = C:/Oracle/wallets/db))
   )
SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_128_GCM_SHA256)
...
<ORACLE_HOME>/network/admin/listener.ora
...
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = C:/Oracle/wallets/db))
   )
...

To configure the tnsnames.ora file:

  1. Click on Service Naming in Net Manager.

  2. Click on Edit, and then Create to create a new service. Complete the Net Service Name Wizard as described below:

    Net Service Name: <Service Name>

    Select: “TCP/IP with SSL (Secure Internet Protocol)”

    Host Name: <Host Name>

    Port Number: <Port Number>

    (Oracle8i or later) Service Name: <Service Name>

    Connection Type: Default database Test the connection on page 5 of the wizard

    Figure 1-4 Net Manager Service Name


    This figure shows the Net Manager, Service Name window.

    Here is the sample tnsnames.ora file:

    ...
fidelio_tcps =
   (DESCRIPTION =
     (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCPS)(HOST = example.com)(PORT = <Port No>)))
     (CONNECT_DATA = (SERVICE_NAME = <Service_Name>))
   )
...

  3. Click File, and then clickSave Network Configuration to save.

  4. Click File, and then clickExit. All server configurations have been completed.

Step 5: Configure the Oracle Client to Connect with TCPS Connection

Perform the following configuration on the machine running the SPMS application.

  1. Follow the steps in Step 4 for configuring the client sqlnet.ora file. This file is located in the <ORACLE_HOME>/network/admin folder. File contents are similar to the below example. 
    ...
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS,NTS)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = C:/Oracle/wallets/user))
   )

SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_128_GCM_SHA256)
...
  2. Follow the steps in Step 4 for configuring the tnsnames.ora file on client/application. This file is located in the <ORACLE_HOME>/network/admin folder. Below are the sample file contents: 
    FIDELIO=
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = example.com)(PORT = <Port No>))
  )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = <Service Name>)
  )
)

  3. Connect to the Database using SQL*Plus client with SSL.

  4. Launch the SQL*Plus session from the command line, by typing the username and password as <username>/<password>@ssl_connectstring.

    Note:

    To enable the IIS Server connection to the database, the wallet folder of the IIS server must give permission to IIS_IUSR to access to the wallet. For further details, refer to the Oracle Database Security Guide, section “Configuring Secure Sockets Layer Authentication” located at: https://docs.oracle.com/database/121/DBSEG/asossl.htm#DBSEG9665.