Login failed for user '' but works in Data Studio

Question

I am trying to use my AD account to connect to the Azure SQL using Java 8, JDBC Driver, and my accessToken.

When I use Data Studio using my AD Account, I can connect successfully to the Azure SQL DB.

But when I use my Java Program then it gives me this error:

Request processing failed; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ''

My code abstract:

SQLServerDataSource ds = new SQLServerDataSource();
        ds.setServerName("NAME.database.windows.net"); 
        ds.setDatabaseName("db-name"); 
        ds.setAccessToken(accessToken);
        ds.setEncrypt(true);
        ds.setTrustServerCertificate(true);
        try (Connection connection = ds.getConnection();
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT SUSER_SNAME()")) {
            if (rs.next()) {
                System.out.println("dbResults => You have successfully logged on as: " + rs.getString(1));
                res = rs.getString(1);
            }
        }

is this helpful:https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver15#connecting-using-access-token? — Leon Yue, Dec 07 '20 at 05:24
@juunas: I am using this https://github.com/Azure-Samples/ms-identity-java-webapp/tree/master/msal-java-webapp-sample — rohit12sh, Dec 07 '20 at 19:23
Samples often get access tokens for MS Graph API, which won't work with SQL DB. Have you tried decoding the access token at https://jwt.ms and seeing what the audience is? It should have something like `aud: https://database.windows.net/` — juunas, Dec 07 '20 at 19:36
@juunas interesting point. The token I get from Java sample gives me this for `aud: 00000003-0000-0000-c000-000000000000`, which doesn't look like `https://database.window.net/`. — rohit12sh, Dec 08 '20 at 01:56
Yeah, it means you are acquiring an MS Graph API token. It will not be accepted by Azure SQL. Now, you need to decide if you want to get the token on behalf of the user or as the app itself. It affects how the token is acquired. Also if you want to get it on behalf of the user, you need to add a delegated permission to Azure SQL in your app registration. — juunas, Dec 08 '20 at 06:21
@juunas I have got this added in my App Registration but still seeing the same issue: is there a different way to request for SQL Server token? I use this scope to acquire the token. SilentParameters parameters = SilentParameters.builder( Collections.singleton("User.Read.All"), result.account()).build(); — rohit12sh, Dec 09 '20 at 16:03
The `"User.Read.All` there is a scope. You need to use `"https://database.windows.net/.default"` instead as the scope :) — juunas, Dec 09 '20 at 16:05
Eureka!! that worked!! @juunas. Where was this in Azure's documentation if you could please point me to? — rohit12sh, Dec 09 '20 at 16:11

score 3 · Accepted Answer · answered Dec 09 '20 at 17:37

3

After discussion in comments, we found out that we needed to change the scope used when getting the access token. "User.Read.All" was specified, which is the short form "https://graph.microsoft.com/User.Read.All". This means a Microsoft Graph API access token is returned, which won't work with Azure SQL DB.

Changing the scope to "https://database.windows.net/.default" resolved the issue. This gets an access token for Azure SQL DB with the static permissions that the app registration has on Azure SQL DB.

Documentation: https://learn.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent

answered Dec 09 '20 at 17:37

juunas

54,244
13
113
149

one question - how do I switching between the scopes and gets appropriate token? how do I fetch token for both SQL Auth and Graph API? – rohit12sh Dec 10 '20 at 02:25
2

You need to do an acquireToken call twice with different parameters. If these are tokens on behalf of user, you can usually use the refresh token to get the second token, so no login is necessary for the second token. – juunas Dec 10 '20 at 06:20

Login failed for user '' but works in Data Studio

1 Answers1

Linked