azureazure-active-directoryazure-web-app-serviceazure-sql-databaseazure-managed-identity

Reputation: 161

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

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?

Upvotes: 16

Answers (7)

Thomas Koelle

Reputation: 3752

I got this error in Sql Server Management Studio when connecting to a database in Azure.

It was because I had not set database to connect to in the "Connection properties" tab.

I will just add it here because the title matches and google leads you here.

Upvotes: 0

usefulBee

Reputation: 9702

Login failed for user 'token-identified principal'

Finding the root cause of this error can really be annoying. In my case, I wanted my Azure Function App to communicate with my SQL Server database using Microsoft Entra passwordless authentication. Since my function did not have the proper privileges to access the database, it threw this error.

I found buddhabhatti's video explanation or article titled "Azure SQL Database and Azure Function Integration: A Seamless and Passwordless Connection(Microsoft Entra)" to be very helpful and straight forward.

In the 3rd step, she explains:

Replace “DEMO-HUB-DEV-FUNAPP” with your Azure Function

CREATE USER [DEMO-HUB-DEV-FUNAPP] FROM EXTERNAL PROVIDER;
/*For db_datareader and db_datawriter */
EXEC sys.sp_addrolemember
@rolename = N'db_datareader',
@membername = [DEMO-HUB-DEV-FUNAPP]

EXEC sys.sp_addrolemember
@rolename = N'db_datawriter',
@membername = [DEMO-HUB-DEV-FUNAPP]

GRANT EXECUTE TO [DEMO-HUB-DEV-FUNAPP]

GRANT SELECT TO [DEMO-HUB-DEV-FUNAPP]

After following those steps my function now has the proper privileges, and the Login failed error message disappeared.

Maybe the only thing I would change is to use ALTER ROLE instead of sp_addrolemember.

Upvotes: 0

Macro

Reputation: 51

Same here 2 years later. Maybe is this:

The back-end services of managed identities also maintains a token cache that updates the token for a target resource only when it expires. If you make a mistake configuring your SQL Database permissions and try to modify the permissions after trying to get a token with your app, you don't actually get a new token with the updated permissions until the cached token expires.

The last note of the paragraph

Upvotes: 0

Ash K

Reputation: 3701

Easiest way to solve this error is using something called Service Connector. It takes care of enabling System Identity, adding AAD user (this will be your Azure App Service Name), adding appropriate connection string to the database in your App Service Configuration and so on.

WARNING: Running this command will make the user ID that is running the command the admin of the SQL Server.

Give it a try:

az webapp connection create sql
-g <your-resource-group>
-n <your-app-service-name>
--tg <your-database-server-resource-group>
--server <your-database-server-name>
--database <your-database-name>
--system-identity

More info here at Microsoft Learn.

Upvotes: 1

Mahesh

Reputation: 369

I had the same issue when I disabled the 'System Managed Identity' and enabled after few days.

Solution: I realized that the Principal Id is different (new id) every time we enable 'System Managed Identity'. So I had to drop the user from the database and recreate it.

CREATE USER [<APP SERVICE- API NAME>] FROM EXTERNAL PROVIDER
ALTER ROLE DB_DATAREADER ADD MEMBER <APP SERVICE- API NAME>
ALTER ROLE DB_DATAWRITER ADD MEMBER <APP SERVICE- API NAME>

Upvotes: 5

Raghava Akula

Reputation: 111

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

SQL Command

CREATE USER [[email protected]] 
FROM EXTERNAL PROVIDER
WITH DEFAULT_SCHEMA = dbo; 
ALTER ROLE db_datareader ADD MEMBER [[email protected]]; 
ALTER ROLE db_datawriter ADD MEMBER [[email protected]];

Upvotes: 3

Kartik Bhiwapurkar

Reputation: 5165

• 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

Upvotes: 10

Login failed for user &#39;&lt;token-identified principal&gt;&#39;

Answers (7)

Related Questions

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