Delegate Auth Token permissions

Question

I'm trying to connect to the Azure SQL DB using the generated token given when the user authenticates in the web app, but I'm receiving the following message when I try to add the token to the DB connection: "AAD Auth Error - Login failed for user '<token-identified principal>'".

the WebApp and the API are created at the AD applications and the token can be used at the API to protect the API from non-authorized usages, but when I try to use the same token or acquire a new one for the logged user, I always get the same error message...

Is it possible to do?

The user connects using the MS login page at the app, the token returned from MS Login is sent to the API to protect routes and use the same credentials to connect to the Database.

Thanks

Answer 1

Hi @PauloMarques-9662 ,

Thanks for reaching out.

I understand you are trying to connect Azure AD with Azure SQL DB and getting error "AAD Auth Error - Login failed for user '<token-identified principal>"

This is usually related to an AAD user which is not added on SQL DB that you are trying to connect or that the AAD user is not added in the AAD Server Admin.

You just need to add an AAD user in Azure SQL DB. Thus, you need to add the user in Azure SQL DB as the contained user. To create and add an Azure AD based contained user, connect to the database with an Azure AD identity, as a user with at least the ‘ALTER ANY USER’ permission. Then use the following Transact-SQL syntax: -

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;

Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell&view=azuresql#create-contained-database-users-in-your-database-mapped-to-azure-ad-identities

Hope this will help.

Thanks,
Shweta

---------------------------------------

Please remember to "Accept Answer" if answer helped you.