Login failed for user '<token-identified principal>'

Question

My Web App fails to login to the DB server when the Azure Active Directory Admin is an AAD Group

I have been rolling out the 'new' Managed Identity feature on my Azure Web Apps with varying levels of success. In a nutshell, we would like to be able to control access to our Azure SQL servers through Active Directory. We have enabled Managed Identity on the Web App:

I decided that we would create an AAD Group, add any DBAs, and the Azure web app that is authorised to talk to that DB server. That AAD Group would then be assigned as the Azure Active Directory Admin. Here is the AAD Group containing a number of DBAs and the Web App as members:

Setting the Azure Active Directory Admin for the database server:

At this point, the users within the group are able to log in successfully through SSMS, whereas the Web App cannot. I receive the "Login failed for user '<token-identified principal>'" error in my Web App logs. If I set the Web App directly as the Azure Active Directory Admin, it can log in successfully.

I have installed the MSI Validator through the .scm. (Kudu) dashboard and confirmed that the Web App can successfully retrieve a token from the DB Server both when the Group is assigned as admin and the Web App is directly assigned as admin. (For completeness, I also attempted to access tokens from Servers that it shouldn't have access to and it could not retrieve as expected)

I have also tried the following:

• Deleting and recreating AAD Groups.
• Scaling Web App and DBs up and down to trigger restarts/cache clearing.
• Checked the MSI Environment details are set MSI Details in Environment

Is there anything else I can check? Or should I be raising a ticket with Microsoft at this point?

Answer 1

• There are some requirements below which are responsible for this scenario of yours that you are encountering regarding the managed system identity of the App service created not able to access the Azure SQL server while being in an Azure AD group as the SQL Server Administrator and when being assigned as the independent SQL Server administrator, it is able to access the SQL Server and its databases. They are as follows: -

A) The Azure AD group created should be a security group which in your case might not be a security group.

B) Also, the service principal used for the MSI when being in the group cannot use the ‘CREATE DATABASE SCOPED CREDENTIAL’ syntax against Azure SQL database inherently against the MASTER DB of the Azure SQL Server due to which when trying to login through the SSMS, it throws out an error. Thus, when independently assigned as the Azure SQL Server Administrator, it is assigned the ‘db_owner’ server role by default and thus, you can access the Azure SQL DBs through SSMS.

C) Finally, please select the default database to be logged on to through SSMS and add the MSI/service principal of the App service to the SQL DB that you are trying to connect to as this MSI/service principal may not exist there as AAD users are contained inside each user database. Also, it is not considering the MSI/service principal of the App service as the Azure SQL Server Admin when in an Azure AD Group which is assigned as the Azure SQL Server administrator.

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, in this case, the MSI/service principal, 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;

In this way, you should be able to overcome this issue. Please refer the below link for more details regarding the above: -

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

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview#azure-ad-features-and-limitations