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

Mike-E-angelo 476 Reputation points
2022-09-10T10:38:02.293+00:00

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>'.
Azure SQL Database

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mike-E-angelo 476 Reputation points
    2023-03-22T12:22:47.32+00:00

    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. 🤷‍♂️

    8 people found this answer helpful.
  2. ShaktiSingh-MSFT 13,751 Reputation points Microsoft Employee
    2022-09-14T05:44:41.73+00:00

    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.

    240756-image.png

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

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

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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
    3 people found this answer helpful.
  3. asalvo 6 Reputation points
    2022-11-17T22:37:19.887+00:00

    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.

    1 person found this answer helpful.
  4. Ashish Khanal 5 Reputation points
    2023-09-23T00:21:17.8833333+00:00

    This answer solved the issue for me:

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

    1 person found this answer helpful.
    0 comments