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