Practical Business Intelligence

May 23, 2011

Configuring Constrained Delegation in SharePoint 2010

Filed under: Business Intelligence, Kerberos, SharePoint — Duncan Sutcliffe @ 1:58 pm

I’ve been doing a *lot* of environment build and configuration work recently, specifically focusing on authentication and the right way to configure Kerberos so that all the business intelligence components of a SharePoint deployment can pass user credentials between themselves and down to data sources.  One of the key things for SharePoint 2010 is that you will need to configure constrained delegation, whereas in earlier versions you could get away with unconstrained delegation.

There will be a proper series of posts about Kerberos coming along (it’s a big subject with a lot of words to write – I teach a two day course if you are really interested), but in the interim here’s a pictorial guide to the delegation you will need to set up.

Constrained Delegation Pathways Map

The key points are:

  • Web site account delegates to itself, to any other web site accounts and to SSRS account over HTTP
  • SSRS account delegates to all report data sources’ accounts over MSOLAPSvc.3 and MSSQLSvc
  • Claims to Windows Token service account delegates to all PerformancePoint, Excel, Visio and InfoPath services data sources’ accounts over MSOLAPSvc.3 and MSSQLSvc
  • PerformancePoint, Excel, Visio and InfoPath services all delegate to all their required data sources’ accounts over MSOLAPSvc.3 and MSSQLSvc

April 15, 2011

Listing all the SPNs for a service

Filed under: Kerberos — Duncan Sutcliffe @ 6:26 am

This came up as a request on the Twitter hashtag #sqlhelp yesterday: how can you list all the SPNs for all the SQL Server instances in a domain? This is pretty easy to achieve using setspn.exe:

setspn -T (forest) -F -Q (service)/(server)

Both the service and the SPN can use wildcards so the following command will give a list of all the SQL Server SPNs in the entire forest “my forest”.

setspn -T myforest -F -Q MSSQLSvc/*

September 30, 2010

Kerberos in a Clustered and Load Balanced BI Deployment

As if it weren’t hard enough getting Kerberos delegation to work properly in your average business intelligence distributed architecture, there is another level of knowledge required when you’re building an environment that’s got lots of resilience built in.

This post covers the following scenarios:

  • Clustered SQL Server
  • Clustered Analysis Services
  • Scale out and load balanced Reporting Services
  • Reporting Services integrated with SharePoint

I’ve been building just such an architecture in a lab environment using SQL Server 2008R2 and SharePoint 2010 but the steps below should be applicable to previous versions as well.

The steps involved are:

  • Install SQL and SSAS clusters and set up DNS
  • Install SSRS for scale out deployment and set up network load balancer DNS
  • Setting SPNs
  • Trusting computers and service accounts for delegation
  • Configuring Reporting Services
  • Configuring SharePoint
  • Configuring Reporting Services/SharePoint integration

SQL and SSAS Clusters

The good news is you really don’t need to do anything that you wouldn’t normally do when setting up a cluster for either of these two services.  Create the cluster, install the services on the nodes, set up MSDTC, then create a DNS name that points at the cluster address.

Make sure each node in the cluster uses the same service account. For example, create a domain account called svc-SQL and use it as the service account for SQL on every node of the SQL cluster.  This is required, because the SPN to be set up later can only use one account for the cluster as a whole.  Do the same for Analysis Services.

SSRS Cluster

Here again there’s nothing out of the ordinary; the order I’d do it in is:

  • Provision the servers, configure the NLB to load balance across them, and create a DNS entry for the RS service on the NLB
  • Install Reporting Services but don’t configure it
  • Install the SharePoint binaries on each server and  join them to the farm (without making them a Web Front End)
  • Run the RS configuration tool on the first server, create a new database in SharePoint mode, then complete the rest of the configuration (report server web service, etc.)
  • Run the RS configuration on each other server, pointing them to the same Reports database.
  • After configuring all the RS servers, add all the servers to the scale-out deployment using the appropriate tab on the configuration tool.  You only need to do this on one of the servers.

Again, you need to make sure the webservice is running under the same account on all servers.

Service Principle Names

First real challenge.  There are some key points to bear in mind:

  • You create SPNs for the cluster name, not for the individual nodes in the cluster. Don’t create SPNs for the individual nodes as this seems to break Kerberos.
  • You must specify the port number, even if you are using the default ports for SQL Server and Analysis Services.
  • You must create SPNs using the fully qualified domain name of the servers, but creating them for the NetBIOS name seems to be optional – your mileage may vary on this.

The SPNs you will need are for:

  • SQL cluster
  • SSAS cluster
  • RS cluster
  • SharePoint Central Admin
  • SharePoint web sites
  • SharePoint applications such as PerformancePoint if you are using them

The commands will follow this template:

setspn –S MSSQLSvc/[SQL cluster].[domain]:1433 [service account]
setspn –S MSOLAPSvc.3/[SSAS cluster].[domain]:2383 [service account]
setspn –S HTTP/[RS NLB].[domain]:80 [service account]
setspn –S HTTP/[central admin server or NLB].[domain]:[port] [service account]
setspn –S HTTP/[web front end or NLB].[domain]:[port] [service account]

Let’s build some examples of that.  In this scenario, the SQL cluster is called SQL, the SSAS cluster is called AS, the SSRS load balancer is called RSNLB, the SharePoint Central Admin box is SP01, and the SharePoint web front end load balancer is called WFENLB. The domain is mycompany.local and all services are on their default port, other than Central Admin which is on port 8080.  You’d need to run the following commands.

setspn –S MSSQLSvc/SQL.mycompany.local:1433 svc-SQL
setspn –S MSOLAPSvc.3/AS.mycompany.local:2383 svc-AS
setspn –S HTTP/RSNLB.mycompany.local:80 svc-RS
setspn –S HTTP/SP01.mycompany.local:8090 svc-SPFarm
setspn –S HTTP/ svc-SPPortal

Something to bear in mind: if you have created an alternative URL for your SharePoint site (such as http://intranet), you need to create the SPN for that name instead of the WFE load balancer name.

Trusting for Delegation

On the DC, ensure that each of the following are trusted for Kerberos delegation:

  • SharePoint Central Admin server
  • SharePoint web servers
  • Reporting Services servers
  • SharePoint Central Admin web application pool account
  • SharePoint web site application pool account
  • Reporting Services web app service account

You can’t trust accounts until an SPN has been set up – the ‘Delegation’ tab doesn’t even appear in their account’s properties – so you have to complete this step after the SPNs have been created.

You might find you need to trust the SQL and SSAS boxes and service accounts.  You also might find you need to trust the virtual network names for their clusters.

Configure Reporting Services

On each of the SSRS servers, locate the file rsreportserver.config and edit it:

  • Find the element <AuthenticationTypes> and ensure it contains the element <RSWindowsNegotiate/>.  Remove the element <RSWindowsNTLM/> if it exists.
  • Find the element <UrlRoot> and insert the web address of the SharePoint site (in the example above the correct value would be http://WFENLB or http://intranet).
  • Find the element <Hostname> and add the name of the SSRS cluster (in the example above the correct value would be RSNLB).  The <Hostname> element may not exist, so you will need to create it – it goes inside the <Service> element, the same as the <UrlRoot> element.  You would create a line such as <Hostname>RSNLB</Hostname>.

You must repeat for every SSRS server otherwise only some of the servers will actually work and your users will get ‘intermittent’ errors.


A trip to Central Admin should show that the RS servers have already been added to the farm.  Make sure the Central Admin and all other site collections are set to use Windows Authentication in all their zones which you intend to use SSRS in.

SharePoint and Reporting Services Integration

SharePoint 2010 automatically installs the SSRS add in as part of the pre-requisites.  For MOSS 2007, download the latest version of the add in and ensure your entire SP farm is service-packed.

Integration is then ready to be configured from Central Admin. In SP 2010 the config panel is at General Application Settings –> Reporting Services Integration.  The values you need are:

  • Report Server Web Service URL: the address of the SSRS web service as accessed through the network load balancer, for example http://RSNLB/reportserver
  • Authentication Mode: Windows Authentication
  • Credentials: The username and password of a domain account which is in the local Administrators group on the SSRS servers.  If there isn’t a single account that’s in this group on every one of your SSRS boxes, use an account which is in the Administrators group for the first SSSR box in the deployment.  You need to specify the username in the format [domain]\[user]

After the first server is integrated, you are given the chance to add each of the other SSRS servers in the scale-out deployment to the integration.  You must complete this for each server you have in the deployment.

Finally, you can click ‘Set server defaults’ to change any of the the properties of the integration you want to alter.

Points to Bear in Mind

Not working?  Here’s what I check in my setup:

  • Kerberos needs TCP and UDP ports 88 open in order to work.  Is your firewall blocking that port?  Try disabling the Windows firewall to check.
  • Are the clients using Internet Explorer 6 or above?  Is the browser seeing that the site is in the local intranet zone? Does the zone allow integrated Windows authentication?
  • Go back over the SPNs.  One mistake in your setspn command will have broken Kerberos completely.
  • Check all the accounts and computers are trusted.
  • Try navigating direct to the SSRS web service (i.e. not by opening a report in SharePoint’s interface).  If you can run a report through http://rsnlb/reportserver but get an error message when doing it through SharePoint, the probable cause is with a SharePoint SPN or delegation permission not with Reporting Services.

The Shocking Blue Green Theme. Create a free website or blog at


Get every new post delivered to your Inbox.