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/WFENLB.mycompany.com:80 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.
SharePoint
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.
