Clarity connectivity with TCPS(SSL) enabled Oracle server

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Steps to enable TCPS(SSL) connectivity from Clarity to Oracle RDBMS server.

Environment

Clarity 15.5,15.5.1, 15.6, 15.7

Resolution

1.  Make sure that Client Authentication is turned off on the listener. If it's turned on, you need to perform additional steps of importing the client certificate into the Server truststore to establish connectivity. The following steps are assuming the SSL Client Authentication is turned off.
2. Import the Oracle server certificates (including the root certs) to a keystore file of format PKCS12. Following is an example to do that.   
       keytool -importcert -file server_cert.cer -keystore oracle_store.p12 -storetype PKCS12 -alias servercrt -storepass password
3. Once you have imported all the certificates to a PKCS12 keystore, copy the keystore to a location on the Clarity server where the user running Clarity has access.
4. Open up NSA and navigate to the "Database" tab.
5. Ensure that "Specify URL" is checked. 
   
6. Add the following Parameter to the URL to make it TCPS(SSL) compatible.
   
   EncryptionMethod=SSL;Truststore=Path_To_Keystore_Created_On_Step2;TruststorePassword=changeit;CryptoProtocolVersion=TLSv1.2
   
   The resultant URL will look similar to the following:
   
   jdbc:clarity:oracle://XXXXXX.lvn.broadcom.net:1521;ServiceName=XXXXX;BatchPerformanceWorkaround=true;
InsensitiveResultSetBufferSize=0;ServerType=dedicated;supportLinks=true;EncryptionMethod=SSL;
Truststore=cacertsp12.p12;TruststorePassword=changeit;CryptoProtocolVersion=TLSv1.2

Notes:

1. Trust store should be the path where you imported the certificate on Step 2 
2. While this ensures that the Clarity connectivity is working as expected, reporting using Jaspersoft doesn't work using the bean connection

Additional Information

Note: The SSL Client Authentication should be turned off on the Listener and SQLNET.ORA. The following parameter turns it off:
SSL_CLIENT_AUTHENTICATION=FALSE