Configuring a Service Principle Name (SPN) for an SSRS application pool

  • Article

In my last post I talked about associating the SQL Server 2005 Reporting Services (SSRS) application pool with a domain service account rather than NETWORK SERVICE to enable access to a data source outside a firewall. One thing I forget to talk about that warrants a bit more detail is the need to register a Service Principle Name (SPN).

Users will need to connect using integrated Windows authentication with their Active Directory credentials, so I started testing access to the SSRS instance.. After I set up my new application pool for SSRS, I could connect to the report server locally, but when I tried to connect remotely I got prompted for credentials, none of which worked.

After a more thorough reading of the patterns and practices article in my last post, I saw the section at the end entitled "Creating Service Principal Names (SPNs) for Domain Accounts". In order to connect to my SSRS instance over HTTP using Kerberos, I needed to register two different Service Principle Names with Active Directory for the domain service account that I associated with the SSRS application pool.

This is accomplished using the SETSPN.EXE tool that comes with the Windows Support Tools add-on for Windows Server 2003. I recommend using the new version of the tool that ships with SP1 as it has more informative error messages.

Here's the syntax and the output I used for my first failed attempt:

C:\Program Files\Support Tools>setspn -A HTTP/servername domain\svcaccount

Registering ServicePrincipalNames for CN=Service Account,OU=UserAccounts,DC=domain,DC=corp,DC=microsoft,DC=com
HTTP/servername
Failed to assign SPN on account 'CN=Service Account,OU=UserAccounts,DC=domain,DC=corp,DC=microsoft,DC=com', error 0x2098/8344 -> Insufficient access rights to perform the operation.

Apparently registering an SPN with Active Directory requires a level of privileges that my lowly user account did not have. So after a quick call to the help desk I was able to have a support technician with the appropriate privileges register the SPN for me. I also asked the technician to register a second SPN for the FQDN of the server. Note that this operation occurs against Active Directory, so you can run it from anywhere on the domain.

After the SPNs were was registered I was able to verify them using the following command:

C:\Program Files\Support Tools>setspn -L domain\svcaccont
Registered ServicePrincipalNames for CN=Service Account,OU=UserAccounts,DC=domain,DC=corp,DC=microsoft,DC=com:
HTTP/servername.domain.corp.microsoft.com
HTTP/servername

Now that my SPNs are registered, domain users can connect to the site remotely using Kerberos authentication.

Here's a recap of the infrastructure I've established in order to start building and deploying operational reports:

  • Production Airframe Database
    • Hosted outside the firewall
    • SQL authentication enabled
    • SQL login reporting created on production SQL Server 2005 instance to be used for reporting connections
    • SQL database user reporting created in the Airframe database and associated with login reporting. User added to the db_datareader database role for read-only access to all the tables in the database
  • Hot Standby Airframe Database
    • Hosted outside the firewall
    • Hosted on a different server than the production database
    • Updated hourly via log shipping, database is left in standby mode to enable read-only access.
    • Used for disaster recovery and to offload overhead associated with operational reporting from the production database
    • SQL login reporting created with the same SID as the production instance
    • SQL database user reporting in the Airframe Hot Standby database associated with SQL login reporting since it has the same SID.
  • SSRS Operational Reporting Instance
    • Hosted inside the firewall
    • Created new application pool for the Report Server and Report Manager components using the Reporting Service Configuration Tool and associated it with a domain service account
    • Registered two SPNs for the domain service account to enable Kerberos authentication for my new SSRS instance.

Now that I have all of the plumbing in place, we can proceed with the development of the operational reports and start granting users access.