Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ with SQL Server Reporting Services

Before I talk about this error message, I want to apologize to followers and students I had promised this post a while back.  Sorry for delay, I will try to be more prompt on my future posts.

Anyhow, so how many people have seen that exact message?  “Login failed … ‘NT AUTHORITY\ANONYMOUS LOGON'”?  What does this message even mean?  And why do I have to deal with it?

In my experience I have seen this message in three or four difference cases.  I think in all cases the issue is similar, so I hope this post helps your troubleshoot and fix this issue within your environment.

Problem Statement

When running reports on SQL Server Reporting Services (SSRS) for some reason I keep getting login failed with ‘NT AUTHORITY\ANONYMOUS LOGON’.  I am not sure what is going on, the connection string for report is set to “Windows Integrated Security” like in image below. I have access to the server, I confirmed my login “Domain\username” has full rights to the SQL Instance and database, hell I am “sysadmin”; there should be no reason to get this error message.

So what is going on?

Well one can say it is an issue with your connection string?  That is you have it set to “Windows integrated security” that is why you are getting this.  So people often either a) hard code SQL Server login credentials or b) a service account credentials within the connection string.  All-in-all, I don’t have problem with that however it makes it hard to control what an individual can execute or what they have permission to.  Windows integrated security is a good approach.

That said what is the issue then?

Problem is simple, it is an issue with delegation.  Windows 2008+ defaults to Kerberos authentication model.  In which when you log into SSRS, it uses your credentials to authenticate you.  However when you run the report, for SSRS to authenticate you to the SQL Server instance in question, we must have proper delegation setup with in active directory.  For that we have to do following steps to resolve this problem…

Check to see if you have SSRS configured correctly

1. RDP to SSRS server.
2. Go to Reporting Services Configuration Manager.
3. Go to Service Account.
4. Confirm that you have Service Account set to a Domain Account and NOT Network Services (Default).
5. Check for Service Principal Name using following command:”SETSPN -L Domain\ServiceAccountName” (this is your SSRS service account).
6. You should have one SPN “http/servername.FQDN”.  If you are using non-default port (other then 80); than it should be “http/servername.FQDN:PORT”.
7. If you are missing the SPN please add it using the following command:”SETSPN -A http/servername.FQDN:Port Domain\ServiceAccountName”.
8. Now go to your Active Directory administrator and ask them to give the SSRS service account we created SPN for in step #7, Delegation rights under Delegation Tab, “Trust this user for delegation to any service (Kerberos only)” as in screenshot below.
9. Restart SSRS.

If you try to run your report it should work now :).  If you guys have any other issues please let me know.

Few Notes…

• If you change service account in your SSRS, please back up the encryption key before changing.
• If you are using single service account for everything (not best practice), consider setting “Trust this user for delegation to specific services only”.