SharePoint and SQL Server Reporting Services can be integrated together to provide a one-stop shop for users to explore all content for a site. Deploying reports within your SharePoint site can simplify deployment and maintenance of the solution while providing for a consistent look and feel for the user. This article walks the Database Administrator through the integration process.
SharePoint integration for Reporting Services has two modes: local and connected. Local mode can be used when all you need to do is serve up reports from within SharePoint and do not need or have a separate Report Server. If you have a separate Report Server or need the additional functionality garnered by using one, connected mode will work in this situation. Connected mode allows your Reporting Server to handle scheduling, snapshots, and report processing. This article covers configuration for connected mode.
To install Reporting Services in connected mode with SharePoint, SharePoint must be installed as a SharePoint farm. Additionally, the same edition of SharePoint must be installed on the Reporting Server. This is because it will need to be joined to the SharePoint farm. Also, note that since SharePoint comes in only a 64-bit install, Reporting Services will also need to be installed on a 64-bit OS.
On the database side, both Reporting Services and SharePoint require a SQL Server database. However, Reporting Services requires a full SQL Server edition whereas SharePoint needs only the SQL Server Embedded Edition. SharePoint and Reporting Services can share the same database, but it must be a full SQL Server edition. The workgroup and express editions will not be sufficient. Choose between Enterprise, Standard, and Developer editions.
The Reporting Services Add-in for SharePoint Products will need to be added. If you are using SQL Server 2008 R2 for Reporting Services, the database engine for SharePoint will need to be SQL Server 2008 R2 as well.
An existing installation of a Reporting Services database not set for connected mode cannot be configured for SharePoint integration. Instead, the option to run Reporting Services in SharePoint connected mode must be chosen during the Reporting Services install. Or alternatively, for an existing Report Server, you can use the Reporting Services Configuration tool to add an integrated reporting services database in integrated mode.
It’s important to note that all reporting services databases associated with a Report Server or scale-out must be all integrated or all not integrated. They cannot be mixed and they can’t be reconfigured. This is because SharePoint becomes the repository for the reports, data sources, and models when in integrated model. The Reporting Services database is then used to store the schedules, snapshots, history, and subscriptions.
Once Reporting Services is installed, as I mentioned above, the same SharePoint product that is running in the SharePoint farm must now be installed on the Reporting Server. This installs the Web Front End (WFE) for the Reporting Server.
To install the Web Front End, begin the SharePoint product install and choose Server Farm for the Installation Type. Choose Complete for the Server Type. You will need to be a SharePoint Farm Administrator to add the SharePoint products. Once the installation is complete, configuration will need to be performed on both servers. Note that a single server install doesn’t require the WFE.
The Web Front End now can be joined to the SharePoint farm. You will need the SharePoint farm Passphrase to complete this step.
You will utilize the Microsoft SharePoint 2010 Products Configuration Wizard to add the WFE. Select Connect to an existing server farm. You will be asked for the configuration database’s particulars and then for the passphrase.
For the SharePoint product, if you haven’t already installed the Reporting Services add-in, you will need to do that. Once installed, you can use SharePoint Central Administration to configure it. In General Application Settings under Reporting Services, choose Reporting Services Integration. This screen allows you to specify the URL of the Report Server Web Service to be integrated, the authentication mode, and whether it should be activated for all or select site collections.
The authentication modes available here are Trusted Account and Windows Authentication. With Trusted Account, the Web Front End passes the logged in user’s credentials through to the Report Server for further authentication. Windows Authentication is used for Kerberos enabled environments or when the Report Server and SharePoint Server are on the same box.
Reporting Services always requires a valid security context, so there can be a problem here if you have a site that allows for anonymous access and you would like to provide Reporting Services content to the users of that site. Out-of-the-box, anonymous access to Reporting Services is denied. However, each anonymous request could be wrapped by a valid security context to allow Reporting Services to authenticate that account.
On the Report Server, you will need to make sure to set up an Execution Account. This account is used by Reporting Services to connect to external resources and for data sources that don’t require credentials. It can be set up using the Report Server Configuration Manager. Make sure to use an account with the bare minimum permissions needed and to be careful not to use a user account or the Reporting Services service account.
Deploying Reports to SharePoint
Now that everything is configured, it’s time to get some reports up on the SharePoint site. Let’s assume you already have a SQL Server Business Intelligence Development Studio (BIDS) project containing several reports, data sources, and report parts you wish to deploy to your SharePoint site.
To deploy to the SharePoint site, you will need to right click on your solution in BIDS and choose Properties. In the properties window you can specify the relative URL for data sets, data sources, reports, and report parts in addition to the site URL and option for overwriting data sets and data sources. A set of deployment options might look like the following.
OverwriteDataSets No OverwriteDataSources No TargetDataSetFolder http://MySharePoint/sites/Reports/Documents TargetDataSourceFolder http://MySharePoint/sites/Reports/Data%20Connections TargetReportFolder http://MySharePoint/sites/Reports/Documents TargetReportPartFolder http://MySharePoint/sites/Reports/Documents TargetServerURL http://MySharePoint/sites/Reports/ TargetServerVersion SQL Server 2008 R2
Once that is all set up, right click your solution and select Deploy. Your Reporting Services content is now up on your SharePoint site.
Integrating SQL Server Reporting Services with SharePoint allows the products to share content databases and security models. Reports can then be displayed and managed in your SharePoint site right alongside other SharePoint content on your site.
Configuring the integration between the two is not obvious. With a little guidance and some up front planning, however, the integration can be setup rather quickly.