System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITYANONYMOUS LOGIN'

Depending on how you installed SQL Server you may receve an SqlException -2146232060 when you are connecting to SQL Server from the web server using the credentials of the end user.

One probably reason could be an error in the SPN registration.

During installation of SQL Server you need to decide what service account you are going to use for the SQL Server service. If you are using one of the built-in accounts (Local System, Local Service, Network Service) then the SQL Server service is able to maintain the SPN registration on your behalf because the SPN is registred on the computer account.

If you are using a service account (e.g. MYDOMAIN\SqlUser) for the SQL Server service, then the SPN must be registered on that account. If the SQL Server is installed by the domain administrator, then the installation will make that registration for you. But if you are installing the SQL Server as a local administrator on the Windows Server, then SPN registration may not work.

Start SQL Server Configuration manager to view the account name used for MSSQLSERVER:

Next check if there is registrered a SPN for that account. You can use either of the following two tools:

  • Command line tool: SetSPN.exe mydomain\sqluser
  • ADSI Edit: Open ADSIEdit.msc, connect to the domain, navigate to the account in Active Directory, view properties, select the servicePrincipalName attribute

In both cases you should find the following 3 registered SPN

MSSQLSvc/MYSQLSERVER
MSSQLSvc/MYSQLSERVER.mydomain.com
MSSQLSvc/MYSQLSERVER.mydomain.com:1433

Replace servername (MYSQLSERVER) and domain name (mydomain.com) with your own value.

Pay attention to not having duplicate SPN - you can check with setspn.exe -X. If the SPN is registered on the computer account and the SQL Server service is executed in the context of a user account, then remove the SPN on the computer account before you are registering on the user account. You may need to restart both web server and SQL server after changing the registration.