Azure SQL authenticate as user via API with MS Identity Platform

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.

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://docs.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.