Today I managed to solve the problem “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON‘. (Microsoft SQL Server, Error: 18456)”. This error showed up when I tried to connect to an Azure SQL Database with SQL Management Studio. The complete error was as follows:
Cannot connect to mydatabase.database.windows.net.
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (Microsoft SQL Server, Error: 18456)
The error is somewhat misleading. Typically it has nothing to do with an anonymous logon. More likely you did not configure the connection settings correctly, or you don’t have local permissions on the database to connect.
3 fixes for error 18456
In this post, I describe the most common causes for error 18456:
- You did not specify the database (and left it set to <default> )
- You did not specify AD Domain Name or Tenant ID
- You don’t have the Connect permissions to connect to the database
You did not specify the database (and left it set to <default> )
When you’re trying to connect to a specific database on an SQL server, you need to specify it in the Connection Properties of SQL Management Studio. Here’s how to do so:
- In Connect to Server window, select tab Connection Properties
- In the Connect to database field, click the dropdown arrow. This will list all databases you have access to.
- Select the database
- Click Connect
Didn’t work? Is your account MFA-enabled? Then try the steps below!
You did not specify AD Domain Name or Tenant ID
In case your account is configured to require MFA, you need to set 2 things in the Connect to Server window.
- Tab Login: Authentication must be set to Active Directory – Universal with MFA Support
- Tab Connection Properties: AD domain name or Tenant ID must be checked and set to your Azure account. I.e. itexperience.onmicrosoft.com
Also, make sure you’re connecting to the right database.
You don’t have the Connect permissions to connect to the database
Minimal permissions required to connect to a database are Connect permissions (db_datareader). If you don’t have these access rights assigned, you should ask the Database Administrator to assign these to your account. Assigning permissions to an Azure SQL Database is different from an on-premises SQL database. There is no graphical user interface / window for doing so. Instead, you need to execute SQL queries. In a future blog, I’ll describe the exact steps.
Here’s some information: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins
