SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies

The purpose of this blog post is to provide a step by step walk through for setting up Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies. This add-in allows for Reports to run within the context of SharePoint.  The Reporting Services Add-in provides the following functionality:

• A Report Viewer Web Part, which provides report viewing capability, export to other rendering formats, page navigation, search, print, and zoom.
• Web application pages so that you can create subscriptions and schedules, set model item security, and manage reports, models, and data sources.
• Support for standard Windows SharePoint Services features including document management, collaboration, security, and deployment with report server content types. You can use alerts, versioning (check in/out), and Filter Web Parts with reports. You can add the Report Viewer Web Part to any page or dashboard on a SharePoint site and customize its appearance. You can use SharePoint permission levels and roles to control access to report server content. You can also use SharePoint forms authentication to support access over Internet connections.

  Note The add-in is for reporting on SQL data not SharePoint data.

  This walk through makes a few assumptions about your setup environment.
• Active Directory 2003 domain running in native mode
• The SharePoint server is on a separate box from the Reporting server
• The reporting server and the SQL server are on the same box
• The SPAdmin account is the SharePoint administration account and is local administrator on the SharePoint server and the SQL server
• SPSQL account runs the sql services and reporting services
• SPSites account runs the application pool for the SharePoint web site.
• Your SharePoint Server is set to use Kerberos authentication

SetSPN

SetSPN (set spin) is used to configure Active Directory user and computer accounts for Kerberos delegations. Kerberos delegation is necessary if you are running reporting services on a separate server than your SharePoint server. If user A hits a website on computer B, computer B can forward the authentication to computer C. There are two benefits to configuring Kerberos; one, Kerberos is a more secure protocol than NTLM, two, Kerberos is necessary to correctly configure Reporting Services. 

1. Login to domain controller
2. Download the setspn.exe from http://www.microsoft.com/downloads/details.aspx?familyid=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&displaylang=en
3. Run setspn_setup.exe and install tool, click next
4. Agree to the EULA
5. Accept the default path and click install now
6. Click start -> Run and enter cmd
7. From the command prompt navigate to C:\Program Files\Resource Kit. You will need to use the setspn for the following three accounts. The SharePoint Service Account (SPAdmin), the Default site application pool account (SPSites) and the SQL Service account (SPSQL). Issue the following commands:
   1. setspn -A http/llqawss01 qalbapad\spadmin
   2. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spadmin
   3. setspn -A http/llqawss01 qalbapad\spsites
   4. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spsites
   5. setspn -A http/llqawss01 qalbapad\spsql
   6. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spsql
   7. setspn -A http/llqasql01 qalbapad\spadmin
   8. setspn -A http/llqasql01.qalbapad.qalocal qalbapad\spadmin
   9. setspn -A http/FQDN of server (www.ll.com) qalbapad\spsites
   10. setspn -A http/FQDN of server (www.ll.com) qalbapad\spadmin
   11. setspn -A http/FQDN of server (www.ll.com) qalbapad\spql
       You notice that you will need to setspn on each name the computer may use, the netbios name, the internal FQDN, if this machine uses another FQDN you will need to add this as well.(To be honest this is probably over kill but this will cover all your bases)
8. On the domain controller open active directory users and computers, We need to trust the computer accounts and Service accounts for delegation
   1. Find the SQL server in Active Directory Users and Computers (ADUC) right Click and go to properties and click the Delegation tab, then select Trust this computer for delegation to any service (Kerberos only)
   2. Find the WSS server in ADUC right Click and go to properties and click the Delegation tab then select Trust this computer for dlegation to any service (Kerberos only)
   3. Find the SharePoint Service account in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)
   4. Find the SharePoint Site (SPSites) account in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)
   5. Find the SQL Server Service (SPSQL) in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)

On the SQL/Reporting Server

1. Make SPAdmin local administrator of the SQL server computer
2. Install Microsoft .NET Framework 2.0
3. Microsoft .NET Framework 3.0
4. Download the SharePoint install from Microsoft
5. Execute SharePoint.exe
6. Accept the licensing agreement, click continue
7. Choose the advanced installation option
8. For Server Type choose Web Front-End (WFE), click install now
9. Click close to run the SharePoint Technology Configuration wizard
10. Select Yes, I want to connect to an existing server farm, click next
11. Enter the name of the database server and then click Retrieve Database Names. This will bring back the SharePoint Configuration database name.
12. In the Specify Database Access account enter the SharePoint Service account (SPAdmin) and password information, click next
13. Click Next
14. Click Finish
15. Download the reporting services add-in http://www.microsoft.com/downloads/details.aspx?familyid=1E53F882-0C16-4847-B331-132274AE8C84&displaylang=en

On the SharePoint Server

Install the SharePoint add in for Reporting services.

1. SharePointRS.msi, click next
2. Accept the Licensing agreement, click next
3. Click next
4. Click Install
5. Click Finish when complete
6. Login to the SQL Server computer, click start -> All Programs -> Microsoft SQL Server 2005 -> Configurations Tools -> Reporting Services Configuration
7. Connect to the SQL Server
8. Click on Database Setup
9. Click on Change to change the server mode to SharePoint
10. Click yes to create a new Reporting Services database
11. Leave the defaults and enter a name for the new SharePoint integrated Reporting Services database, click OK.
12. Click Apply
13. Leave the defaults and click OK
14. Now we need to configure the Reporting Services application pool to run as SPAdmin. Open IIS Manager and navigate to the Application Pool -> Report Server
15. Right click on Report Server and click properties, click the Identity tab
16. Configure the identity to be SPAdmin. This will allow the reporting server to access the SharePoint server for the SharePoint integration to work properly.
17. In IIS manager under the Web Sites folder right click the default site (This is where reporting services web is located) and click properties
18. In the Web Sites tab change the port to 8080, Click OK to apply
19. Return to the Reporting Server configuration and refresh. In the Web Service Identity you will need to click apply to complete the change made to the application pool
20. Click on SharePoint Integration
21. Follow link to SharePoint Central Administration site
22. From the Application tab click on Manage integration settings
23. Enter the url for the report server plus the virtual directory for the report server. Most likely this will be http://machinename:port#/reportserver, Click OK
24. Click on Grant database access, this will default to the local server. Change to the reporting server. Click OK
25. You will be prompted to enter credentials for accessing the report server. Enter the SQL account (SPSQL), click ok
26. Click on Set Server Defaults
27. In Reporting Services Server Defaults accept the defaults and click OK