驱动程序无法使用SSL与SQL Server建立安全连接

Question

驱动程序无法使用SSL与SQL Server建立安全连接

14

我连接SQL数据库的时候遇到了问题。每当我尝试连接到SQL服务器时，我会收到以下错误信息：

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

这里使用的是com.microsoft.sqlserver.jdbc.SQLServerDriver

当我使用JtDS驱动程序（由例如这篇文章建议）时，我仍然无法连接到SQL服务器，我会收到以下错误信息。

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

我使用的连接字符串(使用jtds):

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

或者不使用jtds时:

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

我还有一台服务器，可以使用这些配置运行和连接到数据库。但是在尝试本地连接到数据库时，我一直收到这些错误。

我正在运行macOS Sierra版本10.21

jtds版本：

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

SQL Server版本：

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

编辑：

在另一个网络中，我可以使用相同配置和电脑连接到数据库。只有在我家的网络中才会出现这个错误。

- Mees Kluivers

对于jTDS，默认值为ssl=off。尝试在jTDS连接URL的末尾添加ssl=request或ssl=require。(jTDS存在与SSL连接有关的长期问题，这两个选项都对我无效，但我没有下载修补过的JAR文件或其他类似的东西。)至于微软的JDBC驱动程序，在Mac OS上不受官方支持(根据此处列出的"系统要求")。 - Gord Thompson

明天我会尝试这个。谢谢。 - Mees Kluivers

您在 Mac 笔记本电脑上的 VM 中安装了 SQL Server 还是在家庭网络中使用不同的数据库服务器？您是否通过 VPN 访问数据库？您能否提供有关网络的任何详细信息？您使用哪个版本的 SQL Server？使用哪个版本的 Java？我使用官方微软驱动程序与 SQL Server 2008、2012，我也遇到了类似的错误，但情形可能不太一样。抱歉问题这么多。 - Leland B

@J-Bar Java版本：1.8.0_10；SQL Server；Microsoft SQL Azure（RTM）- 12.0.2000.8 - Mees Kluivers

如果唯一的变量是网络，那么问题很可能就出在那里。当 Windows 服务器进行更新安装时，我曾经遇到过同样的错误。 - Leland B

显示剩余3条评论

6个回答

2

您的问题类似于 https://youtrack.jetbrains.com/issue/IDEA-163324 解决方案如下：

打开终端
运行 scutil --getHostName
如果主机名未设置或包含 .local，请运行sudo scutil --set HostName "<YOUR HOSTNAME HERE>"

- wonsky

1

我怀疑当您的笔记本电脑在“不同的网络”中时，它就在保护您连接的数据库服务器的防火墙内部。因此，在这种情况下，连接数据库没有问题。

我推测您在“家庭网络”上的连接是在保护您尝试连接的数据库服务器之外的防火墙之外。因此，由于防火墙的保护，这种情况下您的数据库连接尝试被忽略了。

如果这些假设是正确的，您需要联系“不同网络”的网络管理员，以便从您的家庭网络通过防火墙获得访问权限。

希望这有所帮助。

- JohnH

我今天稍后会尝试一下，如果成功了我会告诉你的，谢谢！ - Mees Kluivers

并不是这个解决了我的问题，它在一段时间后神奇地消失了。但是，对于其他遇到此问题的人来说可能会有用。 - Mees Kluivers

5

请注意，神奇消失而没有解释的问题往往会再次出现。 - JohnH

1

当您的SQLJDBC驱动程序jar太旧时，也会出现此异常。例如，如果您的SQL服务器是最新的2018或2019版本，那么如果您使用类似于sqljdbc4.0的旧JDBC jar文件，则会遇到此异常。

解决此问题的方法是使用最新的jar文件来支持2018或2020版本。我已经使用了以下jar文件mssql-jdbc-7.2.2.jre8.jar来解决这个问题。所以也可以考虑这种方式。

- ChiranjeeviIT

0

对于DataGrip

进入高级设置，将 trustServerCertificate 设置为 true

- rahulroy9202

0

使用：

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

- Hassan Ali Hassan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Sunil Kumar · Accepted Answer

请将您的字符串url设置如下，并在项目中添加sqljdbc42.jar。

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

encrypt = true;

trustServerCertificate=true;