Unable to connect to Azure DB using JDBC with ActiveDirectoryServicePrincipal

Question

I'm trying to connect to an Azure DB via JDBC using authentication mode ActiveDirectoryServicePrincipal.

Connection string: jdbc:sqlserver://xxx.database.windows.net:1433;Database=abcd;Authentication=ActiveDirectoryServicePrincipal;

Used Java components:

• mssql-jdbc-11.1.1.jre8-preview.jar
• msal4j-1.11.3.jar
• oauth2-oidc-sdk-9.22.1.jar
• json-smart-2.4.7.jar

When trying to connect, the attempt fails with the following error:

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

However, when trying to connect using authentication mode SqlPassword, it's working without a problem (different user, of course, but with the same rights). Additionally, the username and password of the Service Principal user works as well when trying to connect using Powershell.

So there seems to be a problem with the JDBC components. Does anyone have any idea how to solve this?

Edit (for clarification): I've read the official documentation (https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication) and followed the described steps to set up the connection.

Answer 1

By Providing the application/client ID in the username property and the secret of a service principal identity in the password property, you can connect to an Azure SQL Database/Synapse Analytics through ActiveDirectoryServicePrincipal.

application/client ID in the username property and the secret of a service principal identity in the password property, in your connection string this two parameters are missing may that can cause an error.

Setup Requirements:

• Java 8 or above

• Microsoft JDBC Driver 9.2 (or higher) for SQL Server

• Microsoft Authentication Library (MSAL) for Java or Microsoft Azure Active Directory Authentication Library (ADAL) for Java and their dependencies.

Code example to establish connection to an Azure DB via JDBC using authentication modeActiveDirectoryServicePrincipal.

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

for more information you can refer this Document.