Problem

The below error is observed while connoting the MSSQL DB from API Gateway/ Analytics.

** Database Error: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:becf451f-b8ed-4c89-80d7-e0ffbbc4b521

Resolution

This error is indicating that there is an issue with establishing an SSL connection between API Gateway / Analytics and the MSSQL server.

So, first check if the MSSQL server is indeed configured to use SSL.

If it is not, then use the following JDBC url in API Gateway / Analytics.

jdbc:sqlserver://<DB_HOST>:<DB_PORT>;DatabaseName=<DB_NAME>;integratedSecurity=false;encrypt=false;

If the MSSQL server is configured to use SSL, and you wish to disable server certificate validation as a workaround to mitigate this error, then use the following JDBC url.

jdbc:sqlserver://<DB_HOST>:<DB_PORT>;DatabaseName=<DB_NAME>;integratedSecurity=false;encrypt=false;trustServerCertificate=true;

Note : Once "trustServerCertificate=true" flag is used, the connection will use SSL or non-SSL based on what is configured in the MSSQL server side, regardless of what value is set for the "encrypt" flag in the JDBC url.

Alternatively, if the MSSQL server is using SSL and you wish to configure the certificates correctly, first create Java keystore and truststore with the necessary cetificates.

Once the keystore and truststore has been created, make sure to give access to API Gateway / Analytics to them by modifying the jvm.xml as follows.

$ cat ./conf/jvm.xml
<ConfigurationFragment>
<VMArg name="-Djavax.net.ssl.trustStore=/etc/certs/truststore.jks"/>
<VMArg name="-Djavax.net.ssl.trustStorePassword=changeme"/>
<VMArg name="-Djavax.net.ssl.keyStore=/etc/certs/keystore.jks"/>
<VMArg name="-Djavax.net.ssl.keyStorePassword=changeme"/>
</ConfigurationFragment>