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:

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

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];

Connection string for JDBC driver.

Have you added your app service in SQL database using Access Control? Ref: docs.microsoft.com/en-us/azure/active-directory/… [This ref is for VM but same way you can give access for your app service] — Anish K
@Ammanuelg Did you try this tutorial Connecting using ActiveDirectoryMSI authentication mode? — Leon Yue
I tried the code in the link where @LeonYue recommend but I am getting the same issue. — Ammanuel g
Have you find a solution yet? I'm trying the same but getting the same error — Joost Luijben

Jack Jia Jack Jia · Accepted Answer · 2019-09-16T02:48:25

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: