Hiding Subreports from the List of Available Report Documents in SharePoint

Does the SharePoint library which contains your SSRS reports look a bit like this?

The user is asked to look at a list of RDL files and guess which one is the top level report, and which are subreports which are only intended to be consumed inside a ‘container’ report or are used as the target of hyperlink actions inside another report.  But it’s easy to hide reports which are not intended for users to run individually by making use of SharePoint’s View features.  This example has screenshots from SharePoint 2010 but you can also do the same thing in MOSS 2007.

The goal is not to physically move the subreports, but to make them hidden from the list a user is presented with by categorising them in a managed way.  So, from the Library ribbon, click “Create Column”.

In the next dialog, fill in the following values:

• Column Name: "Report Type"
• Type of information: "Choice"
• Require that this column contains information: "Yes"
• Enforce unique values: "No"
• Choices: "Report", "Subreport"
• Display choices using: "Drop-Down Menu" or "Radio Buttons"
• Allow ‘Fill-in’ choices: "No"
• Default value: "Choice", "Report"
• Add to default view: Checked

You should end up with a page something like this:

After you create the column, you will be returned to the library. As long as you checked the “Add to default view” box when you created the column, you should now have an additional column headed “Report Type”, with no values for any of the reports.  So, edit the properties of each report and set the value for the new “Report Type” property to either “Report” or “Subreport”.

Once you have completed categorising your reports and subreports, create a new view by clicking the “Create View” button in the library ribbon.

Select “Standard view”, then create the view with the following settings:

• View name: "Reports only"
• Make this the default view: Checked
• View audience: "Create a public view"

You can add in any additional columns or sorts as you wish, then in the “Filter” section, enter the values you need:

After creating the view, you will be taken back to the report library but now you are only seeing the top level reports.

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/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.