Friday, June 22, 2012

Installing and Configuration of SSRS 2008 with SharePoint 2010


Installing SQL Server 2008 R2
At a minimum, install the database engine and reporting services. By installing SQL Server first you have the option of specifying the database engine that SharePoint will use. Alternatively you can install SharePoint and allow it to install its own instance of SQL Server Express (now called SQL Server Embedded Edition). There are two steps I want to highlight in the SQL Server 2008 R2 installation: Setup Role and Reporting Services Configuration.
In the Setup Role step, choose SQL Server Feature Installation as shown below:

In the Reporting Services Configuration step, choose Install the SharePoint Integrated mode default configuration. This allows the install to do the majority of the work, limiting the steps that you will need to perform to get Reporting Services integrated with SharePoint.

Installing SharePoint 2010
In order to successfully install SharePoint, there are a number of prerequisites that must be installed. When you launch the SharePoint 2010 setup, make sure to first click Install software prerequisites. After clicking Install software prerequisites.

Note that this step will automatically install anything that you need from the list of required products and updates. In order to run Reporting Services in SharePoint Integrated mode, you need the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in; this will be done for you.

The Standalone option is perfect for getting going quickly; it installs the software with all default settings. While this approach requires the least amount of effort on your part, it does install an instance of the SQL Server Embedded Edition that can only be used by SharePoint; i.e. you can't leverage this SQL Server instance for the Reporting Services database. In addition you truly get a standalone instance of SharePoint; you cannot add any other SharePoint servers. Despite these limitations, I'm going to proceed with the Standalone option. 

When the SharePoint 2010 Foundation installation is completed, you will be prompted to run the SharePoint Products Configuration Wizard. You need to do that to in order to complete the configuration steps required to have a working installation of SharePoint 2010 Foundation. When the SharePoint Products Configuration Wizard is finished, your browser will open to the SharePoint Team site that has been created for you.

Configuring Reporting Services SharePoint Integration
At this point we have Reporting Services 2008 R2 and SharePoint 2010 Foundation installed. The next step is to configure Reporting Services SharePoint integration using SharePoint Central Administration. Launch SharePoint Central Administration from the Start menu (it should be pinned to the Start menu; if not then select it from All Programs, Microsoft SharePoint 2010 Products group). Click General Application Settings and then Click Reporting Services Integration as shown below:



Note that the Reporting Services options shown above will only appear after the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in is installed. I installed the add-in by running the Install software prerequisites option before installing SharePoint.
There are just a couple of things that you need to enter to complete the integration as shown below:

The following are the main points about the integration settings:
  • You can get the Report Server Web Service URL from the Reporting Services Configuration Manager (in the Microsoft SQL Server 2008 R2, Configuration Tools program group). Click on Web Service URL in the menu on the left.
  • When you have everything running on a single server as I do in this example, choose Trusted Account for Authentication Mode.
  • For Credentials you do need an account that is in the local Administrators group. With everything on a single server it can be a local account, but I'm using a domain account.
  • Choose Activate feature in all existing site collections.
After entering the required information and clicking OK, you should see the Reporting Services Integration Summary as shown below:

At this point Reporting Services is configured in SharePoint Integration mode.


Sending Requests to a Report Server
All requests for a report server item or operation must be a valid authenticated request. The authentication provider you are using determines how that request is processed.

Windows Integrated Security Using Kerberos
If the SharePoint Web application is configured for Windows Authentication using Kerberos, the connection from the SharePoint Web application to the report server can use the impersonated or delegated credentials of the current Windows user. By using Windows integrated security with Kerberos and identity delegation, you can eliminate the classic "double-hop" issue wherein Windows credentials expire after a single connection. It can also expand the set of options that are available to you when you configure data source connections for reports and models. The following diagram shows the connections when a report server is configured for SharePoint integration, and the SharePoint Web application uses Windows Authentication with Kerberos and identity delegation.



Connection 1
A user accesses a SharePoint site under the user token created when the user logged on to the network. It contains the user identity and group membership. The SharePoint Web application authenticates the user. The user requests a report server item or operation.
Connection 2
The SharePoint Web application sends the token and the request to the report server. The connection request is sent under the delegated Windows identity of the user. The report server authenticates the user to see whether the user is allowed to access the report server.
Connection 3
If authentication is successful, the report server will use the user account of the Reporting Services instance to make a connection to the SharePoint content databases to verify that the user is authorized to access the item or operation. If authorization is successful, the report server services the request.
Connection 4
If the user is viewing a report, the report server can delegate the Windows identity of the user during report processing to retrieve data from external data sources. This means that when you set data source properties on a report, you can select the Windows integrated security option for the data source connection. For more information, see Specifying Credential and Connection Information for Report Data Sources (SSRS) and How to: Create and Manage Shared Data Sources (Reporting Services in SharePoint Integrated Mode) in SQL Server Books Online.

Windows or Forms Authentication and Trusted Accounts
If the SharePoint Web application is configured for Forms authentication or for Windows Authentication using NTLM, the connection to the report server is sent across the network under a predefined trusted account that has permission to impersonate a SharePoint user on the report server. The following diagram shows the connections when trusted accounts and SharePoint user identities are used.

Connection 1
A user logs on to a SharePoint site. The SharePoint Web application authenticates the user. The SharePoint Web application translates the user identity to a SharePoint user identity (SPUser). A new user token is created for that user in the context of SPUser. It contains the user identity and group membership. The user requests a report server item or operation.
Connection 2
The SharePoint Web application connects to the report server using a trusted account, which is the process identity of the SharePoint Web application. The SharePoint Web application then impersonates the SharePoint user identity in the request for an item or an operation.
The report server authenticates that the connection request is from a trusted account by comparing it to account information that the report server retrieved from the SharePoint configuration databases when the report server started. On a report server, the trusted account is a Windows user with permission to impersonate the SharePoint Web application. It is also used to impersonate the SPUser, but it is not allowed access to report server items and operations.
Connection 3
If authentication is successful, the report server will use the user account of the Reporting Services instance to make a connection to the SharePoint content databases to verify that SPUser is authorized to access the item or operation. If authorization is successful, the report server services the request.
Connection 4
If the user is viewing a report, the report server cannot use the SPUser to retrieve data from external data sources due to the “double-hop” issue. This means that when you set data source properties on a report, you cannot select the Windows integrated security option for the data source connection. You can, however, configure the report to use other connection options, such as stored credentials or prompted credentials. For more information, see Specifying Credential and Connection Information for Report Data Sources (SSRS) and How to: Create and Manage Shared Data Sources (Reporting Services in SharePoint Integrated Mode) in SQL Server Books Online.