How to authenticate to Azure database with the users credentials not the web apps

Question

I have an ASP.Net MVC web application that connects to an azure sql database. I have an account set up on that database using my AAD login. When I run locally (localhost) the web application loads fine and my credentials are authenticated successfully and I am able to query the database. When i publish the application to an app service on the cloud i am unable to authenticate on the database.

I followed this tutorial https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi initially, which I understand authenticates as the app itself once published (I've proved this by registering the app to AAD and adding the Application API login to the Database)

What i really want is a way to authenticate as the user of the app not the app itself - i.e. An Azure version of Kerberos which we currently use for our on-prem applications

Answer 1

Given you have understood how the AAD Authentication works in Azure SQL DB, and you have successfully configured MSI to authenticate with Azure SQL DB, here is what you need to do to authenticate individual user accounts in Azure SQL DB using AAD.

• Register an application with your AAD (https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-app)
• Under API Permissions add one more permission -> Azure SQL Database
• Select Delegated Permissions -> User impersonation
• Create a new security group in your Azure SQL DB
• Grant that security group permissions to your SQL DB instance (https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure#create-contained-database-users-in-your-database-mapped-to-azure-ad-identities)
• Also add the application to the SQL DB as external user (the same way you add the security group)
• When you sign-in your users to your application, request an access token for Azure SQL DB using resource=https://database.windows.net in your authorization request.
• Use the access token to access the database in the context of the signed-in user: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure#azure-ad-token

If you successfully completed all the steps you shall be able to sign in to the database using AAD access_token with security context of a signed-in user. But the question is - do you really want to do that? This only makes sense if you are taking data authorization decisions inside your database. And really configure user accounts within the database.