idea连接sql server报错：驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接

idea连接sql server报错：驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接

---

运行环境：IDEA2020.1；JDK1.8；SQL SERVER2005；

---

原始代码

public class rainstormDivision {
    public static void main(String[] args) {
        String url = "jdbc:sqlserver://localhost:1433;databaseName=dataOfTest;integratedSecurity=false;";
        Connection conn;
        try {
            conn = DriverManager.getConnection(url,"sa","123456");
            Statement stat = conn.createStatement();//创建一个 Statement 对象来将 SQL 语句发送到数据库。
            ResultSet resultSet=stat.executeQuery("SELECT * FROM dbo.ST_STBPRP_B");
            while (resultSet.next()) {
                System.out.println(resultSet.getString("STCD"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

---

问题描述

com.microsoft.sqlserver.jdbc.SQLServerException: 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target”。 ClientConnectionId:f40d8661-b0d3-438c-9077-7475cd35d616

---

com.microsoft.sqlserver.jdbc.SQLServerException: 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target”。 ClientConnectionId:f40d8661-b0d3-438c-9077-7475cd35d616
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3806)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1906)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3329)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2950)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2790)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1663)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1064)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at rainstormDivision.rainstormDivision.main(rainstormDivision.java:18)
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
	at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1949)
	at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302)
	at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296)
	at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1509)
	at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216)
	at sun.security.ssl.Handshaker.processLoop(Handshaker.java:979)
	at sun.security.ssl.Handshaker.process_record(Handshaker.java:914)
	at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1062)
	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:1795)
	... 8 more
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:387)
	at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:292)
	at sun.security.validator.Validator.validate(Validator.java:260)
	at sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:324)
	at sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:229)
	at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:105)
	at com.microsoft.sqlserver.jdbc.HostNameOverrideX509TrustManager.checkServerTrusted(SQLServerTrustManager.java:86)
	at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:922)
	at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1491)
	... 16 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
	at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
	at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:280)
	at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:382)
	... 24 more

---

原因分析：

可能是因为使用的JDBC驱动版本太高，原本使用的是msscl-idbc-11.2.0.jre8.jar，但一直报上述错误，网上搜了很多方法，删除java.security里面的3DES_EDE_CBC字符等，均未解决，然后看到有人说原因可能是高端本的驱动可能不太兼容较低的sql server，尝试换了一个低版本的驱动sqljdbc42.jar后，程序可以正常连接数据库了。

---

"D:\project tools\jdk\jdk1.8\bin\java.exe" "-javaagent:D:\project tools\intellij idea 2020\IntelliJ IDEA 2020.1\lib\idea_rt.jar=57802:D:\project tools\intellij idea 2020\IntelliJ IDEA 2020.1\bin" -Dfile.encoding=UTF-8 -classpath "D:\project tools\jdk\jdk1.8\jre\lib\charsets.jar;D:\project tools\jdk\jdk1.8\jre\lib\deploy.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\access-bridge-64.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\cldrdata.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\dnsns.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\jaccess.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\jfxrt.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\localedata.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\nashorn.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\sunec.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\sunjce_provider.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\sunmscapi.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\sunpkcs11.jar;D:\project tools\jdk\jdk1.8\jre\lib\ext\zipfs.jar;D:\project tools\jdk\jdk1.8\jre\lib\javaws.jar;D:\project tools\jdk\jdk1.8\jre\lib\jce.jar;D:\project tools\jdk\jdk1.8\jre\lib\jfr.jar;D:\project tools\jdk\jdk1.8\jre\lib\jfxswt.jar;D:\project tools\jdk\jdk1.8\jre\lib\jsse.jar;D:\project tools\jdk\jdk1.8\jre\lib\management-agent.jar;D:\project tools\jdk\jdk1.8\jre\lib\plugin.jar;D:\project tools\jdk\jdk1.8\jre\lib\resources.jar;D:\project tools\jdk\jdk1.8\jre\lib\rt.jar;D:\project tools\intellij idea 2020\IntelliJ IDEA 2020.1\workspace\hydrologyHandbook\out\production\hydrologyHandbook;F:\sqljdbc_4.2.8112.200_chs\sqljdbc_4.2\chs\jre8\sqljdbc42.jar" rainstormDivision.rainstormDivision
41107200
41107200
41107200
41107200
41107200
41107200
41107200
........

---

解决方案：更换低版本的JDBC驱动

提示：直接在官网下载低版本的驱动，sql server jdbc驱动官网：
https://docs.microsoft.com/zh-cn/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16
点击进入后一般主页只显示最新的驱动下载，目前是Microsoft JDBC Driver 10.2，需要自己前往历史版本界面下载。

---

---

点击此处进入历史版本，根据你的需求对相应版本进行下载，在idea的project stucture中导入外部jar即可。
（这个作为解决上述错误的一个可能方法，希望能帮到需要的人）