Integrating reports in dashboards is hot. If you’re organization isn’t analyzing all the data it is gathering to answer questions about your business, you’re going to fall behind. If you have SharePoint , you can integrate Reporting Server and Analysis Server with it (I am not going to tell how to work with SSRS, there is someone who is much better at that).

This is my Server environment:

AD_SRV     : Active Directory Server
SP_SRV      : Sharepoint Server
SQL_SRV   : SQL Server (Sharepoint Configuration Database)
REP_SRV  : Reporting Server (SSRS)

As you can see we will run into the double hop problem. In short: the credentials which are passed into IIS (Sharepoint) cannot be passed to another machine (SSRS) for authentication. You can solve this by using the Kerberos Protocol. This is what I am gonna try to explain

What do you need:

• Domain Service Account (the account of the Sharepoint Web App Application Pool). Let’s say: srvAccount
• Microsoft SQL Server 2008 Reporting Services add-in for Microsoft Sharepoint Technologies (download here, see to it that you take the correct version i.e. x86 or x64)

First of all install the Sharepoint Object Model on the SSRS Server, if you don’t you will receive an error like:
The configuration paramter SharePointIntegrated is set to true but Share Point Object Model cannot be loaded.
Use the Service Account srvAccount
Also install .Net version 3.5 and the Windows Installer update (the setup will do that)

Then make sure that the srvAccount can create SPN’s dynamically, see step 3 in this article from Microsoft.

I installed SSRS to run under the service account srvAccount.
Then to configure the reporting server (start the Reporting Services Configuration Manager):

• Create a new database (I installed it on srvAccount).
  1. Choose “Integrated Security”
  2. Choose “Sharepoint Integrated Mode”
  3. At credentiails, choose "Service Credentials”
• Create your Web-Service URL and your Report Manager URL (write these down somewhere, you will need them again)

Then install the Reporting Services Add-in on SP_SRV.

Next step is to configure the add-in:

• start Sharepoint Central Administration, and click [Application Management]. There should be a section called “Reporting Services”, if it is not available, you have to enable it (Site Actions > Site Settings > Site Collection Features. Search for Report Server Integration Feature and click [Activate]). It’s possible that the feature isn’t there, then perform a installation in Files-Only Mode (see: http://technet.microsoft.com/en-us/library/aa905871.aspx).
• Below section “Reporting Services”, click on [Manage Integration Settings]
• Fill in the Web Service URL, you wrote down earlier in the field besides “Report Server Web Service URL”
• Choose “Windows Authentication”
• Click [OK]
• Next click [Grant Database Access] (back at the section Reporting Services)
• In the field “Server Name” fill in the name of the database server (i.e. REP_SRV) on which your reporting database runs
• Click OK and  and fill in the username and password of your service account (srvAccount)

So.. now the Service Principal Names. Restart your SQL Server Service on SQL_SRV, so that it will register it’s SPN’s dynamically.
The following SPN’s we’re registered manually by me:

• setspn –a HTTP/<FQDN of REP_SRV> <domain>\srvAccount
• setspn –a HTTP/<NETBIOS Name of REP_SRV> <domain>\srvAccount

As you’ve done the SPN’s, you’re able to trust the srvAccount for delegation. In Active Directory, enable your service account to be trusted for delegation. Do the same for all your servers.

Now enable you’re SharePoint web application for Kerberos:

• Open Central Administration
• Navigation to Application Management > Authentication Providers
• Choose the web application you wish to configure from the drop-down in the top right corner (this includes the Central Administration web application)
• Click on ‘Default’
• Set the authentication to Negotiate (Kerberos)
• IISRESET

Last step: on your reporting server open rsreportserver.config (located in C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer”
Find the tag:

<AuthenticationTypes>
            <RSWindowsNTLM/>
</AuthenticationTypes?

Change RSWindowsNTLM to RSWindowsNegotiate.

So, I hope I forgot nothing, happy reporting

Note: You can secure your kerberos environment some more, I showed you how to trust a user for delegation. I went for the option “Trust this user for delegation to any service (Kerberos only)”, you can also choose to go for “Trust this user for delegation to specified services only”. With this option you will dedicate some services allowed for the user (like http, cifs, etc….).

Some Websites which could be useful:
Tool for helping to troubleshoot Kerberos
Reza Alirezaei’s blog
Steve Caravaial’s blog
Winsmarts.com