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.

Solution

SQL Server databases without encryption support

If the SQL Server database does not support encrypted connections then the following applies:

• Microsoft JDBC Driver version 9 does not encrypt connections by default. Using this version can resolve the problem.
• For Microsoft JDBC Drivers starting from version 10 the default behavior to encrypt connections can be disabled. This is available from the following query parameter in the JDBC URL: encrypt=false

  • Example

    jdbc:sqlserver://dbms.sos:1417;sendStringParametersAsUnicode=false;selectMethod=cursor;encrypt=false;databaseName=jobscheduler200

  • The query parameter can be specified in a Hibernate configuration file from the following locations:
    • During installation
      • from a copy of the file hibernate-integrated-security-mssql.cfg.xml that is available from the hibernate-examples directory of the JOC Cockpit installer tarball/archive.
      • Users can copy the file and adjust the properties
        • <property name="hibernate.connection.username">
        • <property name="hibernate.connection.password">
        • <property name="hibernate.connection.url">
          • This property value should be added the encrypt=false query parameter.
      • When running the JOC Cockpit installer users can specify to use the newly created Hibernate file.
    • For an existing installation
      • in the hibernate.cfg.xml configuration file available in the JETTY_BASE/resources/joc directory.
      • Users can adjust the property
        • <property name="hibernate.connection.url">
      • JOC Cockpit has to be restarted to apply changes.

SQL Server databases with encryption support

SQL Server databases may or may not be configured to enforce encryption.

• 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 how 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) 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:

        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 applicable when testing connections. The following query parameter can be used in the JDBC URL to suppress verification: trustServerCertificate=true.
    • See the previous chapter how to apply this query parameter.