How to resolve "unable to find valid certification path to requested target" connecting to MS-SQL Server via JDBC using mssql-jdbc-10.1.0.jre17-preview.jar

Question

Environment:

Local machine with JDK 17, MS SQL-Server 2019, Windows 10

I have upgraded to JDK 17 and so using "mssql-jdbc-10.1.0.jre17-preview.jar" as my JDBC driver. I am following the instructions detailed in Create Java apps using SQL Server on Windows to help diagnose the problem. The JAVA source I am using is as described on the website, adjusted for my environment

package com.sqlsamples;  
  
import java.sql.Connection;  
import java.sql.DriverManager;  
  
public class App {  
  
    public static void main(String[] args) {  
  
//        String connectionUrl = "jdbc:sqlserver://localhost:1433;"   
//                               + "databaseName=master;"  
//                               + "user=JBarrow;"  
//                               + "password=xxxxxxxxx;"  
//                               + "TrustServerCertificate=True";  
  
        String connectionUrl = "jdbc:sqlserver://localhost:1433;"  
                             + "databaseName=master;"  
                             + "user=JBarrow;"   
                             + "password=xxxxxxxxx;"  
                             + "TrustServerCertificate=false;"  
                             + "trustStore=C:\\Development\\JDK\\jdk-17\\lib\\security\\cacerts;"  
                             + "trustStorePassword=changeit";  
        try {  
            // Load SQL Server JDBC driver and establish connection.  
            System.out.print("Connecting to SQL Server ... ");  
            try (Connection connection = DriverManager.getConnection(connectionUrl)) {  
                System.out.println("Done.");  
            }  
        }   
        catch (Exception e) {  
            System.out.println();  
            e.printStackTrace();  
        }  
    }  
}  

With the pom.xml file containing the SQL JDBC driver

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">  
    <modelVersion>4.0.0</modelVersion>  
    <groupId>com.sqlsamples</groupId>  
    <artifactId>SqlServerSample</artifactId>  
    <packaging>jar</packaging>  
    <version>1.0.0</version>  
    <name>SqlServerSample</name>  
    <url>http://maven.apache.org</url>  
    <dependencies>  
        <dependency>  
            <groupId>junit</groupId>  
            <artifactId>junit</artifactId>  
            <version>3.8.1</version>  
            <scope>test</scope>  
        </dependency>  
        <!-- add the JDBC Driver -->  
        <dependency>  
            <groupId>com.microsoft.sqlserver</groupId>  
            <artifactId>mssql-jdbc</artifactId>  
            <version>10.1.0.jre17-preview</version>  
        </dependency>  
    </dependencies>  
    <properties>  
        <!-- specify which version of Java to build against-->  
        <maven.compiler.source>17</maven.compiler.source>  
        <maven.compiler.target>17</maven.compiler.target>  
    </properties>  
</project>  

If I swap the assignment of connectionUrl to the first version (trustServerCertificate=true), I can connect to the SQL Server as before (I upgraded). However, this is not recommended and so wanted to install the Microsoft certificates into my cacerts file as the preferred solution. The only documentation I could find to do this was detailed in this Microsoft article - Typical error when trying to connect to SQL server from Java program.

I used the link provided within this page PKI Repository, right clicked each of the 2 .crt files and 'Save link as', Chrome gave a warning that can't be downloaded securely, selected 'Keep' to override. Both could be viewed as per the image below (although did first give me a warning that from "Unknown Publisher")

First, I listed the existing certificates within the cacerts file that shipped with JDK 17 (using "keytool -list -cacerts") then, using CMD with Administrator rights

 keytool -importcert -trustcacerts -alias TLS1 -file "C:\Development\3rdParty\Java\mssql-jdbc\Certificates\Microsoft RSA TLS CA 01.crt" -keystore "C:\Development\JDK\jdk-17\lib\security\cacerts"  
   
keytool -importcert -trustcacerts -alias TLS2 -file "C:\Development\3rdParty\Java\mssql-jdbc\Certificates\Microsoft RSA TLS CA 02.crt" -keystore "C:\Development\JDK\jdk-17\lib\security\cacerts"  

Both confirmed that the "Certificate was added to the keystore" although I did get a message "Warning: use -cacerts option to access cacerts keystore" that I couldn't find any information about.

I then created a revised list of certificates, performed a text comparison between the two lists and confirmed that the cacerts file had two more entries (93, was 91) within it which were

tls1, 28 Jan 2022, trustedCertEntry,   
Certificate fingerprint (SHA-256): 04:EE:EA:8E:50:B4:77:5B:3C:24:79:72:62:91:7E:E5:00:02:EC:4C:75:B5:6C:DF:3E:E1:C1:8C:FC:A5:BA:52  
tls2, 28 Jan 2022, trustedCertEntry,   
Certificate fingerprint (SHA-256): 05:E4:00:5D:B0:C3:82:F3:BD:66:B4:77:29:E9:01:15:77:60:1B:F6:F7:B2:87:E9:A5:2C:ED:71:0D:25:83:46  

I did notice that the fingerprints listed here did not match the thumbprints of the two files listed on the PKI Repository site.

The cacerts file is the original one supplied with JDK 17 and confirmed that the date and size had been amended. There are no other versions of JAVA on the laptop and a cacerts file does not exist anywhere else on the laptop.

Just to be sure, I then rebooted the laptop.

I then built the JAVA test file listed above using "mvn package" and then run it using "mvn -q exec:java "-Dexec.mainClass=com.sqlsamples.App""

This is when I get the reported error in the title

Connecting to SQL Server ...  
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". ClientConnectionId:5a54906f-bc98-4a7a-878e-1e158c1b725e  
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3674)  
        at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2114)  
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3198)  
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2828)  
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2666)  
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1635)  
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:936)  
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)  
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:252)  
        at com.sqlsamples.App.main(App.java:29)  
  

In order to check that I had done everything right, I also tested the following

1. Renamed the cacerts file with the SQL certificates installed (only one in the folder) and got "the trustAnchors parameter must be non-empty", confirming that JRE was using the correct file as, according to web searches this is the message that appears in this case.
2. Amended the password and got "Keystore was tampered with, or password was incorrect", again confirming that the correct file was being accessed.

So I have run out of ideas as to how to resolve this issue. I am using the mssql-jdbc-10.1.0.jre17-preview.jar version of the JDBC as that is the one listed to support JDK 17.

Answer 1

Hi @John Barrow ,

There is some mistake when you export and import server certificate. Please follow below MS document to export and import server certificate.

https://learn.microsoft.com/en-us/sql/connect/jdbc/configuring-the-client-for-ssl-encryption?view=sql-server-ver15

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".