Skip navigation.

Practical Business Intelligence

or, How I Solved my Data Warehouse Problems Using SQL Server.

Posts tagged with "Reporting Services"

Kerberos Issues With Reporting Services and SharePoint in a Distributed Environment

Friday, 20. February 2009, 10:28:02

, , , ...

This seems to cause huge headaches when integrating Reporting Services and SharePoint. After installing the addin correctly and successfully on all your servers, you are unable to complete the setup in Sentral Administration when using Windows Authentication. In "Manage Integration Settings", you are constantly plagued by the error
Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized
You most likely get this message only when your various services are running under domain accounts rather than the built in accounts like Network Service.

This is caused by incorrectly configured Kerberos somewhere in the system. Kerberos is an authetication mechanism used to pass credentials from one service to another across computers. It's the mechanism which allows SharePoint to authenticate to Reporting Services on a separate server, and which allows a user's credentials to be passed from their browser to SharePoint to Reporting Services to a data source.

The problem with Kerberos is that you don't just click a setting to enable it. It doesn't need to be enabled - it needs to be configured, and there are lots of places the configuration can go wrong. Depending on how your environment was set up, you may find problems in several different places. Here is my list of steps to sort everything out and get it working.

  1. If you have any IIS websites on your Reporting Services machine, and they have an SPN already set, you will have to run Reporting Services web service under the same account as the app pools your other sites are using. This is because the http service can only have one SPN assigned to it (see below). In practical terms this is most likely to be a problem if your RS server also has a SharePoint front end web site which you want to keep active for some reason. In this case you would have to run RS using the same account as you run SharePoint's web app.
  2. Setting SPNs
    Kerberos requires that a Service Principal Name is registered in the domain's Active Directory for the http service. This is explained at this Knowledge Base article. Essentially you need to run 
    setspn -a http/<ReportingServer> <domain>\<user>
    setspn -a http/<ReportingServer>.domain <domain>\<user>
    You need domain admin rights to do this.
  3. Remove duplicate SPNs
    If there is already an SPN for the http service on the Reporting Services box, it must be removed - or you will have to run RS under that account (as above). You can't have one SPN registered twice to separate accounts - even though the setspn tool will quite happily allow you to do it without any kind of warning. Finding duplicate SPNs is tricky, information is available from Microsoft.
  4. Setting Trusts in Active Directory
    With the SPNs set, you now need to set up trusts. A trust is an indication that a particular account is allowed to delegate credentials on another account's behalf (still with me?). Again you need domain admin rights to do this. In Active Directory Users and Computers, open the properties for the account which RS is running under. Open the Delegation tab and enable 'Trust this user/computer for delegation to any service (Kerberos)'. Then repeat this for the Reporting Services computer.
  5. Setting Authentication Providers in SharePoint
    SharePoint needs to be told to use Kerberos. In Central Administration, go to Application Management -> Authentication Providers. Change the web application to the Central Administration app (this is important!) then select the default authentication link. Set to "Integrated Windows Authentication" and "Negotiate (Kerberos)". Save everything and then run IISRESET on the SharePoint web front end host server (not the Reporting Services server). If you have other authentication provider schemes, you will probably need to perform the same steps for them.
  6. Setting IIS to use Kerberos Authentication
    Another trip to the command line on the Reporting Services server. Navigate to c:\Inetpub\AdminScripts and run the following command:
    cscript adsutil.vbs get w3svc\NTAuthenticationProviders
    If this returns "NTLM" or is not set, execute the following command:
    cscript adsutil.vbs set w3svc\NTAuthenticationProviders "Negotiate"
    Then run IISRESET.
  7. Setting Reporting Services to Use Kerberos
    Finally, make sure Reporting Services itself is configured to allow Kerberos. Open the rsreportserver.config file and serach for the AuthernticationTypes node. It should have an entry of
    <RSWindowsNegotiate/ >
    or
    <RSWindowsKerberos/ >
    Save the file and run IISRESET.

Hopefully this checklist will help, integrating SharePoint and Reporting Services can be a frustrating task!

4 comments

Including a Variance (or Percentage Growth or Change) between two group members in Reporting Services

Monday, 26. January 2009, 15:12:22

, , , ...

In a Reporting Services tablix (or matrix in Reporting Services 2005), it's common to group on a time period such as Year in order to show annual totals. This gives you an easy comparison of figures, such as in the image on the left. This is a valid and useful visualisation of numbers.

However, it doesn't easily enable users to see the percentage variance between the two members of the Year group. If you need to see the amount by which your figures have changed, you need to have it calculated. This can then be displayed next to the annual totals as in the image on the right.

A simple way might be to have the calculation done in your table's data source and displayed as FIRST(Field) rather than SUM(Field). This will work if your query is at the same grouping as your matrix, but because percentages can't be summed, or averaged, or any other type of aggregation to get values at a subtotal or total level, you can't have a total line or change the grouping.

You are left with using an expression in the report. The formula itself is very simple: 
(([This Year]-[Last Year])/[Last Year]) * 100
The problem is that there is no scope which lets you grab the value of one group member - only for the group as a whole or the entire table.

The solution is to put the values you need into variables which can then be used in expressions in the report itself. Two calculated fields are needed to achieve it. They are not added to the query but as calculated fields of the datasource. The first holds values of the two years, but makes the value of the first year a negative number. This can then be SUMmed in order to give a value difference. Add a calculated field with the name "Difference" and add the following code:
=CDbl(iif(Fields!Year.Value=datepart("yyyy", now())-1, (Fields!Sales.Value * -1), Fields!Sales.Value))
In this example the code is dynamically working out the current and previous year so there is no need to change the formula each year. The result is explicitly converted to a double just to be sure we will always work with a consistent data type. The second calculated field is called "FirstValue" only contains values for the first year:
=CDbl(iif(Fields!Year.Value=datepart("yyyy", now())-1, Fields!Sales.Value, 0))


To display the percentage change inside a tablix, add a new column outside the year group. In the column's value cell, add an expression with the following code:
=Sum(Fields!Difference.Value)/Sum(Fields!FirstValue.Value)
This expression can then be placed in any table cell and will give the correct percentage variance at any subtotal level in the table.

To achieve the same result in Reporting Services 2005, you can't mix tables and matrices in the same container so you will need to add a second table immediately beside the matrix and place the expression in it.

4 comments

No "Reporting Services" Section in Application Management After Installing RS Addin for SharePoint

Wednesday, 14. January 2009, 15:26:28

, , , ...

More pain when trying to configure the Reporting Services Addin for SharePoint 2007 today. In this case it was in a SQL Server 2008 environment, but I am willing to bet the same issue occurs when using the 2005 addin.

The issue is that the addin appears to complete its installation correctly, but once completed there is still no Reporting Services configuration section in the Application Management page of Central Administration.

After trying twice, I had to troubleshoot. To see what the installer is actually doing, you can call it from a command line, as recommended on Microsoft's instructions, by running the command:
rsSharePoint.msi SKIPCA=1
This copies all the required files to the computer without installing the solution to SharePoint. You need to do this on all the SharePoint farm servers which are going to be involved in Reporting Services - at the minimum that will be your Reporting Services server and your SharePoint web front end.

Once the installer finishes on all the computers, navigate to your temp directory (C:\Documents and Settings\<your username>\Local Settings\Temp) on the SharePoint server from a command line. You should see a file named rsCustomAction.exe - this is the executable which deploys and activates the Reporting Services feature to SharePoint. Run it with the command
rsCustomAction.exe /i
and watch as the installation proceeds.

My installation was failing when trying to activate the feature in the root site collection - the Administration site. The error message posted was:
******* user does not have permission to add feature to site collection
Although I was signed on with an account which was a Farm Administrator and a member of the Administrators group on the server.

However, the SharePoint server had been installed and set up by someone else, and when the Admin site collection was created my user was not added as a Site Colleciton Administrator on it. I ensured I was a Site Collection Administrator on all the site collections and ran
rsCustomAction.exe /i
again. The installer proceeded without a problem.

So, the moral of the story: your user must be a site collection administrator on the Administration site collection as well as any other site collections in order to install the add in.

3 comments