I recently came across an error involving SQL Server Reporting Services (SSRS) that I recognized immediately but still took me some time to resolve due to intricacies of this particular environment.
This involves a scenario with a Web App calling into SSRS which then connects to a separate SQL Cluster.
The admin of the Web App reported this error when calling SSRS:
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source ‘DataSource’. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
As usual, not a lot of details. So I went into the Report Manager web interface (from a workstation) and tested the datasource, which turned out to be using Windows integrated security. I got
Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’
I had a feeling this was Kerberos related right away. The Web App admin, agreed and mentioned adding SPNs.
After much research, I found that the needed SPNs are
- HTTP / SSRS server, with no port
- SQL cluster, with SQL port
In my environment the SSRS server is accessed by its machine name but if it had a separate DNS entry or was load-balanced, this would have changed the name I needed to register.
We also had multiple nodes in the SQL cluster but the report datasource used the cluster name, so it is the name that matters for the “second hop”.
The solution that worked for us was like this:
setspn -a HTTP/ReportServer mydomainsqlreportsvc setspn -a HTTP/ReportServer.mydomain.local mydomainsqlreportsvc setspn -a MSSQLSvc/SqlCluster:1433 mydomainsqlEngineServiceAcct setspn -a MSSQLSvc/SqlCluster.mydomain.local:1433 mydomainsqlEngineServiceAcct
In this example the SSRS machine name is “ReportServer” in the “mydomain.local” domain running with a services account of “sqlreportsvc”. The multi-node SQL cluster had a cluster name of “SqlCluster” and it was running on port 1433. Names have been changed to protect the innocent.
Shout out:
I have to say Scott Stauffer’s chapter on Kerberos in SQL Server MVP Deep Dives was an excellent resource for me during the problem solving and fix of this issue.