Problem

A Microsoft JDBC Driver >= 10.x is used to connect JOC Cockpit to an SQL Server database.

The following errors are raised:

PKIX path building failed

unable to find valid certification path to requested target

Analysis

Starting from version 10.x JDBC Driver the Microsoft JDBC Driver by default tries to establish a secure SSL connection to the SQL Server database. 

There are two prerequisites about use of SSL connections:

• The SQL Server database is configured to support encrypted connections and is equipped with an X.509 TLS/SSL certificate.
• JOC Cockpit is in reach of the Root CA and optionally the Intermediate CA certificate that was used when signing the the SQL Server's TLS/SSL certificate.

...

• If encryption is not enforced then users can fallback to use unencrypted connections. This comes at the price of using unsecure connections.
  • This is available from the following query parameter in the JDBC URL: encrypt=false
  • See the previous chapter for locations to apply this query parameter.
• If encryption is enforced then users have to deploy the respective Root CA certificate and optionally Intermediate CA certificate(s) with to a Java truststore.
  • Keep in mind that deploying the certificates to the Windows certificate store will not resolve the problem as this certificate store is not used by Java.
  • Users can add the certificate(s) to the JOC Cockpit's truststore, see JS7 - JOC Cockpit HTTPS Connections.
  • Users can add the certificate(s) to the global Java cacerts truststore.
    • The cacerts truststore is located in the Java installation directory. The precise location depends on use of a JRE/JDK and on the Java version in use. Frequently the lib/security sub-directory of the Java JRE/JDK holds the cacerts truststore file.
    • Import of certificate(s) can be performed with the Java keytool command that is available with the JRE/JDK:

      • Example how to import certificates to the Java truststore:

        Code Block
        keytool -import -trustcacerts -alias <sql-server-alias> -file <certificate.cer> -keystore <cacerts> -storepass "<password>"

        Explanation:

        • <sql-server-alias> specifies the certificate's alias name, frequently the fully qualified domain name (FQDN) of the SQL Server host or database name is used.
        • <certificate.cer> specifies the path to the certificate file. If a certificate chain is used, for example consisting of a Root CA certificate and Intermediate CA certificate, then the keytool command can be executed individually for each certificate.
        • <cacerts> specifies the location of the cacerts Java truststore file.
        • <password> specifies the password required when writing to the cacerts truststore. The default password frequently is changeit. 
        • Argument values can be quoted.
  • Consider that for successful SSL handshake the hostname specified with the certificate - available from the common name (CN) in the certificate's subject property - and the SQL Server's hostname have to match. This suggests to use a fully qualified domain name (FQDN).
    • Users have the option not to verify the hostname specified with the certificate. This undermines security, however, it might be applicably when testing connections. The following query parameter can be used in the JDBC URL: trustServerCertificate=true.
    • See the previous chapter for locations to apply this query parameter.