SSL Enable the CA PPM JDBC connection to the application database

Products

• Clarity PPM On Premise

Issue/Introduction

How to secure JDBC connection between CA PPM application and database servers (on seperate hosts) with SSL.

We would like to know what parameters need to be defined in the database connection string in the ca ppm property file to enable SSL.

Environment

Clarity all supported version

Resolution

After installing the SSL certificate on SQL Server, we had to use the following attributes in database element of the CA PPM property file.

We added useURL="true" and encryptionmethod=SSL

<database id="Niku" vendor="mssql" serviceName="niku" password="xxxxxx" upgradeStatus="upgradeNotNeeded" schemaName="niku" username="xxxxxxx" host="sqlservere.clarity.com" url="jdbc:sqlserver://example.com:1433;DatabaseName=NNNNN_STAGE;InsensitiveResultSetBufferSize=0;ProgramName=Clarity;encryptionmethod=ssl;" driver="com.ca.clarity.jdbc.sqlserver.SQLServerDriver" instanceName="" serviceId="NNNNN_STAGE" jndiDatabaseId="jdbc/NikuDS" useURL="true"/> 

Restart all the clarity services. 

You can also then run a Wireshark packet trace filtered for the SQL Server DB IP address and port number defined in your connection string and verify that the network connection is indeed SSL encrypted.

Here are the explanation of each of parameters used in the URL. 

• jdbc:sqlserver://: This is the standard prefix for SQL Server JDBC connection URLs.
• [host]: The hostname or IP address of your SQL Server instance.
• [port]: The port number on which SQL Server is listening (default is 1433).
• databaseName=[database]: The name of the database you want to connect to.
• encryptionMethod=SSL: This property explicitly enables SSL encryption for the connection.
• validateServerCertificate=[true/false]: This property determines whether the driver validates the server's SSL certificate.
  • Set to true for production environments to ensure the server's identity is verified. This requires a trust store containing the server's certificate.
  • Set to false for development or testing environments where certificate validation might be bypassed for convenience, but this is not recommended for production due to security risks.

Additional Information

In Addition to the above we need to perform the following steps on Clarity App servers. 

1. Gather Root, Intermediate & server certificates for your SQL server. 
2. Navigate to %JDK_HOME%/jre/lib/security where you can find a file named cacerts.
3. Import Root, Intermediate & server certificates to cacerts file. Following is an example.
   1. keytool -importcert -file Path_to_cert_file -keystore cacerts -alias Give_alias_name -trustcacerts
4. Once the import is complete restart your NSA and Beacon and validate connectivity to your database. The previous connection strings that you defined on the NSA are still needed.
5. If there are multiple APP servers, please copy the cacerts file to other nodes under %JDK_HOME%/jre/lib/security.