Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I

Integrating Reporting Services with SharePoint is really a fun and it gives extremely user-friendly environment for Reporting Services. There are mainly two ways of integrating Reporting Services with SharePoint. If Reporting Services is installed in Native Mode, Report Explorer and Report Viewer web parts can be used for interacting with reports. When the Reporting Services is installed in SharePoint Integrated Mode, SharePoint takes overall functionalities of Reporting Services, including SharePoint features such as versioning, alerts, and enterprise search. I recently configured SQL Server 2008 R2 in SharePoint 2010 as Integrated Mode, thought to share the experience; the reason for this post. Note that this is the way I configured the integration, you may take different approach for this integration.

I started this with Windows 2008 R2 and configured SharePoint 2010 on it. See this post “Configuring Windows 2008 R2 for SharePoint 2010” if you come across any issue with the installation. When SQL Server Reporting Services is installed, make sure that you select either “Install the SharePoint integrated mode default configuration” or “Install, but do not configure the report server”.

If you need to complete the SharePoint farm installation using non-domain accounts, this post “Complete Farm SharePoint 2010 installation on Single Server using non-domain accounts” shows you the way.

Once the server is ready with SharePoint 2010 and SQL Server 2008 R2, configuration can be started. If you have selected “Install, but do not configure the report server”, you need to configure Reporting Services in SharePoint 2010 mode. Here are steps for configuring it;

• Open Reporting Services Configuration Manager and connect with Reporting Services instance.
• Make sure that Report Service status is Started. Notice that Report Server Mode is Native.
• Open the “Report Service Database” section by clicking the Database navigational tab button. It shows the SQL Server Name, Database Name, and Mode if configured. Click on Change Database button to launch Report Server Database Configuration Wizard. Here is a screenshot of it;
  
• Click Next and open the next window of the wizard. Configure the database server which will host the Reporting Services instance. Once authentication is set, click Test Connection and see whether connection can be established successfully. Click Next.
• Type the database name as ReportServer which is the default name. However you can give a names like ReportServerSharePointIntegrated too. Select the option Report Server Mode as SharePoint Integrated. Click Next to continue.
• Set credentials that will be used for connecting to the database. If everything is in one box, Service Credentials would be enough.
• When you click on Next, you will see the summary of what you have entered/selected. Continue with the wizard and complete it.
• If need to change the port of Web Service URL, go to Web Service URL section by clicking Web Service URL navigational tab button and set it. I have set the port as 20000 and my Web Service URL is http://DP03:20000/ReportServer.
  

Next step is downloading Reporting Services Add-in for SharePoint 2010. Here is the link for downloading it:http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b3bebf9d-d86d-48cd-94e2-0639a846be80.

Once it is downloaded, start installing it. Starting screen would be this;

Complete the installation. Now all required parts are ready. Next is configuring SharePoint for Reporting Services. Open SharePoint 2010 Central Administration and click on General Application Settings link.

Once the General Application Settings is open, a section called Reporting Services can be seen which allows managing integrating settings.

The link Reporting Services Integration opens the window where Report Server Web Service URL and Authentication Mode can be set. The Report Server Web Service URL should be the URL that has been configured with Web Service URL navigational tab of Reporting Services Configuration Manager. Authentication mode can be either Windows Authentication or Trusted Authentication. Since everything is in one box, Windows Authentication would be fine with this.

In order to user Trusted Authentication, a trusted account has to be predefined. When Trusted Authentication is used, Report Server has no knowledge on the user connected to the SharePoint. Trusted account impersonates connected SharePoint user and will be used for accessing the Report Server. Trusted Authentication is usually used with Form Authentication or windows authentication without AD or Kerberos. Here is my screen;
Set Server Defaults under Reporting Services allows changing default server settings for Reporting Services. Some of this elements can be changed are; Number of Snapshots, Ad-Hoc Reporting and Client-Side Printing.

Now the integration is done. The SharePoint is fully integrated with Reporting Services. We need a hosting environment for Reporting Services. Since Reporting Services Add-In has added necessary content types for the Farm, reports can be added to any site. Or else, a new site can be created for hosting Reporting Services reports and connections. See Part II for creating a site, configuring libraries and content type, and publishing reports from BIDS.