Login Failed for user '<token-identified principal>'. (Microsoft SQL Server, Error: 18456)

Megan Barnason 0 Reputation points
2023-05-09T17:13:32.1466667+00:00

I was booted off the server this morning and then when I tried to log back in, I keep getting the below error. I have not made any changes to my log in credentials. When I use the MFA verification, I get the text with the code and enter it, then get this error. Other users are able to login. I have administrator access where as they do not. I have changed nothing.

TITLE: Connect to Server


Cannot connect to XXXXXX.database.windows.net.


ADDITIONAL INFORMATION:

Login failed for user '<token-identified principal>'. (Microsoft SQL Server, Error: 18456)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

Azure SQL Database
{count} votes

3 answers

Sort by: Most helpful
  1. Alberto Morillo 33,251 Reputation points MVP
    2023-05-09T18:24:24.0633333+00:00

    A possible solution may be to drop the user on the database and recreate the user as a contained database user mapped to the Azure Active Directory identity as explained here. For example,

    CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
    CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;
    
    1 person found this answer helpful.
    0 comments No comments

  2. Rahul Randive 9,011 Reputation points Microsoft Employee
    2023-05-09T19:04:29.1566667+00:00

    Hi @Megan Barnason

    Thanks for your question.

    Please follow below blog for Login failed for user '<token-identified principal>'. (Microsoft SQL Server, Error: 18456) related error

    https://techcommunity.microsoft.com/t5/azure-database-support-blog/aad-auth-error-login-failed-for-user-lt-token-identified/ba-p/1417535

    Let us know if this help or if you need any additional information.

    Thank you!

    1 person found this answer helpful.
    0 comments No comments

  3. JonMark Pintas 11 Reputation points
    2023-11-26T14:20:45.3366667+00:00

    I was getting this error and found a solution. You should not add distribution lists to security groups in AAD / Entra ID. Unfortunately, AAD allows this to happen, but then during the authentication process you will get the above error

    1 person found this answer helpful.
    0 comments No comments