SPN for Reporting Services
SQL Server Reporting Services uses Windows Authentication to authenticate users when they connect, but it also impersonates the user. Depending on how you’re setup, this often requires the creation of an SPN. SPN’s are awesome when they work, they allow integration with Active Directory, but when they don’t work, it’s a nightmare to fix.
Reporting Services often requires an SPN assigned to the account running the Reporting Services Service. You’ll know that you need to set this up when you try connecting to your Reporting Services instance from within the same domain and you are prompted for credentials. If SPN’s are setup correctly then your browser will work out the authentication for you and your users won’t need to login again.
This SPN should be HTTP/Server and HTTP/Server.domain and it needs to be added to the service account that’s running your service.
Setspn -s HTTP/ReportingServiceServer DomainAccount Setspn -s HTTP/ReportingServiceServer.mydomain.com DomainAccount
NOTE: the fully qualified SPN seems to be breaking things
Seems pretty easy, and when you test out reporting services you won’t be prompted for credentials. Perfect, right?
Almost.
Everything falls apart once you try to manage the server remotely. Whether that be through remote powershell, Server Manager or the new Windows Admin Center, you will get a Kerberos error telling you that you cannot connect to the server.
Setting the HTTP SPN to an account breaks remote management.
How do we fix it? I don’t think you can. If you bind the HTTP SPN for the Host Name to the account running Reporting Services (or any other service), then you can’t remote manage the server.
But what if we didn’t set the SPN for the Host Name? Here is the simple 2 step work around that stops Reporting Services prompting for credentials AND allows remote management:
- Create a new CNAME DNS entry for your server (maybe “ServerNameReports”. Point it to your actual server.
- Add the HTTP SPN to the CNAME alias
So in powershell you just need to:
Add-DnsServerResourceRecordCNAME -Name "ReportingServicesServerReports" -HostNameAlias "ReportingServicesServer.mydomain.com" -ZoneName "mydomain.com" Setspn -s HTTP/ReportingServiceServerReports DomainAccount Setspn -s HTTP/ReportingServiceServerReports.mydomain.com DomainAccount
When you access the report server, use http://reportingserviceserverreports/reports and you will not be prompted for credentials. With no SPN on the actual server name, you will still be able to remote manage the server.