代码之家 › 专栏 › 技术社区 › theb

无法使用带有ActiveDirectoryServicePrincipal的JDBC连接到Azure DB

mssql-jdbc azure-sql-database jdbc azure java

theb · 技术社区 · 3 年前

我正在尝试使用身份验证模式通过JDBC连接到Azure DB ActiveDirectoryServicePrincipal .

连接字符串:jdbc:sqlserver://xxx.database.windows.net:1433;数据库=abcd;身份验证=ActiveDirectoryServicePrincipal;

使用的Java组件:

mssql-jdbc-11.1.1。jre8预览版。罐子
msal4j-1.11.3。罐子
oauth2-oidc-sdk-9.22.1。罐子
json-smart-2.4.7。罐子

尝试连接时,尝试失败,出现以下错误:

2022-04-28 15:23:50 INFO  - Connecting to Source Database jdbc:sqlserver://xxx.database.windows.net:1433;Database=abcd;Authentication=ActiveDirectoryServicePrincipal; ...
2022-04-28 15:23:50 INFO  - Connecting to Source JDBC data source jdbc:sqlserver://xxx.database.windows.net:1433;Database=abcd;Authentication=ActiveDirectoryServicePrincipal; ...
2022-04-28 15:23:51 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:51 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:51 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:51 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:51 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:51 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:52 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:52 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:53 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:53 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:54 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:54 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:55 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:55 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:56 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:56 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:57 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:57 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:23:59 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:23:59 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:24:00 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:24:00 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:24:01 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:24:01 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:24:02 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:24:02 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:24:03 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:24:03 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:24:04 DEBUG - SkipCache set to false. Attempting cache lookup
2022-04-28 15:24:04 DEBUG - Cache lookup failed: Token not found in the cache
2022-04-28 15:24:04 ERROR - Error establishing database connection to Source DB (URL: jdbc:sqlserver://xxx.database.windows.net:1433;Database=abcd;Authentication=ActiveDirectoryServicePrincipal;)
com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user yyy in Active Directory (Authentication=ActiveDirectoryServicePrincipal). com/nimbusds/common/contenttype/ContentType
    at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getCorrectedException(SQLServerMSAL4JUtils.java:240)
    at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getSqlFedAuthTokenPrincipal(SQLServerMSAL4JUtils.java:97)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.getFedAuthToken(SQLServerConnection.java:5440)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.onFedAuthInfo(SQLServerConnection.java:5388)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.processFedAuthInfo(SQLServerConnection.java:5275)
    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onFedAuthInfo(tdsparser.java:305)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:128)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:6262)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4880)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:90)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:4818)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7601)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3885)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3331)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2923)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2763)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1657)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1063)
    at com.synabi.dqnconnect2jdbc.util.DriverShim.connect(DriverShim.java:19)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at com.synabi.dqnconnect2jdbc.Importer.run(Importer.java:484)
    at com.synabi.dqnconnect.App.main(App.java:621)
Caused by: java.util.concurrent.ExecutionException: java.lang.RuntimeException: com/nimbusds/common/contenttype/ContentType
    at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getCorrectedException(SQLServerMSAL4JUtils.java:238)
    ... 23 more
Caused by: java.lang.RuntimeException: com/nimbusds/common/contenttype/ContentType
    at com.microsoft.sqlserver.jdbc.SQLServerMSAL4JUtils.getCorrectedException(SQLServerMSAL4JUtils.java:230)
    ... 23 more

但是,当尝试使用身份验证模式连接时 SqlPassword ,它可以正常工作(当然是不同的用户,但拥有相同的权限)。此外,在尝试使用Powershell连接时,服务主体用户的用户名和密码也可以使用。

所以JDBC组件似乎有问题。有人知道怎么解决这个问题吗?

编辑(澄清):我已经阅读了官方文件( https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication )并按照描述的步骤设置连接。

0 回复 | 直到 3 年前

Pratik Lad 3 年前

通过提供 用户名属性中的应用程序/客户端ID 和 密码属性中服务主体身份的秘密 ,您可以通过ActiveDirectoryServicePrincipal连接到Azure SQL数据库/Synapse Analytics。

username属性中的application/client ID和password属性中的service principal标识的secret of a service principal identity,在连接字符串中缺少这两个参数可能会导致错误。

设置要求:

Java 8或以上
Microsoft JDBC Driver 9.2 (or higher) for SQL Server
Microsoft Authentication Library (MSAL) for Java 或 Microsoft Azure Active Directory Authentication Library (ADAL) for Java 以及它们的依赖关系。

使用身份验证模式ActiveDirectoryServicePrincipal通过JDBC与Azure DB建立连接的代码示例。

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));
            }
        }
    }
}

有关更多信息,请参阅此 Document .