ASP.NET Core App -> ASP.NET Core API -> Azure SQL Pass-through identification using MSAL

%3CLINGO-SUB%20id%3D%22lingo-sub-2058930%22%20slang%3D%22en-US%22%3EASP.NET%20Core%20App%20-%26gt%3B%20ASP.NET%20Core%20API%20-%26gt%3B%20Azure%20SQL%20Pass-through%20identification%20using%20MSAL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2058930%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20ASP.NET%20Core%203.1%20Razor%20Pages%20Web%20App%20calling%20an%20ASP.NET%20Core%203.1%20MVC%20API%2C%20which%20in%20turn%20accesses%20an%20Azure%20SQL%20database%20on%20the%20user's%20behalf.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20configured%20the%20application%20with%20MSAL%20(Microsoft.Identity.Web%20and%20Microsoft.Identity.Web.UI)%20as%20per%20various%20documents%2C%20and%20registered%20both%20the%20App%20and%20the%20API%20in%20AD.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20works%20perfectly%20in%20the%20development%20environment%20-%20when%20I%20start%20both%20applications%20I%20get%20prompted%20to%20sign%20in%20with%20Microsoft%20ID%2C%20and%20the%20App%20receives%20an%20access%20token%20giving%20it%20access%20to%20the%20API.%20The%20API%20then%20uses%20this...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3Evar%20token%20%3D%20await%20new%20AzureServiceTokenProvider().GetAccessTokenAsync(%22https%3A%2F%2Fdatabase.windows.net%22%2C%20_tenantId)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E...to%20obtain%20an%20access%20token%20for%20SQL.%20When%20I%20run%20it%20locally%20this%20token%20identifies%20me%20as%20the%20user%20and%20all%20works%20as%20expected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EHowever%3C%2FEM%3E%2C%20when%20I%20deploy%20the%20API%20to%20an%20Azure%20App%20Service%20and%20then%20try%20the%20same%20thing%2C%20the%20access%20token%20it%20receives%20to%20access%20SQL%20contains%20an%20application%20identity%2C%20not%20a%20user%20identity.%20That's%20no%20good%20because%20I%20need%20to%20be%20able%20to%20use%20row-level%20security%20and%20auditing%20at%20the%20database%20level.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20has%20been%20driving%20me%20slightly%20crazy%20-%20can%20anyone%20point%20me%20at%20any%20document%20or%20tutorial%20that%20%3CEM%3Euses%20this%20exact%20tech%20stack%3C%2FEM%3E%20(not%20the%20older%20AAD%20version)%20to%20achieve%20that%20(and%20which%20works%20in%20an%20Azure%20App%20Service)%3F%20Since%20it%20all%20works%20perfectly%20in%20the%20dev%20environment%20I'm%20starting%20to%20suspect%20this%20is%20either%20a%20bug%20or%20an%20out-of-date%20feature%20in%20App%20Services.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2058930%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Active%20Directory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIdentity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I have an ASP.NET Core 3.1 Razor Pages Web App calling an ASP.NET Core 3.1 MVC API, which in turn accesses an Azure SQL database on the user's behalf.

 

I have configured the application with MSAL (Microsoft.Identity.Web and Microsoft.Identity.Web.UI) as per various documents, and registered both the App and the API in AD. 

 

All works perfectly in the development environment - when I start both applications I get prompted to sign in with Microsoft ID, and the App receives an access token giving it access to the API. The API then uses this...

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

...to obtain an access token for SQL. When I run it locally this token identifies me as the user and all works as expected.

 

However, when I deploy the API to an Azure App Service and then try the same thing, the access token it receives to access SQL contains an application identity, not a user identity. That's no good because I need to be able to use row-level security and auditing at the database level.

 

This has been driving me slightly crazy - can anyone point me at any document or tutorial that uses this exact tech stack (not the older AAD version) to achieve that (and which works in an Azure App Service)? Since it all works perfectly in the dev environment I'm starting to suspect this is either a bug or an out-of-date feature in App Services. 

0 Replies