The driver could not establish a secure connection to SQL Server by using SSL

Question

I'm having problems connecting to SQL databases. Whenever I try to connect to a SQL server I get the following error;

Caused by: org.hibernate.exception.JDBCConnectionException: Error calling Driver#connect
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator$1$1.convert(BasicConnectionCreator.java:118)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.convertSqlException(BasicConnectionCreator.java:140)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:58)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:75)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:106)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1887)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1845)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:857)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:850)
    at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.withTccl(ClassLoaderServiceImpl.java:425)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:849)
    at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:75)
    ... 143 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection has been closed. ClientConnectionId:.....".
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1668)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1323)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:55)
    ... 160 more
Caused by: java.io.IOException: SQL Server did not return a response. The connection has been closed. ClientConnectionId:.....
    at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.ensureSSLPayload(IOBuffer.java:651)
    at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.readInternal(IOBuffer.java:708)
    at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.read(IOBuffer.java:700)
    at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.readInternal(IOBuffer.java:895)
    at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.read(IOBuffer.java:883)
    at sun.security.ssl.InputRecord.readFully(InputRecord.java:465)
    at sun.security.ssl.InputRecord.read(InputRecord.java:503)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:973)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1375)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1403)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1387)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1618)
    ... 165 more

This is using com.microsoft.sqlserver.jdbc.SQLServerDriver

Whenever I use the JtDS driver (suggested by eg. this post), I'm still not able to connect to the SQL server and I get the following error.

Caused by: org.hibernate.exception.JDBCConnectionException: Error calling Driver#connect
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator$1$1.convert(BasicConnectionCreator.java:118)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.convertSqlException(BasicConnectionCreator.java:140)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:58)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:75)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:106)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.buildJdbcConnectionAccess(JdbcServicesImpl.java:260)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:94)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:111)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:234)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:206)
    at org.hibernate.cfg.Configuration.buildTypeRegistrations(Configuration.java:1887)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1845)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:857)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:850)
    at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.withTccl(ClassLoaderServiceImpl.java:425)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:849)
    at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:75)
    ... 143 more
Caused by: java.sql.SQLException: I/O Error: DB server closed connection.
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2481)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:632)
    at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:371)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:55)
    ... 160 more
Caused by: java.io.IOException: DB server closed connection.
    at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:852)
    at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:731)
    at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:477)
    at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:114)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2368)
    ... 164 more

The connection string I use: (using jtds)

jdbc:jtds:sqlserver://url.database.windows.net:[PORT];database=name;

OR when not using jtds..

jdbc:sqlserver://url.database.windows.net:[PORT];database=name;

I also have a server that is actually able to run and connect to the database using these configurations. I just keep getting these errors trying to connect to the database locally.

I am running macOS Sierra version 10.21

jtds version:

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.3.1</version>
</dependency>

sqlserver version:

<dependency>
   <groupId>com.microsoft.sqlserver</groupId>
   <artifactId>sqljdbc4</artifactId>
   <version>4.0</version>
</dependency>

EDIT:

I am able to connect to the database using the same configuration AND same laptop on a different network. It is only on my network at home that I get this error

For jTDS, the default is `ssl=off`. Try adding `ssl=request` or `ssl=require` to the end of your jTDS connection URL. (jTDS has a [long-standing issue with SSL connections](https://sourceforge.net/p/jtds/bugs/725/) and neither of those worked for me, but I didn't go downloading patched JAR files or anything like that.) As for Microsoft's JDBC driver, it is not officially supported on Mac OS (as per "System Requirements listed [here](https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774)). — Gord Thompson, Sep 24 '16 at 18:38
Also, since it appears that you are trying to connect to Azure, you should make sure that you are using a current version of the Microsoft JDBC driver. FWIW, I just tried the latest version of the Microsoft driver in "sqljdbc_6.0.7507.100_enu.tar.gz" and it worked just fine over SSL from a Ubuntu 16.04 LTS box. Even though macOS may not be officially supported, a web search uncovered a number of claims that the Microsoft JDBC can (or at least *used to*) work on Macs, e.g., [here](http://razorsql.com/articles/sql_server_mac.html). — Gord Thompson, Sep 24 '16 at 21:45
@GordThompson i was fine a few days ago but this error started to randomly appear.. already tried reinstalling java etc. But no success... i will try a different network soon as I think it might be related to that. Thanks for your reply — Mees Kluivers, Sep 24 '16 at 21:52
Do you have a SQL server installed in a VM on your mac laptop or is it a different database server on your home network? Are you accessing the database across a VPN? Can you give any details on the network? What version of SQL server are you using? What version of java? I use the official microsoft driver with SQL Server 2008,2012, and I have similar errors, but it was probably not the same scenario. Sorry for all the questions. — yxre, Sep 27 '16 at 07:29
@J-Bar java version: 1.8.0_10; SQL Server; Microsoft SQL Azure (RTM) - 12.0.2000.8 — Mees Kluivers, Sep 27 '16 at 07:37
If the only variable is the network, then that is probably where the problem is. I had this same error happen when the windows server went to install updates. — yxre, Sep 27 '16 at 07:44
But how does it even matter what network I am working on? I have no clue where to begin looking :/ — Mees Kluivers, Sep 27 '16 at 14:11

score 4 · Answer 1 · edited Nov 20 '18 at 11:24

4

Please make your string url like below and add sqljdbc42.jar in your project

url = "jdbc:sqlserver://" +serverName + ":1433;DatabaseName=" + dbName +";

encrypt = true;

trustServerCertificate=true;

edited Nov 20 '18 at 11:24

Agilanbu

2,747
2
28
33

answered Nov 20 '18 at 10:05

Sunil Kumar

1,909
2
6
2

score 2 · Answer 2 · answered Jan 24 '17 at 10:14

2

Your problem is similar to https://youtrack.jetbrains.com/issue/IDEA-163324

The solution is:

open terminal
run scutil --getHostName
if hostname is not set or contains .local, run sudo scutil --set HostName "<YOUR HOSTNAME HERE>"

answered Jan 24 '17 at 10:14

wonsky

503
2
7

score 1 · Accepted Answer · answered Oct 03 '16 at 16:49

1

I suspect that when your laptop is on a "different network" that it is then inside the firewall that is also protecting the database servers you are connecting to. Hence, there is no problem making the database connections for this case.

Your "network at home" I presume is outside of the firewall protecting the database servers that you are trying to connect to. Hence, your database connection attempts for this case are being ignored due to the firewall protection.

If these assumptions are correct, you will need to contact the network administrator of the "different network" to gain access through the firewall from your home network.

I hope this helps.

answered Oct 03 '16 at 16:49

JohnH

1,920
4
25
32

I will try this later today and let you know if it worked, thanks! – Mees Kluivers Oct 04 '16 at 08:17
Not actually what fixed my problem, it magicly went away after some time. Though, must be useful for anyone else having this issue. – Mees Kluivers Jan 12 '17 at 22:36
5

Beware, problems that magically disappear without explanation can often reoccur. – JohnH Jan 13 '17 at 14:14

score 1 · Answer 4 · answered May 26 '21 at 12:59

This exception also be present when your SQLJDBC driver jar is old .. for example if your SQL server is latest 2018 or 2019 then if you use old JDBC jar file like sqljdbc4.0 then you will get this exception

Solution for this would be take latest jar file to support 2018 or 2020 version .. i have used below jar file mssql-jdbc-7.2.2.jre8.jar to solve the issue. so look into this way as well

score 0 · Answer 5 · edited Oct 06 '21 at 08:55

0

Use:

java.security.Security.setProperty("jdk.tls.disabledAlgorithms","");

edited Oct 06 '21 at 08:55

Shaido

27,497
23
70
73

answered Oct 05 '21 at 14:19

Hassan Ali Hassan

17
6

score 0 · Answer 6 · answered Jul 06 '23 at 16:04

0

for DataGrip

go to advanced and set trustServerCertificate to true

answered Jul 06 '23 at 16:04

rahulroy9202

2,730
3
32
45

The driver could not establish a secure connection to SQL Server by using SSL

6 Answers6

Linked