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
Article ID: 113547
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.
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:
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.
<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.
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.
Feedback
Yes
No
Powered by
