CODEWITHSUNDEEP
javasqlazuremssql-jdbcadal4j
Ammanuel g
Ammanuel g

Reputation: 161

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

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.

Upvotes: 3

Views: 7308

Answers (2)

Ammanuel g
Ammanuel g

Reputation: 161

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.

Upvotes: 2

Jack Jia
Jack Jia

Reputation: 5549

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.

enter image description here

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

enter image description here

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

enter image description here

3. Configure the Azure SQL Server on portal

enter image description here

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:

enter image description here

Upvotes: 3

Related Questions