The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed:

Question

I'm new to SQL(Microsoft SQL Server Management) and I am trying to connect it with IntelliJ

I am getting the following error: com.microsoft.sqlserver.jdbc.SQLServerException: 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".

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MyJDBC {

public static void main(String[] args) {


    String  connectionURL = "jdbc:sqlserver://localhost:10020;databaseName=mydatabase;user=me;password=random_password";
    try {
        System.out.print("Connecting to the server......");
        try (Connection connection = DriverManager.getConnection(connectionURL))   {
            System.out.println("Connected to the Server.");
        }
    }catch (Exception e){
        System.out.println("I am not connected to the Server");
        e.printStackTrace();
    }
}
}

I have this on my lib LIB

Any help is appreciated it!

Answer 1

Add encrypt=true and trustServerCertificate=true to connection url.

String  connectionURL = "jdbc:sqlserver://localhost:10020;databaseName=mydatabase;user=me;password=random_password;encrypt=true;trustServerCertificate=true";

Microsoft Blog Reference - link
Find below excerpt from it -

This is an issue in Java Certificate Store. As a quick workaround, if you enable TrustServerCertificate=True in the connection string, the connection from JDBC succeeds. When TrustServerCertificate is set to true, the transport layer will use SSL to encrypt the channel and bypass walking the certificate chain to validate trust. If TrustServerCertificate is set to true and encryption is turned on, the encryption level specified on the server will be used even if Encrypt is set to false. The connection will fail otherwise. However, for security considerations, it is not recommended to bypass the certificate validation. Hence, to address the issue, follow the steps below to change the connection string and import the required certificates.

Change the connection string to point to the Java certificate path

String connectionUrl = "jdbc:sqlserver://localhost:1433;" + "databaseName=AdventureWorks;integratedSecurity=true;" + "encrypt=true; trustServerCertificate=false;" + "trustStore= C:\Program Files\Java\jdk-14.0.2\lib\cacert;trustStorePassword=changeit";

Import all the certificates mentioned in this document.

Note: To import above certificates into the keystore cacerts, please use below command and please note you must mention truststore and truststore password in the connection string to successfully connect. Steps to import missing certificates in Java Certificate Store

Download all the certs from here, store them in a location on client host and then use keytool utility to import these certificates into the truststore. Please follow the below steps:

Save all the certificates from the above MS doc. Keytool utility is in the bin folder of your default Java location (C:\Program Files\Java\jdk-14.0.2\bin). You need to use command prompt to navigate to that location. Then you can use the keytool command to import the certificate previously saved. When prompted for password insert the key in the password as “changeit”

Example of commands:

keytool -importcert -trustcacerts -alias TLS1 -file "C:\Users\Documents\Microsoft RSA TLS CA 01.crt" -keystore "C:\Program Files\Java\jdk-14.0.2\lib\security\cacerts"

keytool -importcert -trustcacerts -alias TLS2 -file "C:\Users\Documents\Microsoft RSA TLS CA 02.crt" -keystore "C:\Program Files\Java\jdk-14.0.2\lib\security\cacerts"