使用ActiveDirectoryServicePrincipal连接SQL Server遇SSL认证错误

问题描述 #

尝试通过Azure Active Directory服务主体连接Azure SQL Server时，持续出现PKIX证书路径构建失败的错误。使用相同凭据通过ODBC或SSMS可正常连接，设置trustServerCertificate=true也无法改善问题。

连接字符串 #

String connectionString = "jdbc:sqlserver://<<>>.windows.net;databaseName=<<>>;user=;password=<<>>;encrypt=true;trustServerCertificate=false;loginTimeout=30;authentication=ActiveDirectoryServicePrincipal"

错误日志 #

SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See https://www.slf4j.org/codes.html#noProviders for further details.
Error connection to the database
com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user <> in Active Directory (Authentication=ActiveDirectoryServicePrincipal). javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getCorrectedException(SQLServerMSAL4JUtils.java:277)
    at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getSqlFedAuthTokenPrincipal(SQLServerMSAL4JUtils.java:112)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:5679)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:5618)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:5463)
    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:311)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:131)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:42)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:6490)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:5068)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:5002)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7685)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4048)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3487)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:3077)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2919)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1787)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1229)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
    at database.TestingDriverAndConnection.main(TestingDriverAndConnection.java:16)
Caused by: java.util.concurrent.ExecutionException: java.lang.RuntimeException: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getCorrectedException(SQLServerMSAL4JUtils.java:275)
    ... 20 more
Caused by: java.lang.RuntimeException: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getCorrectedException(SQLServerMSAL4JUtils.java:267)
    ... 20 more

测试连接代码 #

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

public class AADServicePrincipal {
    public static void main(String[] args) throws Exception{
        String principalId = "1846943b-ad04-4808-aa13-4702d908b5c1"; // Replace with your AAD service principal ID.
        String principalSecret = "..."; // Replace with your AAD principal secret.

        SQLServerDataSource ds = new SQLServerDataSource();
        ds.setServerName("aad-managed-demo.database.windows.net"); // Replace with your server name
        ds.setDatabaseName("demo"); // Replace with your database
        ds.setAuthentication("ActiveDirectoryServicePrincipal");
        ds.setUser(principalId); // setAADSecurePrincipalId for JDBC Driver 9.4 and below
        ds.setPassword(principalSecret); // setAADSecurePrincipalSecret for JDBC Driver 9.4 and below 

        try (Connection connection = ds.getConnection();
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT SUSER_SNAME()")) {
            if (rs.next()) {
                System.out.println("You have successfully logged on as: " + rs.getString(1));
            }
        }
    }
}

当前依赖JAR列表 #

accessors-smart-2.4.9.jar
asm-9.5.jar
content-type-2.2.jar
jackson-annotations-2.15.0.jar
jackson-core-2.15.0.jar
jackson-databind-2.15.0.jar
jcip-annotations-1.0.jar
json-smart-2.4.10.jar
lang-tag-1.7.jar
msal4j-1.13.8.jar
mssql-jdbc-12.2.0.jre11.jar
nimbus-jose-jwt-9.31.jar
oauth2-oidc-sdk-10.8.jar
slf4j-api-2.0.7.jar

解决方案 #

经排查，问题并非缺少微软官方证书，而是需要导入公司内部的特定证书。

内容的提问来源于stack exchange，提问作者Nick