8

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:

enter image description here

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:

enter image description here

Setting the Azure Active Directory Admin for the database server:

enter image description here

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?

AjayKumarGhose
  • 4,257
  • 2
  • 4
  • 15
CrabbyAlex
  • 81
  • 1
  • 1
  • 3
  • Can you try logging to the Azure SQL Server directly through a user without adding it to the Azure AD admin group set for the Azure SQL Server?? – Kartik Bhiwapurkar Mar 16 '22 at 18:47
  • @KartikBhiwapurkar-MT Certainly. I can log in successfully when the user is the direct Admin. But I can't log in when they are not the direct admin - as expected. – CrabbyAlex Mar 17 '22 at 08:28
  • could you share the relevant part of the code you re using to connect to sql server ? depends which framework you re using you need to update your code to use the managed identity to connect to sql. – Thomas Mar 18 '22 at 00:03
  • @CrabbyAlex have you solved this? I'm having exactly the same, Admin Group does not work, it only works if i put myself. Making Managed Identity useless – Marcel Jun 19 '23 at 14:39

2 Answers2

6

• 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

Kartik Bhiwapurkar
  • 4,550
  • 2
  • 4
  • 9
  • Good page regarding this topic: https://blog.cellenza.com/en/cloud/how-to-secure-azure-sql-database-with-managed-identity-azure-ad-authentication/ – Kiechlus May 30 '23 at 21:32
  • Can you elaborate on point 2? So if it's created in a certain way, it won't work? Damn i thought managed identity would make stuff easier – Marcel Jun 19 '23 at 14:47
0

I my case I am using a custom ID instead of app pool identity for my Application setup in IIS in Azure VM. enter image description here After adding that particular user with db_datareader,db_datawriter to Azure Sql DB referring this link , This issue is resolved enter image description here

SQL Command

CREATE USER [test@domain.com] 
FROM EXTERNAL PROVIDER
WITH DEFAULT_SCHEMA = dbo; 
ALTER ROLE db_datareader ADD MEMBER [test@domain.com]; 
ALTER ROLE db_datawriter ADD MEMBER [test@domain.com];
Raghava Akula
  • 41
  • 6