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.
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.
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)
To enable Kerberos you will need to update your SSRS config file.
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”
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.
- 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?
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