SRS Configuration For

SRS Configuration for

Separated SQL Servers

Overview

In the event that the SQL Server database service is installed on a different machine from the SQL Server Reporting Services service, the following requirements must be put in place to ensure Kerberos authentication works between the servers (client/ SRS/ DB).

This document assumes that the reader is knowledgeable about the necessary sub-tasks required for success at each step.

Section 1 – System Requirements

• The servers must be members of the same (or trusted) domain(s)
• The services on each server must be configured to run under a Domain-based service account. This can be the same account for each service or different accounts. The domain account does need any special domain or local group membership.
• An arbitrary SPN must be configured in Active Directory for each the service accounts.
• The accounts must be configured properly for delegation.

Section 2 – Action steps

a.Set SPN’s (requires domain admin)

The following two steps must be run at a command prompt of a domain member machine and can only be performed by a Domain Admin

Set the SPN’s for the SQL Server service account as follows (make sure to specify the portused by SQL Server):

Setspn -s mssqlsvc/<SQLServerName>.<domain>:1433 <domain>\<SQL Server User name>

Setspn -s mssqlsvc/<SQLServerName>:1433 <domain>\<SQL Server User name>

Set the SPN’s for the SRS service account as follows:

Setspn -s http/<ReportServerName>.<domain> <domain>\<ReportServices User name>

Setspn -s http/<ReportServerName> <domain>\<ReportServices User name>

b.Confirm SPN’s are set correctly

Run the following two commands at the administrative command prompt to ensure the SPN’s for both accounts are set properly.

Setspn –L <domain>\<SQL Server User name>

Setspn –L <domain>\<ReportServices User name>

If the above does not work:

-In AD, open account (check both of them)

-Right click, select properties

-Select attribute editor

-Look for ServicePrincipalName attribute and verify settings in previous step are present

c.Set Delegation on the SQL Report Service Account

Open Active Directory Users and Computers. Locate the domain user account, right-click the user account, and click Properties.

On the Account tab, under Account options, verify that the following option is not selected: Account is sensitive and cannot be delegated.

On the Delegation tab, verify that the following option is selected: Trust this computerfor delegation to any service (Kerberos only).

d.Restart the database and web servers

After setting the SPN’s the servers may need to be rebooted.

Restart both the SQL Server service and Reporting Services service at a minimum and test.

Section 3 - Reference/Troubleshooting

The following links and documents can be used as reference points

SETSPN Overview /
SRS Windows Auth /
SRS Kerberos /