11

I install SQL Server on my local computer and embedd it into my Spring Boot application. After starting Tomcat I get the following error:

'PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target'. ClientConnectionId:85411829-6853-4fdb-9373-b4c93e1d5e8f

I know that this error is well documenteted. I followed many guides and read much about it, but all advices I found did not fix my issue.

What I had done:

  • Download a random SSL-certificate from a website and add it to the cacert file in the Java directory (descriped here).

  • Configure Spring Boot for SQL Server (descriped here)

  • Configure SSL Encryption for SQL Server (descriped here)

No one of these advices fixed the error. The only thing I realize is that if I set spring.jpa.hibernate.ddl-auto in my application.properties to none the program shows the error message, but it did not abort running.

The application.properties looks like this:

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=Car
spring.datasource.username=admin
spring.datasource.password=password123
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
server.port=8443
server.ssl.key-alias=selfsigned_localhost_sslserver
server.ssl.key-password=changeit
server.ssl.key-store=classpath:ssl-server.jks
server.ssl.key-store-provider=SUN
server.ssl.key-store-type=JKS

My dependencies:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

In SQL Server I create a database with tables and data in it.

Did someone of you have an further advice how to fix this error?

taathy
  • 155
  • 1
  • 1
  • 9
  • 1
    Why would you download a certificate from a random web site to add to your key store? – AlwaysLearning Aug 09 '22 at 13:13
  • Are you using JDBC Driver 10.2 for SQL Server (or later)? 10.2 introduced a breaking change that applies `Encrypt=true` by default, ref: [JDBC Driver 10.2 for SQL Server Released](https://techcommunity.microsoft.com/t5/sql-server-blog/jdbc-driver-10-2-for-sql-server-released/ba-p/3100754). – AlwaysLearning Aug 09 '22 at 13:13
  • 2
    SQL Server is installed with its own self-signed certificate, that's the one for which you need the public key to add to your key store. You can find this via SQL Server (version) Configuration Manager > SQL Server Network Configuration > right-click Protocols for (your instance name) > Properties > Certificate tab. Click the View button and in the certificate dialog that opens, click the Details tab and the Copy to File... button to export its public key. You can then add that public key to your key store. – AlwaysLearning Aug 09 '22 at 13:14
  • @AlwaysLearning: Think thats the correct answer, because it fixed the issue. I really appreciate your help!! – taathy Aug 09 '22 at 15:29
  • @AlwaysLearning the view button inthe certificate is disabled can u please help ? – Ahmed Hosny Sep 29 '22 at 07:52
  • In the same dialog with the View button is a dropdown list of the available certificates. If it's not displaying a selection then the View button will be disabled. – AlwaysLearning Sep 29 '22 at 09:12
  • @AlwaysLearning answer is correct . I too was facing the same problem and AlwaysLearning suggestion resolved the issue Thank you – mjk6035 Nov 18 '22 at 10:07
  • Does this answer your question? [The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed:](https://stackoverflow.com/questions/71374815/the-driver-could-not-establish-a-secure-connection-to-sql-server-by-using-secure) – Ilya Serbis Jul 10 '23 at 20:23

2 Answers2

37

I faced the same issue with spring boot 2.7.4

and it Seems from the comments you're using driver 10.2.X

it turns out that since 2.7.0 the JDBC Driver 10.2 for SQL Server is used
instead of 9.4.1.jre8 for 2.6.x

So you've 1 of 2 solutions that worked for me:

1. Use the older version of mssql-jdbc driver 

    <properties>    
        <mssql-jdbc.version>9.4.1.jre8</mssql-jdbc.version>
    </properties>

2. Or ask the driver to just trust the whatever the Sql server certiticate is
you can do so by adding this to the connection string:

jdbc:sqlserver://hOSt:pORt;databaseName=dbName;encrypt=true;trustServerCertificate=true
M. Amer
  • 916
  • 10
  • 12
2

I had same problem with existing server and it helps:
Notification that helped: encrypt=true;trustServerCertificate=true
Also remember when you creating entity in java, be sure that you have same one created in database.

# Microsoft SQL Server config

spring.datasource.url=jdbc:sqlserver://serverNameOrAdress:1443;databaseName=****;encrypt=true;trustServerCertificate=true
spring.jpa.database-platform=org.hibernate.dialect.SQLServerDialect
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.username=***
spring.datasource.password=***
Blaze
  • 21
  • 1