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

Question

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

Answer 1

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;

Answer 2

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!

Answer 3

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