SSRS 2008 R2 and SharePoint 2010 Integration

If your organization currently uses or plans to use both SharePoint Server 2010 and SQL Server Reporting Services (SSRS) 2008 R2 as separate products, you should consider using SSRS in SharePoint integrated mode. You’ll have only one security model to manage and, even better, business users will have only one environment in which to create, find, and share information, whether that information is in the form of reports, lists, documents, or other content types. Furthermore, you can manage reports using the same content management, workflow, and versioning features that you use for other SharePoint content.

SQL Server 2008 R2 adds many new features to SSRS, and several of the new features tighten integration with SharePoint 2010. One benefit of the enhanced integration is that it’s easier to install and configure the SharePoint integrated mode of SSRS than it was in the previous versions of the two products. You’ll also find additional benefits of the new integration features throughout the reporting life cycle as you author, manage, and access reports. Let’s go exploring!

Installation

There are many different ways to set up SharePoint for integration with SSRS. You can install all required software on a single server to create a development environment, or you can distribute components across multiple production servers for a SharePoint farm. Regardless of the specific topology, the overall architecture for an integrated environment remains the same, as Figure 1 shows.

You start by installing a database instance to host the report server databases. You can use the same database instance to host the SharePoint configuration and content databases if you’d like, but you’re not required to keep all the databases in the same location. If you decide to place all the databases in the same database instance, the Reporting Services service account, which you set in the Reporting Services Configuration Manager, must be a domain account.

The SharePoint databases will contain information used by the report server. The configuration database stores the URL for the Reporting Services Web service, information about the Report Server service account, and the authentication mode. It also includes configuration information about the server defaults, such as report history settings, processing timeouts, and logging. The content databases store the report server content types (e.g., report definition files and data sources), properties related to report server content, and security permissions.

Even when SSRS runs in SharePoint integrated mode, the report server databases are still necessary. The ReportServerTempDB database continues to store session data and cached instances, and the ReportServer database stores information related to snapshots, report history, subscriptions, and schedules. Furthermore, the ReportServer database also maintains copies of report server items that are stored in the SharePoint content database to facilitate faster processing during report execution.

At the time that you install the database instance, you can also add SSRS to the feature selection if you plan to run the two components on the same server. Wherever you choose to install SSRS, you can install SSRS in SharePoint integrated mode immediately, or in files-only mode if you prefer to manually complete the integration steps later. When SSRS runs in SharePoint integrated mode, it not only uses a different database structure but also uses custom security and delivery extensions.

During the installation of SharePoint, there are several prerequisites that are installed as part of the standard process. One of these prerequisites is the Reporting Services Add-in for SharePoint. Now that the add-in is a prerequisite, you no longer have to worry about finding the download site for the add-in file, making sure you have the right version for your combination of SharePoint and SSRS, and then installing the add-in separately as earlier versions require. If you perform a non-standard installation without installing the prerequisites automatically, you can still obtain the add-in by downloading it from Microsoft and installing it separately on each SharePoint Web front-end (WFE).

The add-in installs a new endpoint to SharePoint as a proxy for the Reporting Services Web service. It also installs the Report Viewer Web Part that allows you to view your reports in SharePoint. Finally, the add-in provides the application pages that are necessary for managing report properties, configuring data sources, setting up subscriptions, and everything else that Report Manager handles in a native-mode deployment.

If your report server is on a separate computer from the SharePoint server, your final step will be to install the SharePoint WFE on the report server and join the WFE to the SharePoint farm. Be sure to use the same version of SharePoint on both servers. Adding the WFE components to the report server enables it to communicate with the SharePoint configuration and content databases.

SSRS Configuration

If you installed SSRS in SharePoint integrated mode, no further configuration is necessary. However, if you installed it in files-only mode, you need to open the Reporting Services Configuration Manager located in the Configuration Tools folder in the Microsoft SQL Server 2008 R2 program group. Using this tool, you must specify an account to run the Reporting Services service account and a URL for the Reporting Services Web service. Then, create the report server database in SharePoint integrated mode.

SharePoint Configuration

After all the products are installed and the SSRS configuration is completed, you’re ready to begin the SharePoint configuration process. For SharePoint, use SharePoint Central Administration where you’ll find a set of links for SSRS on the General Application Settings page. At minimum, you must open the Reporting Services Integration page, which is shown in Figure 2 to provide the report server URL, the authentication mode for impersonated connections to the report server, and the credentials of a member of the Administrators group on the report server. This page consolidates the settings required to integrate earlier versions of SSRS with Microsoft Office SharePoint Server (MOSS) 2007, and thereby streamlines the configuration process.

The choice of Windows Authentication or Trusted Account for the authentication mode affects the way that the user identity is communicated by SharePoint to the report server and how data sources must be configured. If you choose Windows Authentication and Kerberos is enabled on your network or SharePoint and SSRS are running on the same server, you can use Windows integrated security for your data sources. If you select Trusted Account instead, the Windows user identity can’t be shared, and you must configure data sources to use stored credentials.

One new setting on the Reporting Services Integration page is the Activate the Reporting Services Feature option. The default selection activates the Reporting Services feature in all existing site collections, but you can choose instead to activate it only in specified site collections.

At this point, the integration configuration process is complete, but you might want to review the default settings on the Set Server Defaults page accessible by a link in the Reporting Services section on the General Application Settings page in SharePoint Central Administration. Here you’ll find the default settings for the number of report history snapshots to store for each report, report processing timeouts, and report processing logs. You can also specify whether to enable Windows integrated security, click-through reports for Report Builder 1.0 reports, the client-side print control download, and the Report Builder download. This last download will launch Report Builder 3.0 by default, but you can change the default URL for the download to launch Report Builder 1.0 instead if your users prefer to create their ad hoc reports by using semantic models.

Site Preparation

Before you can start deploying reports to SharePoint, you’ll need to complete a few more steps. You must create a Web application, a site collection, and add the SSRS content types to at least one document library in your new site.

To create a Web application in SharePoint Central Administration, go to Application Management and click the Manage web applications link. On the ribbon, click New. The default settings should suffice, but the following are three key settings on this page to note if you plan to use Classic Mode Authentication:

Authentication Provider. The authentication provider defaults to NTLM, which means you won’t be able to use Windows integrated security with your data sources. You can switch the authentication provider to Negotiate (Kerberos) if you have Kerberos enabled on your network and you have configured SSRS integration to use Windows Authentication.

Anonymous Access. The Allow Anonymous setting is set to No, which is required for any Web application you plan to use with SSRS.

Application Pool Identity. The best practice for an application pool identity in SharePoint is to use a domain user account. If you decide instead to use Network Service and the report server is located on a separate server from SharePoint, you must configure SSRS integration to use the Trusted Account authentication mode.

As an alternative to Windows Authentication for your Web application, you can use Claims-Based Authentication. When SharePoint communicates with the report server, it generates a SharePoint user token, which the report server uses to generate the proper context for the user and to render the request appropriately. However, if you configure SSRS to use the Trusted Account authentication mode and the Web application uses Claims-Based Authentication, you must configure data sources to use stored credentials. Otherwise, Windows integrated security will fail.

Next, go to Application Management and click the Create site collections link. If necessary, change the Web application to the one you created in the previous step. After naming the site collection and setting the URL, select a template. A good choice here is the Business Intelligence Center site template on the Enterprise tab. You must also assign a site administrator on this page.

Finally, you might need to manually add the SSRS content types to the document libraries that you want to use for storing reports in your SharePoint site. Open the applicable document library, click the Library tab on the ribbon, and then click the Library Settings button. Click the Advanced settings link and select Yes for the option Allow management of content types. Click OK, and then click the Add from existing site content types link. On the subsequent page, you can locate the content types, such as Report Builder Report or Report Data Source. Adding these content types allows users with the appropriate permissions to develop new reports using Report Builder or to add new data sources without having to create and deploy them in Business Intelligence Development Studio (BIDS).

Authoring Features

Now let’s review the specific enhancements to the reporting life cycle that SharePoint integration with SSRS provides, beginning with the authoring stage of the life cycle. In this area, the list of enhancements is short because there’s only one new feature relevant to SharePoint, and it doesn’t even require you to set up SSRS in SharePoint integrated mode to use it. This new feature lets you use a SharePoint list as a data source. In previous versions of SSRS, you could access data in a SharePoint list only by using custom code.

To use this feature, create a data source using the Microsoft SharePoint List connection type, and provide credentials that are authorized to access the list. The connection string is the URL for the site or subsite to which the list belongs, such as SharePointServerName/SiteName or SharePointServerName/SiteName/SubsiteName.

When you create a dataset using this data source, a special query designer is available for you to browse the available lists on the specified site. You choose fields from the list to include in your report. The dataset can include fields from one list only. Figure 3 shows the query designer for a SharePoint status list. Note that you can also apply filters and create parameters based on the filters in the query designer.

The process to deploy the reports to SharePoint hasn’t changed, but if you’re not familiar with the process, Figure 4 shows examples of the project property values for each target folder and the report server URL. Each target folder represents a document library or data connections library in SharePoint, and the target server URL is the SharePoint site containing these libraries.

Management Features

Moving on to the management stage of the reporting life cycle, the following new features improve the SharePoint integration experience for SSRS: alternate access mappings, RS utility/scripting, diagnostic logging, and the file sync feature.

Alternate access mappings. Many people use alternate access mappings (AAM) in SharePoint as a way to associate web requests with a specific web application and SharePoint site using a URL that’s different from the assigned URL. As an example, you might have a public URL, such as www.adventureworks.com that you want to associate with an internal URL, such as sharepoint.adventureworks.com. AAM, in the latest release of SharePoint, now supports SSRS, which means external users and internal users can use separate URLs to access the same reports. Each web application can have up to five different URLs—one for each SharePoint zone: default, Internet, intranet, extranet, and custom. Each URL can use a different authentication provider, which means you can easily support Windows Authentication for internal users and Forms Authentication for external users.

RS utility/scripting. A major shortcoming with SharePoint integration in the past was the lack of support for the RS.EXE utility. Many report server administrators use this utility to deploy reports to a production server and for other routine administrative tasks, such as setting security or managing subscriptions. There’s nothing different about the way this utility works with a SharePoint integrated mode report server. All arguments for the utility remain the same.

Diagnostic logging. Administrators spend a lot of time poring through logs for a variety of reasons. The integration of SSRS diagnostic information into the SharePoint Unified Logging Service (ULS) now simplifies the troubleshooting process for SharePoint administrators. Even with the availability of ULS, all other SSRS logging and performance counters are still useful tools for troubleshooting any problems that you might encounter when running SSRS in SharePoint integrated mode.

To use this feature, click Configure diagnostic logging on the Monitoring page in SharePoint Central Administration. Expand the SQL Server Reporting Services section to review the available categories, as shown in Figure 5, and then select the type of information that you want to include in the ULS. At the bottom of the list, you can choose the level of detail to include in the ULS by specifying the least critical event to report to the trace log. The option to report the least critical event to the event log isn’t supported by SSRS.

File Sync Feature. When you deploy report items to document libraries using the RS.EXE utility or the deploy command in BIDS, a synchronization process runs on the report server to ensure both the SharePoint content database and the report server database have the same copy of each newly added report item. This synchronization process also runs whenever someone updates or retrieves a report item. However, if you manually uploaded a report server item to SharePoint, the synchronization process didn’t run automatically in the previous versions of SSRS. Now, you can activate the Report Server File Sync Feature at the site level, not the site collection level. This feature is disabled by default.

Report Access Features

Apart from the UI enhancements that SharePoint 2010 provides, you won’t see many new enhancements for the report access stage of the integrated reporting life cycle. The Report Viewer toolbar now includes the Export to Data Feed button, which allows you to generate an ATOMSVC document suitable for importing data into Microsoft PowerPivot for Excel, just as you can do in the native mode Report Viewer. The rendering process is engineered to perform faster than it did in MOSS, but you might not notice a difference if query processing takes longer than rendering.

One other change to SharePoint made possible by the Reporting Services Add-in is the ability to render reports in local mode without a dependency on a report server. You can use local mode rendering to view reports from Access Services, or you can view reports using a SharePoint List data source. Local mode allows you to export reports to the same formats available in SSRS, but you can’t configure subscriptions or set up snapshots for these reports.

Get Integrated

Reading about the improved integration of SSRS 2008 R2 with SharePoint 2010 is a good first step. Now see for yourself what’s new and improved by setting up a development server with SharePoint and SSRS, deploying some reports, and exploring the features that I’ve described in this article. The next generation of SharePoint and SSRS integration is a big step forward!

For more information, see the Learning Path.

Learning Path

For more information about using SSRS and SharePoint, see these Microsoft resources: