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

Question

After deploying/swapping my AppService applications, I am seeing the following error occurring when connecting to my Azure Sql Database:

   Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'.

Answer 1

It took 6 full months to track this down, and my ticket was eventually escalated to the highest priority, which may sound important, but updates still took many days if not weeks before they were provided.

Azure works pretty great, but when it doesn't, the support apparatus is woefully lacking and quite terrible, to be honest. I do not have any sort of feeling of confidence if I encounter an actual "real-time" problem that it will be addressed in any decent amount of time to be meaningful.

Keep in mind these were errors occurring upon deployment in my production environment. Three of my 6 months were spent with Azure "support" who were adamant in telling me everything was working as designed, despite having exceptions thrown in a production environment. My system is configured with "Managed" Identity (which feels anything but after this ordeal) and this was leading to this issue. I configured everything as expected and described in documentation (it's a toggle in AppService) yet these exceptions still persisted.

For a while there, it seemed Azure Support was about to close the ticket "as designed" but after some persistence on my part we were able to get this moved to someone that was able to finally assist.

What is really the most shocking part of this is how poor the telemetry is on Azure's side. They really had no idea what was going on, despite these errors occurring on their own hardware completely within their own system boundaries. They had to rely on my logging system to have any sort of an idea of what was going on. I understand this up to a point, but if exceptions are occurring on a customer's production environment between two application boundaries completely in your control, my expectation is that you would have very comprehensive logging to understand what is occurring on your end.

The "workaround" for the moment is to disable a key security setting on my server that seems to be triggering the condition. So now my system is now freeballing a bit in its design and open to a potential attack. I am not comfortable with this at all, and now it is unclear how long this will be before someone in Azure will actually fix this problem.

Keep in mind it took 6 months to get here. Fully prioritized during half of them. 🤷‍♂️

Answer 2

Hi @Mike-E-angelo ,

Thanks for posting question in Microsoft Q&A platform and for using Azure Services.

Regarding your ask for Error: Login failed for user '<token-identified principal>', it means the user is invalid, usually related to a AAD user that does not have user created on SQL DB that you are trying to connect (User DB or Master DB) or that the user is not the AAD Server Admin.

Just create the user in the DB following the steps mentioned here: authentication-aad-configure

To create an Azure AD-based contained database user (other than the server administrator that owns the database), 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;  
 CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;  
 CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;  

If you are connecting from SSMS you may also need to change the default database option (Image below). By default it will try to connect to master DB where this user may not exists there as AAD users are contained inside each user database.

Hope this will help. Please let us know if any further queries.

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

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification

Answer 3

Mike,

I was getting the same error with the same stack trace, however, not after a deployment of our apps. Instead, we would get that error a handful of times once every 10 minutes. We have some scheduled tasks that run via WebJob timer triggers, one that runs every 60 seconds and one that runs every 20 seconds. These 2 timer triggers were the most obvious places where we would see the error every 10 minutes (but worked fine for the other 9 minutes). Occasionally I would see another piece of code get the same error at the same time as those timer triggers.

What seems to have solved it for us, was to scale up the Sql Azure DB, which is about as close as you can get to rebooting the server :) This was in a dev environment and we normally run as a S1, so I went up to a P1 for an hour (no more errors) and then back down to a S1. You might also want to try scaling your App Service up to a higher SKU for a couple of hours and then back down. That's helped us with odd issues more then once.

Answer 4

This answer solved the issue for me:

https://stackoverflow.com/a/77161216/8644294

Answer 5

As mentioned earlier, creating the user in the database following the steps in the authentication-aad-configure guide should resolve the issue. However, I continued to encounter the error despite assigning the necessary permissions to my app service identity.

I later discovered that my application was connecting to two separate database instances on the same server. The connection to the second database was failing, and the common try-catch block for both connections made it difficult to identify the issue.

By enabling verbose logging, I realized that the second database also required the app service identity to be added. Once I added the identity to the second database, the problem was resolved.