SQL Server Login Failed for User 'NT Authority\Anonymous Logon' Fix

Yeah, this one's a pain. You're staring at a SQL Server login failure for NT AUTHORITY\ANONYMOUS LOGON, and it's not your typical wrong password. The culprit here is almost always Kerberos double-hop authentication. Let's fix it.

The Quick Fix (Most Common Scenario)

You're connecting from a web app or SSRS to SQL Server, and somewhere in the middle there's a pass-through authentication that didn't actually pass through. The server got an anonymous token instead of the user's real credentials.

Step 1: Identify the service account
Run this on the SQL Server to see which account SQL Server runs under:

SELECT servicename, service_account FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%'

Step 2: Register a Service Principal Name (SPN) for that account
On a Domain Controller, open Command Prompt as admin and run:

setspn -A MSSQLSvc/your-sql-server.yourdomain.com:1433 domain\sqlserviceaccount
setspn -A MSSQLSvc/your-sql-server:1433 domain\sqlserviceaccount

Replace your-sql-server with the actual machine name and domain\sqlserviceaccount with the account from Step 1.

Step 3: Enable Constrained Delegation
Open Active Directory Users and Computers. Find the service account. Right-click -> Properties -> Delegation tab. Select "Trust this user for delegation to specified services only" -> "Use Kerberos only". Click Add, then Users or Computers, enter your SQL Server's name. In the services list, select MSSQLSvc entries. Click OK.

Step 4: Restart SQL Server
Run this in SQL Server Management Studio or via Services.msc:

NET STOP MSSQLSERVER && NET START MSSQLSERVER

Test your connection. If it works, you're done. 90% of the time this is the fix.

Why This Works

Kerberos double-hop is the root cause. When a user authenticates to a web server (first hop), then the web server tries to pass those credentials to SQL Server (second hop), Kerberos says "nope, I'm not handing out tickets to random machines." The result: the SQL Server gets an anonymous token.

The SPN tells Kerberos which service account owns SQL Server on that machine. Delegation allows the service account to request tickets on behalf of the original user. Without both, you get the anonymous logon error every time.

If you skip delegation and just use SQL Server authentication, you bypass Kerberos entirely. That works too, but it's less secure in some environments.

Less Common Variations

Linked Servers
If the error shows up when querying a linked server, the problem is the same but the fix is slightly different. On the source SQL Server, run:

EXEC sp_serveroption 'LinkedServerName', 'data access', 'true'
EXEC sp_serveroption 'LinkedServerName', 'rpc out', 'true'

Then configure delegation for the source server's account to the destination server. Check the SQL Server error log — if you see Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' followed by OLE DB provider 'SQLNCLI11', that's your linked server.

SSRS (SQL Server Reporting Services)
With SSRS, the fix often involves changing the SSRS service account to a domain account (not NetworkService) and then setting delegation for that account. Don't bother with the UseIntegratedSecurity=true in SSRS config — it rarely helps here. Instead, go to the SSRS web portal, set the data source to use Windows integrated security with the service account, then configure constrained delegation on the service account to the SQL Server.

IIS and Application Pools
If the app pool runs as NetworkService, you'll hit this error. Switch the app pool to a domain account and set delegation for that account. Also verify the app pool's loadUserProfile is set to true — this matters more than people think for Kerberos ticket caching.

Prevention

Don't wait for the error to pop up. Here's what to do before you deploy:

• Use domain accounts for SQL Server services, not local accounts or NetworkService. It makes delegation possible without jumping through hoops.
• Register SPNs during setup. It's one command per server. Script it.
• Document delegation in your environment. If you have a web app talking to SQL, list which service accounts need delegation and to which servers. This isn't a set-and-forget thing — updates can break it.
• Test with SQL auth first to isolate the problem. If the query works with SQL auth but fails with Windows auth, it's almost certainly Kerberos. Then fix delegation.
• Monitor the security log for Kerberos ticket errors (event ID 4768, 4769). They'll tell you which service account failed to get a ticket, saving you hours of guessing.

One last thing: if you're on SQL Server 2022 and still getting this, check if you're using TCP/IP over named pipes. Named pipes don't support Kerberos delegation properly in some builds. Switch to TCP/IP in SQL Server Configuration Manager if that's the case.

That's it. No magic. Just SPNs, delegation, and the right service account. Get those three right and you'll never see this error again.