spring-boot-starter-parent 2.7.0 version destroys connection to sql server database

Question

When using 2.5.7 as the starter parent version I can connect to the sql database, but when upgrading to 2.7.0 the following error appears:

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:044dca7c-183e-44cd-7dc1-54b73c13e44a
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3680) ~[mssql-jdbc-10.2.1.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2113) ~[mssql-jdbc-10.2.1.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3204) ~[mssql-jdbc-10.2.1.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2833) ~[mssql-jdbc-10.2.1.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2671) ~[mssql-jdbc-10.2.1.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1640) ~[mssql-jdbc-10.2.1.jre8.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:936) ~[mssql-jdbc-10.2.1.jre8.jar:na]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-4.0.3.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:181) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:68) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:175) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:173) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1460) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1494) ~[hibernate-core-5.6.9.Final.jar:5.6.9.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.20.jar:5.3.20]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.20.jar:5.3.20]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.20.jar:5.3.20]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.20.jar:5.3.20]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:734) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306) ~[spring-boot-2.7.0.jar:2.7.0]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1295) ~[spring-boot-2.7.0.jar:2.7.0]
    at com.comfenalcoantioquia.subsidies.SubsidiesApplication.main(SubsidiesApplication.java:10) ~[classes/:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-2.7.0.jar:2.7.0]
Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:131) ~[na:na]
    at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:369) ~[na:na]
    at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:312) ~[na:na]
    at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:307) ~[na:na]
    at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:654) ~[na:na]
    at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.onCertificate(CertificateMessage.java:473) ~[na:na]
    at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.consume(CertificateMessage.java:369) ~[na:na]
    at java.base/sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:392) ~[na:na]
    at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:478) ~[na:na]
    at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:456) ~[na:na]
    at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:199) ~[na:na]
    at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:172) ~[na:na]
    at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1377) ~[na:na]
    at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1290) ~[na:na]
    at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:411) ~[na:na]
    at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:383) ~[na:na]
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2021) ~[mssql-jdbc-10.2.1.jre8.jar:na]
    ... 56 common frames omitted

I solved it by carefully looking at my other project thatcan succesfully connect to the database and that (spring-boot-starter-parent version) was the only thing that I notice different and surprise it was the "problem", then reading here https://spring.io/blog/2022/05/19/spring-boot-2-7-0-available-now I notice that in one of the highlights it says "Web server SSL configuration using PEM-encoded certs" and I have the feeling it has to do with this issue, so for fixing I feel I am doing like a workaround which is adding this in the url connection at the properties yml:

encrypt=true;trustServerCertificate=true

this solution I got from here: https://stackoverflow.com/a/72376020/12085680

but I was wondering if there is a more proper way to fix this, so I can use 2.7.0 or above and do what is supposed to do? So please share any guide or tutorial for doing the proper solution, like should I generate a certificate? how? where should it be placed? thanks for any guide/help

You upgraded to `mssql-jdbc-10.2.1`. The answer is to read the release notes, [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), and then you'd know that JDBC 10.2 had a **BREAKING CHANGE** that changes the default from `encrypt=no;` to `encrypt=yes;` and that you'd need to do something about that. — AlwaysLearning, Jun 21 '22 at 10:43
@AlwaysLearning oh there is what I did not see a while a go hehe, yeah I already did what they less recommend in there which is change encrypt to false or use trustServerCertificate to true, I need help on how to do this "Install a trusted certificate on your server." which is the recommended way, is there any tutorial you can point me to? — BugsOverflow, Jun 21 '22 at 10:50
Have a read through [Certificate Management (SQL Server Configuration Manager)](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates) as a starting point. Given the difficulty of getting CA-signed certificates for things-that-aren't-web-servers (unless you have your own organizational CA), if you have a limited pool of connecting clients it's usually easier to export the public key portion of the SQL Server's self-signed certificate and install that into the Trusted Certificate stores of the clients. — AlwaysLearning, Jun 21 '22 at 11:01

score 1 · Answer 1 · answered Oct 17 '22 at 17:31

I faced the same issue with spring boot 2.7.4

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
I assume you're using spring-boot-starter-parent, so under the POM file properties section , just add

    <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 (which you already mentioned):

jdbc:sqlserver://hOSt:pORt;databaseName=dbName;encrypt=true;trustServerCertificate=true

Personally I didn't prefer to trust any certificate blindly so went for 1
specially it saves you the hassle of managing a self-signed certificate for your sql server

spring-boot-starter-parent 2.7.0 version destroys connection to sql server database

1 Answers1