SQL: Fix - Login failed for user 'NT AUTHORITY ANONYMOUS' Azure SQL Database Error 18456

At one of my customer sites, I recently started having an issue logging onto an Azure SQL Database. The customer had configured Azure Active Directory (AAD) and were using Multi-factor Authentication (MFA).

I had previously been using it OK.

I would connect to the server using SSMS, enter my username and password, and then be prompted for the MFA authorization. After I authorized the logon, I would then receive the error shown in the main image: Login failed for user ‘NT AUTHORITY\ANONYMOUS’ and Error 18456.

Now when I’ve seen this error before with on-premises SQL Server, it’s usually a Kerberos problem or a double-hop problem. But this was direct to Azure SQL Database.

Same error occurred on 17.9.1 and 18.3 for SSMS, and for Azure Data Studio.

Turns out that one of the admins at the site had removed my database user (and others) from the master database, AND, that was my DEFAULT database. That then led to this error. Putting the user back into master fixed the issue.

Other Causes

While researching this error though, I came across some other potential causes. The main one was that if you need to connect to a specific database, not to master, you need to ensure that you’ve typed in (i.e. not tried to select) the appropriate database name on the Options tab when trying to log in. If you have no database, or have the wrong one there, you’ll get the same error.

Now one other error was worth noting. There used to be a text box and checkbox on the Options tab that let you enter the domain suffix for your AAD credentials. What you need to do now, is to enter your full email address (AAD credential) as the Username after selecting to logon using MFA. If you don’t do that, again all goes but then right at the end, you get this error:

The critical part is that it says that the ‘User yourname@yourcompany.com’ returned by service does not match user ‘yourname’ in the request. If you see this, it means that you didn’t include the full address in the Username box.

2019-09-25