Microsoft-authentication-library-for-dotnet: Cannot use access token from MSAL to connect to Azure SQL database

[stweedie]

This is a bug that's not entirely related to MSAL, so if someone can direct me to the proper bug tracker, I would appreciate it.

There are a few issues that I'm seeing when trying to set up access token authentication for an Azure SQL Server database connection.

1. Passing a token with an aud attribute that does NOT have the trailing slash https://database.windows.net/ gives a "Login failed for user", as described here.
2. v2.0 of oauth2 flow at login.microsoftonline.com will not accept https://database.windows.net/ as scope, and the nearest actual working scope of https://database.windows.net/.default causes the aud attribute of the resulting access token to be https://database.windows.net, without the trailing slash.

ADAL (Microsoft.IdentityModel.Clients.ActiveDirectory) allows you to specify authority, as you can see in the code sample.

Which Version of MSAL are you using ?
2.7.0

Platform
dotnet core (2.2)

What authentication flow has the issue?

• Desktop / Mobile
  
  
  
  • [ ] Interactive
  
  
  • [ ] Integrated Windows Auth
  
  
  • [ ] Username Password
  
  
  • [ ] Device code flow (browserless)
  
  
• Web App
  
  
  
  • [ ] Authorization code
  
  
  • [ ] OBO
  
  
• Web API
  
  
  
  • [x] OBO
  
  

Other? - please describe;

Is this a new or existing app?

Repro

using v1 = Microsoft.IdentityModel.Clients.ActiveDirectory;
using v2 = Microsoft.Identity.Client;

namespace AzureADSqlIssues
{
    public class AzureADConnection
    {
        private const string TENANT_ID;
        private const string CLIENT_ID;
        private const string CLIENT_SECRET;
        private const string CONNECTION_STRING;

        public async Task<string> GetAccessTokenV1()
        {
            var creds = new v1.ClientCredential(CLIENT_ID, CLIENT_SECRET);
            var ctx = new v1.AuthenticationContext($"https://login.microsoftonline.com/{TENANT_ID}", false);
            var token = await ctx.AcquireTokenAsync("https://database.windows.net/", creds);
            return token.AccessToken;
        }

        public async Task<string> GetAccessTokenV2()
        {
            var creds = new v2.ClientCredential(CLIENT_SECRET);
            var app = new v2.ConfidentialClientApplication(
                CLIENT_ID,
                $"https://login.microsoftonline.com/{TENANT_ID}",
                "http://localhost",
                creds,
                null,
                null
            );

            var res = await app.AcquireTokenForClientAsync(new List<string>() { "https://database.windows.net/.default" });
            return res.AccessToken;
        }

        public async Task<IEnumerable<string>> GetUsernames()
        {
            using (var connection = new SqlConnection(CONNECTION_STRING))
            {
                connection.AccessToken = await GetAccessTokenV1();
                // below doesn't work
                // connection.AccessToken = await GetAccessTokenV2();

                connection.Open();
                var cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT TOP 10 Name from Users";
                var reader = cmd.ExecuteReader();
                var names = new List<string>();

                while (reader.Read())
                {
                    names.Add(reader["Name"]);
                }

                return names;
            }
        }
    }
}

Expected behavior
Assuming correct configuration, should provide the SQL results.

Actual behavior
Using an access token from v1 works perfectly. Using an access token from v2 gives anonymous logon error.

Possible Solution

Return trailing slash or fix underlying bug in azure SQL server or allow authority to be set when requesting token or accept a different scope

[jmprieur]

@stweedie
can you please try with the following scope: "https://database.windows.net//.default"

See also Warning: Should you have one or two slashes in the scope corresponding to a v1.0 Web API

[stweedie]

Alright, thanks for the quick response @jmprieur.

I figured it wasn't directly an MSAL.net issue, but wasn't sure where I could even go to file a bug report for any of the underlying services.

[jmprieur]

Thanks for the update @stweedie :)