asp.net-coreimpersonationazure-ad-msalmicrosoft-identity-platformazure-sql

Azure SQL authenticate as user via API with MS Identity Platform


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.


Solution

  • 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.