ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

SSL Enable the CA PPM JDBC connection to the application database

book

Article ID: 113547

calendar_today

Updated On:

Products

Clarity PPM SaaS 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

Release: CODFSS99000-15.4.1-PPM SAAS FedRAMP-Sandbox-Small Environment
Component:

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 in the url attribute encryptionmethod=SSL. 

<database id="Niku" vendor="mssql" serviceName="niku" password="xxxxxx" upgradeStatus="upgradeNotNeeded" schemaName="niku" username="xxxxxxx" host="sqlservere.clarity.com" url="jdbc:clarity:sqlserver://sqlserver.clarity.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 the 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.

Additional Information

In Addition to the above we need to perform the following steps on PPM 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. 

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.