驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。

ゞ浴缸里的玫瑰 2024-03-31 13:06 50阅读 0赞

##  ##

## 其实解决方法很简单，只需要加上一个字段就行了，只要是关于套接字连接错误的都可以这样解决，而且不用修改SQL server管理器的配置 ##

trustServerCertificate=true；

在不同的环境下有不同的加法，具体情况请往下看

## 说明：我的1433端口被占用了，所以使用的是1434端口 ##

## **1.1** ##

**先看报错信息：****ERROR c.a.d.p.DruidDataSource - create connection SQLException, url: jdbc:sqlserver://localhost;DatabaseName=PT\_STORE\_HLW, errorCode 0, state 08S01**

**com.microsoft.sqlserver.jdbc.SQLServerException: 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“SQL Server 未返回响应。连接已关闭。 ClientConnectionId:22dc49b0-221d-4a51-9a84-8d507658df6e”。  
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)  
at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1668)**

下面这一段是在测试idea使用数据源连接数据库时：

08S01\] 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target”。 ClientConnectionId:64509506-0bbe-41b4-87e7-3c9ce5085eed sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.

ERROR c.a.d.p.DruidDataSource - create connection SQLException, url: jdbc:sqlserver://localhost;DatabaseName=PT_STORE_HLW, errorCode 0, state 08S01
    com.microsoft.sqlserver.jdbc.SQLServerException: 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“SQL Server 未返回响应。连接已关闭。 ClientConnectionId:22dc49b0-221d-4a51-9a84-8d507658df6e”。
    	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 com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:156)
    	at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:218)
    	at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:150)
    	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1560)
    	at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1623)
    	at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2468)
    Caused by: java.io.IOException: SQL Server 未返回响应。连接已关闭。 ClientConnectionId:22dc49b0-221d-4a51-9a84-8d507658df6e
    	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:975)
    	at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1367)
    	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1395)
    	at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1379)
    	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1618)
    	... 10 common frames omitted

## **2.1** ##

**jidbc连接：**

String RL="jdbc:sqlserver://localhost:1434;database=数学建模;encrypt=true;trustServerCertificate=true"

完整代码如下：

public static void main(String[] args) {
            //使用jbdc进行驱动;
            //String RL="jdbc:sqlserver://localhost:1434;"+"DatabaseName=Student_info";
            //只需要跳过检查就行并不用那些又是删除又是修改的
            String RL="jdbc:sqlserver://localhost:1434;database=数学建模;encrypt=true;trustServerCertificate=true";
            String sqlStr="select *\r\n"
                    + "from 颜色\r\n"
                  ;
            try {
                //加载驱动包
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                System.out.println("启动成功");
                String userName="eclipse";
                String userPwd="wkz123";
                //创建数据库连接
                Connection dbConn= DriverManager.getConnection(RL,userName,userPwd);
                System.out.println("链接成功");
                //向数据库发送个东西
                Statement stmt=dbConn.createStatement();
    
                ResultSet rs=stmt.executeQuery(sqlStr);//执行数据库查询语句存在于sqlStr中
                while(rs.next())
                {
    //
                    String  str=rs.getString("文物编号")+"   "+rs.getNString("纹饰")+
                            "   "+rs.getString("类型")+"   "+rs.getString("颜色")+
                            "   "+rs.getString("表面风化");
    
                    System.out.println(str);
    
                }
                dbConn.close();
            }catch(Exception e) {
                e.printStackTrace(System.out);
            }
        }

## **2.2** ##

**mybatis连接：**

完整代码：

## **2.3** ##

**spring boot+druid+mybatis plus连接：**

spring.datasource.url=jdbc:sqlserver://localhost:1434;database=server;encrypt=true;trustServerCertificate=true
    
    
    
    spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
    spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
    spring.datasource.url=jdbc:sqlserver://localhost:1434;database=server;encrypt=true;trustServerCertificate=true
    spring.datasource.username=eclipse
    spring.datasource.password=wkz123
    mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
    
    server.port=8088

## **2.4** ##

**在idea中测试链接数据库：**

报错信息：

08S01] 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target”。 ClientConnectionId:64509506-0bbe-41b4-87e7-3c9ce5085eed sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.

解决方法：

把trustServerCertificate改为true，如下图所示：

![56fb7fc4ba5f488db620a0d90b01e279.png][]

[56fb7fc4ba5f488db620a0d90b01e279.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/120b6450ed4543679c74101a7a76a92c.png