Configuring Kerberos for SSRS – Lessons Learned

If you need to do any authentication “double hops” in your SSRS environment you will need more than just NTLM Authentication.

An example of this would be if you had a data source that resided on another server and you need to pass through windows authentication.

When you went to test the connection you would get an error like this.

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

For a situation like this, you need to ensure your SSRS server and service account is configured to use Kerberos.

This article is an attempt to pull together the basics of what needs to be configured as well as a checklist of things that could go wrong.

Image: Wikipedia

Kerberos configuration tool

The Kerberos configuration tool is a great help in ensuring you have the basic things done properly.

Warning though, even though it might say everything is fine it may still not work for you.

You can download this tool from here: https://www.microsoft.com/en-us/download/details.aspx?id=39046

Once you install it, browse to the installation folder and run the exe.

Make sure you run as admin and I have found it is best to run on the actual SSRS Server you are trying to troubleshoot.

SPN’s

To check existing SPN’s for a user account run with the -l parameter E.g

H:\>setspn -l domainname\serviceaccname

To add a new SPN run with -a

To delete an SPN use -d

The Kerberos configuration tool creates the statement for you which is the easiest way of doing it.

After many issues (and this is probably overkill but anyway) I recommend you add SPNS for the hostname, the aliasname and add the port as well (if not port 80 for the HTTP SPN’s)

Authentication

To enable Kerberos you will need to update your SSRS config file.

Use the authentication type RSWindowsNegotiate.

Local security policy

This can be easily overlooked.

You must ensure that the service account for the SSRS service is a member of the local security policy “Impersonate a client after authentication”

Service account

The service account needs to be configured to Trust the user for delegation in AD.

TIP: The Delegation box will not appear unless SPNs are set.

Troubleshooting

  • Have the SPN’s been set and spelt correctly?
  • Does the Kerberos Configuration tool show any issues?
  • Has the Local Security Policy been set?
  • Is the SSRS mode of authentication correct?
  • Have the AD Delegation settings been done?
  • After all of the above – Have the server and services been restarted?

References

TechNet – Enabling Kerberos Authentication for Reporting Services
SQL Server Central – Solving Kerberos Issues in SSRS When Running Beside IIS
ITProToday – Implement Kerberos Delegation with SSRS

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

1 Comment

  1. Nice post. One more thing that can be easily overlooked is if you test the connection with a data source that accepts only NTLM, then it does not matter that you have already set everything else correctly. This is what happened to me; I had a SQL server with a test database where Kerberos auth requests were just turned down. Once I corrected that, it all started working… 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.