SSRS with a FQDN

I had the need to set up a SQL Server Reporting Services instance with a fully qualified domain name (FQDN). The server worked fine when accessed simply by the machine name:

http://machinename

Running on the actual server, I was able to see the reports without any credential entry required.

But when a DNS entry was added pointing to the IP address:

http://reports.nicedomain.net

an endless loop of authentication dialogs was presented that seemingly ignored any credentials that were entered.

We are running SSRS and SQL Server as domain service accounts.

Kerberos vs NTLM

When accessing the server with just the machine name it is considered “local intranet” or similar and uses NTLM for authentication.

When the FQDN is used it switches to Kerberos so some additional config is needed. The Microsoft docs explain that we need a SPN to make this work:

If you are deploying Reporting Services in a network that uses the Kerberos protocol for mutual authentication, you must create a Service Principal Name (SPN) for the Report Server service if you configure it to run as a domain user account.

What SPNs to add? Download and run the Kerberos Configuration Manager.

This would show the missing SPNs, and provides a script to add them. We maybe went a bit overkill in trying to work out what was required

SetSPN -s "MSSQLSvc/machinename.domain.net:INSTANCE_NAME" "DOMAIN\SVC_ACCOUNT" 
SetSPN -s "MSSQLSvc/machinename.domain.net:PORT" "DOMAIN\SVC_ACCOUNT" 
Setspn -s http/machinename.domain.net "DOMAIN\SVC_ACCOUNT"
SetSPN -s http/machinename "DOMAIN\SVC_ACCOUNT"
SetSPN -s http/machinename.domain.net:443 "DOMAIN\SVC_ACCOUNT"
SetSPN -s http/reports.nicedomain.net "DOMAIN\SVC_ACCOUNT"
SetSPN -s http/reports.nicedomain.net:443 "DOMAIN\SVC_ACCOUNT"

This needs to be done by a domain admin.

Delegation

The service account also needs some Kerberos delegation - I think this solves the double-hop problem where SSRS needs to pass the user credentials onto SQL Server.

In the Active Directory config for the user (once again by a domain admin):

Once this is verified to work the delegation can be limited to a less privileged level.

Kerberos Configuration Manager will then show the Delegation is correct:

Config

On the SSRS server need to edit RsReportServer.config, probably at C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer so that <RSWindowsNegotiate /> is the first entry in the authenticationtypes section:

SSL

To enable the SSL domain we need a certificate created and imported into the server. Then on the SSRS server open Report Server Configuration Manager and in the Web Service URL and Web Portal URL sections add the binding:

Reboot and hopefully all the Active Directory stuff has propagated. If it’s all working, the default machine name port 80 binding can be removed.

This was all far more than I ever wanted to know about SSRS and Kerberos!

Some tips were gained from:

• http://www.dbafire.com/2018/06/09/configuring-kerberos-for-ssrs/
• http://mglaser.bloggingabout.net/2007/01/26/hosting-reporting-services-on-a-fully-qualified-domain-name-fqdn/
• http://callumhibbert.blogspot.com/2009/02/kerberos-delegation-and-sql-reporting.html
• https://docs.microsoft.com/en-us/sql/reporting-services/report-server/register-a-service-principal-name-spn-for-a-report-server?redirectedfrom=MSDN&view=sql-server-ver15