Since SQL Server 2005 SP2, you can configure SQL Server Reporting Services in SharePoint Integrated mode. In this mode you can upload report files (rdl files) and shared data connection files to SharePoint document libraries and work with them from there.
To get to a report, a user just browses to a SharePoint site and selects a report from a document library.
Depending on your requirements, you can install Reporting Services on the same server as your SharePoint server or on a separate server. But if you install Reporting Services on a separate server, you will have to install SharePoint anyway because Reporting Services needs the SharePoint object model and needs to be part of the farm (see this document for more info). If you install Reporting Services on an existing SharePoint server you are deploying side-by-side and should check this document for more info.
To deploy Reporting Services on a SharePoint server side-by-side, just install Reporting Services and do not create a basic configuration. The basic configuration deploys Reporting Services in native mode. After installation, start the Reporting Services Configuration program to configure the server for integrated mode. You will also need the SQL Server Reporting Services Add-in for SharePoint Technologies. Installation is pretty straightforward and is fully explained in the documentation.
When you run a report, you frequently need to access data on a separate server. If you design a report with a datasource that queries a remote SQL Server, you will have to make sure that your credentials are passed to that server correctly. You basically have two options:
- Connect to SharePoint using basic authentication - or -
- Connect to SharePoint using Kerberos
You cannot authenticate with NTLM because that protocol does not support double hop.
Basic authentication is the simplest option. You can configure basic authentication with SharePoint Central Administration. When you do so, IIS will be configured appropriately. When a user connects to SharePoint using basic authentication, the password is actually sent to IIS. A connection can be made to a third server with the provided credentials using either NTLM or Kerberos.
The Kerberos option takes more work to implement because it requires service principal names in Active Directory. You configure Kerberos authentication from SharePoint Central Administration and that will also configure IIS for Kerberos. Basically, the NTAuthenticationProviders setting will be set to "Negotiate, NTLM". For more info, see this article.
What are the SPNs you need to set? Well, suppose you have the following scenario where UserX connects to SharePoint (with Reporting Services) and a report needs data from a separate SQL Server:
UserX ----------> SRVSP+RS --------> SRVSQL
UserX will type a name to connect to SharePoint, for example: http://sp.domain.com. For Kerberos authentication to succeed, a Kerberos ticket will need to be passed to IIS. Internet Explorer will request a Kerberos ticket for a service and that service is identified by a service principal name. If you would run a network trace, you would see that Internet Explorer request an SPN in the form of http/<servername typed by user>. So in this case: http/sp.domain.com.
Note: if the website is configured on a different port than 80, do not specify the port on the SPN like http/sp.domain.com:8080. Internet Explorer always uses the FQDN only no matter the port number.
Once the ticket has been acquired, it needs to be passed to the IIS process that hosts the SharePoint web application. In IIS speak, that process is an application pool. An application pool should be configured with a process identity and that should be a domain user account. The SPN of http/sp.domain.local needs to be added to that domain user account. Suppose the domain user account is domain\apppool you would need to use the following command to set the SPN:
setspn -a http/sp.domain.com domain\apppool
The setspn.exe tool is part of the Windows Support Tools on the Windows Server cd. To check that the SPN was set correctly, use the following command:
setspn -l domain\apppool.
Setting the SPN is not enough. For UserX's credentials to be passed from SRVSP+RS to SRVSQL, the domain\apppool account needs to be trusted for delegation. You do this with the Active Directory Users and Computers (ADUC) MMC snap-in. Where you find this option in the GUI depends on the Active Directory functional level. If it is Windows 2000, the option is in the Account tab. If it is WIndows 2003, the option is in a separate Delegation tab. That tab is only visible for accounts that have SPNs registered.
Once the account is trusted for delegation, you need to make sure that the account has enough user rights on the server (SRVSP+RS) to perform the actual delegation. The user right Impersonate a client after authentication is the minimum required. When the application pool account (domain\apppool) is part of IIS_WPG everything should be fine.
One last step is to make sure that the user account that runs SQL Server on SRVSQL also has an SPN. The SPN needs to be MSSQLSrv/fqdn:1433. In this example: MSSQLSvc/srvsql.domain.com:1433.
Besides the above requirements at the server and domain level, there are some requirements at the client level. The client needs to be able to talk to a domain controller on port 88/UDP (Kerberos) and the website must be in the Intranet or Trusted Sites zone.
