com.microsoft.sqlserver.jdbc.SQLServerException: MSI Token failure: Failed to acquire token from MSI Endpoint

Question

I want to Access the Azure SQL Database using App service API(Java) with MSI (Managed Service Identity) authentication.

I am trying to find out the how to connect Azure sql with MSI from Azure App service for Java.

Here is the connection string I am using.

jdbc:sqlserver://mysqldb.database.windows.net:1433;database=TestDB;Authentication=ActiveDirectoryMsi;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Here is the steps I used:

1. Create AAD group
2. Add Azure web app'S MI(Managed Identity) to this AAD group
3. Add this group as Active Directory admin to Azure SQL Server

4. Create user and give roles for this group.

   CREATE USER [myAADgroup] FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER [myAADgroup];
   ALTER ROLE db_datawriter ADD MEMBER [myAADgroup];
   ALTER ROLE db_ddladmin ADD MEMBER [myAADgroup];
   

5. Connection string for JDBC driver.

Answer 1

I tested locally and got a success. Here are my steps for your reference:

1. Enable the managed identity for your web app, or function app, or VM

Here, I will use function app.

and then set the status to on and save. And you will get an object ID.

2. Create an Azure AD group, and add the identity as a member

3. Configure the Azure SQL Server on portal

4. Connect to database

Here, I deploy my app to a function app. The sample:

public class Function {

    @FunctionName("HttpTrigger-Java")
    public HttpResponseMessage run(@HttpTrigger(name = "req", methods = {
            HttpMethod.GET }, authLevel = AuthorizationLevel.ANONYMOUS) HttpRequestMessage<Optional<String>> request,
            final ExecutionContext context) {

        String result = "";

        SQLServerDataSource ds = new SQLServerDataSource();
        ds.setServerName("jacksqldemo.database.windows.net"); // Replace with your server name
        ds.setDatabaseName("sqldemo"); // Replace with your database name
        ds.setAuthentication("ActiveDirectoryMSI");

        try (Connection connection = ds.getConnection(); 
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT SUSER_SNAME()")) {
            if (rs.next()) {
                String s = rs.getString(1);
                context.getLogger().info("You have successfully logged on as: " + s);
                result += "You have successfully logged on as: " + s;
            }
        }catch(Exception e){
            context.getLogger().log(Level.WARNING, e.getMessage(),e);
        }
        return request.createResponseBuilder(HttpStatus.OK).body(result).build();
    }
}

Finally, I can connect to Azure SQL:

Answer 2

I was working with Microsoft teams and they confirm that the JDBC library(mssql-jdbc) is the issue and they are working on this fix. I have got a change to test their preview JDBC library and it is working as expected. So the next release of the JDBC library will resolve it.