Configure TLS in Oracle with IT Analytics on Linux in split architecture

In a split architecture, database and IT Analytics Portal are on different systems.

Step 1: Configure Oracle wallet on the server side.

Login as Oracle user.
```
su -aptare
```
Create a directory server_wallet on the server system to store the server wallet.
```
mkdir /opt/aptare/oracle/network/server_wallet
```

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

orapki wallet create -wallet "/opt/aptare/oracle/network/server_wallet" -pwd <password> -auto_login

Add a self-signed certificate in the wallet. A new pair of private/public keys is created at this stage.

orapki wallet add -wallet "/opt/aptare/oracle/network/server_wallet" -pwd <password> -dn "CN=<server_machine_name>" -keysize 2048 -self_signed -validity <# of days>

Check the contents of the wallet. Verity whether the self-signed certificate is a trusted certificate.
```
orapki wallet display -wallet "/opt/aptare/oracle/network/client_wallet" -pwd <password>
```

Export the certificate so that it can be loaded into the client wallet later.

orapki wallet export -wallet "/opt/aptare/oracle/network/server_wallet" -pwd <password> -dn "CN=<server_machine_name>" -cert <SERVER_WALLET>\<server-certificate-name>.crt

Check whether the certificate is exported to the above directory.

Step 2: Configure Oracle wallet for client application

Login as Oracle user.
```
su - aptare
```
Create a directory client_wallet on the client system to store the client wallet.
```
mkdir /opt/aptare/oracle/network/client_wallet
```

Create a wallet for the Oracle client. Create an empty wallet with auto login enabled.

orapki wallet create -wallet "/opt/aptare/oracle/network/client_wallet" -pwd <password> -auto_login

Add a self-signed certificate in the wallet. A new pair of private/public keys are created at this stage.

orapki wallet add -wallet "/opt/aptare/oracle/network/client_wallet" -pwd <password> -dn "CN=<client_machine_name>" -keysize 2048 -self_signed -validity <# of Days>

Check the contents of the wallet. Verify that the self-signed certificate is both a user and a trusted certificate.
```
orapki wallet display -wallet "/opt/aptare/oracle/network/client_wallet" -pwd <password>
```

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

orapki wallet export -wallet "/opt/aptare/oracle/network/client_wallet" -pwd <password> -dn "CN=<client_machine_name>" -cert <CLIENT_WALLET>\<client-certificate-name>.crt

Check whether the certificate is exported to the above directory.
Make sure the Oracle service user can access the wallet file cwallet.sso.

Step 3: Perform client-server exchange certificate process.

Repeat these steps on each of the database client systems:
- Copy <server-certificate-name>.crt from the server system to the client system /opt/aptare/oracle/network/client_wallet folder.
- Copy <client-certificate-name>.crt from the client system to the server system /opt/aptare/oracle/network/server_wallet folder.
- After copying certificate file, change the owner to Oracle service user on the client system.
  On the client system:
```
chown aptare:aptare <server-certificate-name>.crt
```
  On the server system:
```
chown aptare:aptare <client-certificate-name>.crt
```

Load the server certificate into the client wallet.

orapki wallet add -wallet "/opt/aptare/oracle/network/client_wallet" -pwd <password> -trusted_cert -cert /opt/aptare/oracle/network/client_wallet/<server-certificate-name>.crt

Check the contents of the client wallet. Note that the server certificate is now included in the list of trusted certificates.
```
orapki wallet display -wallet "/opt/aptare/oracle/network/client_wallet" -pwd <password>
```

Load the client certificate into the server wallet.

orapki wallet add -wallet "/opt/aptare/oracle/network/server_wallet" -pwd <password> -trusted_cert -cert /opt/aptare/oracle/network/server_wallet/<client-certificate-name>.crt

Check the contents of the server wallet. Note that the client certificate is now included in the list of trusted certificates.
```
orapki wallet display -wallet "/opt/aptare/oracle/network/server_wallet" -pwd <password>
```

Step 4: Configure the Oracle database to listen for TCPS connection (Server/Oracle system). In the steps below, host is Oracle server IP address and /opt/aptare/oracle/network/server_wallet is the server wallet location.

Stop Oracle listener.
```
lsnrctl stop
```

Modify the listener.ora (/opt/aptare/oracle/network/admin/listener.ora).

LISTENER = 
		(DESCRIPTION_LIST = 
     (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) 
      (ADDRESS = (PROTOCOL = TCPS)(HOST = xx.xx.xx.xx)(PORT = 2484))
     )
  )

Append the below line at the end of the file.

SSL_CLIENT_AUTHENTICATION = FALSE 
SECURE_PROTOCOL_LISTENER=(IPC) 
WALLET_LOCATION = 
    (SOURCE = 
      (METHOD = FILE) 
      (METHOD_DATA = 
         (DIRECTORY = /opt/aptare/oracle/network/server_wallet) 
      ) 
    ) 
/opt/aptare/oracle/network/server_wallet

Modify the sqlnet.ora file (/opt/aptare/oracle/network/admin/sqlnet.ora).

SSL_CLIENT_AUTHENTICATION = FALSE 
  WALLET_LOCATION = 
    (SOURCE = 
       (METHOD = FILE) 
       (METHOD_DATA = 
          (DIRECTORY = /opt/aptare/oracle/network/server_wallet) 
       ) 
    )

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA) SQLNET.WALLET_OVERRIDE = TRUE

Modify the tnsnames.ora file (/opt/aptare/oracle/network/admin/tnsnames.ora)

SCDB =       
     (DESCRIPTION = 
        (ADDRESS= 
            (PROTOCOL=TCPS) 
            (HOST=xx.xx.xx.xx) 
            (PORT=2484) 
        ) 
        (CONNECT_DATA=(SERVICE_NAME=scdb)(SID=SCDB)) 
    )

Start the Oracle service.
```
lsnrctl start
```
Check the listener status.
```
lsnrctl status
```

Step 5: Configure the Oracle database to listen for TCPS connection on the client system. Configure the listener.ora and sqlnet.ora files on the database server using the following steps. In the procedure below, host is Oracle server IP address and /opt/aptare/oracle/network/server_wallet is the server wallet location.

Modify the listener.ora (/opt/aptare/oracle/network/admin/listener.ora) and add the below contents.

LISTENER = 
   (DESCRIPTION_LIST = 
      (DESCRIPTION = 
         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) 
         (ADDRESS = (PROTOCOL = TCPS)(HOST = xx.xx.xx.xx)(PORT = 2484)) 
      ) 
   )

Add below line at the end of file:

 SSL_CLIENT_AUTHENTICATION = FALSE 
    SECURE_PROTOCOL_LISTENER=(IPC) 
    WALLET_LOCATION = 
     (SOURCE = 
       (METHOD = FILE) 
       (METHOD_DATA = 
         (DIRECTORY = /opt/aptare/oracle/network/client_wallet) 
     ) 
  )   
/opt/aptare/oracle/network/client_wallet

Modify the sqlnet.ora file (/opt/aptare/oracle/network/admin/sqlnet.ora).

SSL_CLIENT_AUTHENTICATION = FALSE 
    WALLET_LOCATION = 
       (SOURCE = 
         (METHOD = FILE) 
         (METHOD_DATA = 
           (DIRECTORY = /opt/aptare/oracle/network/client_wallet) 
         ) 
       )

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA) SQLNET.WALLET_OVERRIDE = TRUE

Modify the tnsnames.ora (/opt/aptare/oracle/network/admin/tnsnames.ora)

SCDB = 
   (DESCRIPTION = 
       (ADDRESS= 
           (PROTOCOL=TCPS) 
           (HOST=xx.xx.xx.xx) 
           (PORT=2484) 
       ) 
       (CONNECT_DATA=(SERVICE_NAME=scdb)(SID=SCDB)) 
    )

Test Oracle connection using sqlplus.
```
sqlplus username/password@dbService
```

Step 6: Load Oracle server wallet certificate to the portal and upgrader Java KeyStore.

Add server certificate in portal Java.

cd /usr/java/bin 
keytool -import -trustcacerts -alias ora_server_cert -file /opt/aptare/oracle/network/client_wallet/server-cert-db.crt -keystore /usr/java/lib/security/cacerts 
password: changeit

Add server certificate in upgrader Java.

cd /opt/aptare/upgrade/jre/bin 
keytool -import -trustcacerts -alias ora_server_cert -file /opt/aptare/oracle/network/client_wallet/server-cert-db.crt -keystore /opt/aptare/upgrade/jre/lib/security/cacerts 
password: changeit

Step 7: Modify connection URL in the portal and receiver property file.

Stop portal and agent services.

/opt/aptare/bin/tomcat-portal stop
/opt/aptare/bin/tomcat-agent stop

Modify database URL in /opt/aptare/portalconf/portal.properties.

db.url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)
(HOST=xx.xx.xx.xx)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=SCDB)))

Modify database URL in /opt/aptare/datarcvrconf/datrarcvrproperties.xml.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)
(HOST=xx.xx.xx.xx)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=SCDB)))

Start portal and agent services.

/opt/aptare/bin/tomcat-portal start
/opt/aptare/bin/tomcat-agent start

Configure TLS in Oracle with IT Analytics on Linux in split architecture

Feedback

Feedback