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

Question

I get the error Login failed for user ''. (Microsoft SQL Server, Error: 18456) from Azure SQL server when a user tries to login using Azure Active Directory - Universal with MFA.

My Azure AD login is within a group, other members of the group can login to the database, but I get the error '<token-identified principal>'. (Microsoft SQL Server, Error: 18456), however If the user is added to the database, then I can login and it works.

create user [[email protected]] from external provider
GO
EXEC sp_addrolemember N'db_datareader', N'[email protected]'

I was wondering if there is a reason for this.

Answer 1

I had this error as well, my workaround was to go into 'Options -> Connection Properties -> manually put the database name I wanted to connect to'

In my org I had only been given permission to the specific database, not the database server.