Azure SQL authenticate as user via API with MS Identity Platform

Question

I have an ASP.NET Core 3.1 Web App calling an ASP.NET Core 3.1 Web API, which in turn accesses an Azure SQL database. Authentication is provided via MSAL (Microsoft Identity Platform) - i.e. using the relatively new Microsoft.Identity.Web and Microsoft.Identity.Web.UI libraries.

The goal is to ensure that the user pulls data from SQL via the API under the context of his/her own login, thus enabling row-level security, access auditing and other good things.

I have succeeded in getting the sign-in process to work for the Web App - and through that it obtains a valid access token to access the API using a scope I created when registering the latter with AD.

When I run both the API and the App locally from Visual Studio everything works as expected - the correct access tokens are provided to the App to access the API, and the API to access SQL - in both cases under the user's (i.e. my) identity.

When I publish the API to App Services on Azure, however, and access it there either from a local version of the Web App or an App-Services hosted version of it, the access token that the API gets to access SQL contains the API's Application Identity (system-assigned managed identity), and not the user's identity. Although I can access SQL as the application, it's not what we need.

The Web App obtains its access token using the GetAccessTokenForUserAsync method of ITokenAcquisition - taking as a parameter the single scope I defined for the API.

The API gets its token (to access SQL) like so:

var token = await new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net", _tenantId)

...where _tenantId is the tenant ID of the subscription.

I have added the SQL Azure Database "user_impersonation" API permission to the AD registration for the API - but that has not helped. As an aside, for some reason Azure gives the full name of this permission as https://sql.azuresynapse.usgovcloudapi.net/user_impersonation - which is slightly alarming as this is just a UK-based regular Azure account.

I have found a few similar posts to this, but mostly for older versions of the solution set. I'm hoping to avoid having to write my own code to post the token requests - this is supposed to be handled by the MSAL libraries.

Should I somehow be separately requesting a SQL access scope from the Web App after sign-in, or should the API be doing something different to get hold of a SQL access token that identifies the user? Why does it work perfectly when running locally?

It seems like this should be a very common use case (the most common?) but it is barely documented - most documentation I've found refers only to the application identity being used or doesn't tell you what to do for this particular tech stack.

Answer 1

Finally - success! In the end this was the critical piece of documentation: Microsoft identity platform and OAuth 2.0 On-Behalf-Of flow - the key points being:

1. The App only asks for a token to access the API.
2. The API then requests a token, on behalf of the user identified via the 1st token, to access SQL.

The key is that - since the API cannot trigger a consent window for the second step - I had to use the Enterprise Applications tab in the Azure portal to pre-grant the permissions for SQL.

So the good news is it does work: maybe it's obvious to some but IMO it took me far too long to find the answer to this. I will write up a fuller explanation of how to do this in due course as it can't only be me struggling with this one.

The bad news is that - in the course of my investigations - I found that Azure B2C (which is the next thing I need to add in) doesn't support this "On Behalf Of" flow - click here for details. That's a great shame as I think it's the most obvious use case for it! Oh well, back to the drawing board.

Answer 2

I'm currently working on a similar problem, using a Net5.0 Web app. The reason it appears to be working locally is you are signed into Visual Studio with a user who can access Azure SQL and those are the rights you get in the Db. The IDE is using those credentials in place of the Managed Service Identity, the latter gets used when you upload the app to Azure.

As you noted, in the App registration you need to grant permission to the App for Azure SQL Database user_impersonation.

In your code, you need to request a token from https://database.windows.net//.default (note the // as it's needed for v1 endpoints). By referencing /.default you are asking for all permissions you've selected for the app in the app registration portal.

https://learn.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent#the-default-scope

In Startup.cs you need to EnableTokenAcquisitionToCallDownstreamApi with the scope you require.

        services.AddMicrosoftIdentityWebAppAuthentication(Configuration)
            .EnableTokenAcquisitionToCallDownstreamApi(new[]
               {"https://database.windows.net//.default"})
            // Adds the User and App InMemory Token Cache 
            .AddInMemoryTokenCaches();

        services.AddAuthorization(options =>
        {
            // By default, all incoming requests will be authorized according to the 
            // default policy
            options.FallbackPolicy = options.DefaultPolicy;
        });

        services.AddDbContext<MyDatabaseContext>(options =>
               options.UseSqlServer(
               Configuration.GetConnectionString("MyAzureConnection")));

        // The database interface
        services.AddScoped<ITodos, TodoData>();

        services.AddRazorPages()
            .AddRazorRuntimeCompilation()
            .AddMvcOptions(o => 
            {
                var policy = new AuthorizationPolicyBuilder()
                .RequireAuthenticatedUser()
                .Build();
                o.Filters.Add(new AuthorizeFilter(policy));
            })
            .AddMicrosoftIdentityUI();

You also need to decorate your controllers with [AuthorizeForScopes(Scopes = new string[]{"https://database.windows.net//.default"}] and include the required scopes for that Controller. For Razor, it's at the top of the page model and requires a reference to `using Microsoft.Identity.Web;'

namespace ToDoApp.Pages.Todos
{
    [AuthorizeForScopes(ScopeKeySection = "AzureSQL:BaseUrl")]
    public class CreateModel : PageModel

I'm using a section in my appsettings.json for the scope and retrieving it using ScopeKeySection:

  "AzureSQL": {
    "BaseUrl": "https://database.windows.net//.default",
    "Scopes": "user_impersonation"
  }

This shows you where to include it for MVC, Razor and Blazor:

https://github.com/AzureAD/microsoft-identity-web/wiki/Managing-incremental-consent-and-conditional-access#in-mvc-controllers

Finally, your DbContext needs a token which you could pass to it from the client app (perhaps...).

This is how I am doing it at the moment

    public class MyDatabaseContext : DbContext
    {
        private readonly ITokenAcquisition _tokenAcquisition;

        public MyDatabaseContext (ITokenAcquisition tokenAcquisition,
                            DbContextOptions<MyDatabaseContext> options)
            : base(options)
        {

            _tokenAcquisition = tokenAcquisition;
            string[] scopes = new[]{"https://database.windows.net//.default"};

            var result = _tokenAcquisition.GetAuthenticationResultForUserAsync(scopes)
                            .GetAwaiter()
                            .GetResult();
            token = result.AccessToken;

            var connection = (SqlConnection)Database.GetDbConnection();
            connection.AccessToken = result.token;
        }

This is a flawed solution. If I restart the app and try to access it again I get an error Microsoft.Identity.Web.MicrosoftIdentityWebChallengeUserException: IDW10502: An MsalUiRequiredException was thrown due to a challenge for the user

It seems to be related to the TokenCache. If I sign out and in again or clear my browser cache the error is resolved. I've a workaround that signs the app in on failure, but it's deficient since I'm using the app's credentials.

However, it successfully connects to the Azure SQL Db as the user instead of the App with the user's rights instead. When I do solve the error (or find one) I will update this answer.