Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode
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”.
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.
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.
To Create a site, configuring libraries and content type, and publishing reports from BIDS.
Let’s start creating a Web Application. Go to SharePoint Central Administration and click
Manage Web Applications under
Application Management. Once
Application Management is open, click on
New to create a new web application.
Some of the attributed in
New Web Application Form as as follows;
- Authentication
Select Classic Mode Authentication
- IIS Web Site settings
Select Create a new IIS web site option. I have named it as Report Server – 10005 and set the port as 10005.
- Security Configuration
Leave default values
- Public URL
In my case, URL is http://DP03:10005
- Application Pool
Make sure Create new application pool is selected. I have named it as ReportServer – 10005.
- Database Name and Authentication
Set the SQL Server name for Database Server. I have named the database as WSS_Content_ReportServer_10005.
Click
OK to save setting. Once the process is completed,
Application Created windows is appeared. Click
OK to continue. New web application will be listed in Web Application window.
Next step is creating a site collection. Go back to
Central Administration and click
create site collections under
Application Management. This opens
Create Site Collection window. Let’s ceate one;
-
-
Add Title as Report Server.
-
Let’s use the root for the Web Site Address. Do not select sites from the drop-down. Leave it blank.
-
Select the Template as Team Site.
-
Set both Primary Site Collection Administrator and Secondary Site Collection Administrator. Since I have everything in one machine, I set both as DP03\Administrator;.
-
Click
OK to create the site.
Once done, a message will be appeared saying
Top-Level Site Successfully Created. Click on the link to go the site (In my case, link is
http://dp03:10005). This is what I see;
Site needs libraries for holding reports and connections. Let’s create two libraries for the site.
-
Click on Libraries link in left pane. It lists all document libraries available.
-
Click the button Create for creating a new library.
-
Select
Document Library and Set the name as
Reports.
-
Click
Create for creating the library. Once the library is open, click on
Library Settings for adding content types.
-
When
Library Settings is open, library related things such as
General Settings,
Columns, and
Views are shown. By default, it does not show
Content Type. In order to enable content type, click
Advanced Settings under
General Settings. Once the window is open, select
Yes for
Allow management of content types.
-
Click OK to save settings. Note that now the Content Type is shown under Library Settings. Click Add from existing site content types link under Content Type. This opens Add Content Types page.
-
Select
Report Server Content Type from
Content Types From drop-down. Then move
Report Builder Report from left listbox to right listbox.
Note that if you are not seeing “Report Server Content Types”, that indicates that Add-In has not been properly installed”.
-
Click
OK to save settings.
Report Builder Report content type is now appeared under
Content Types of
Reports library.
-
We have to create another library for data connection. Follow same steps for creating the library. Name it as
Data Connections. Then enable
Allow management of content types as did for
Reports library. Then add
Report Data Source content type for it.
Now SharePoint 2010 is ready for holding reports from Reporting Services 2008 R2. Let’s try to publish some reports to this. Open Business Intelligence Management Studio and create a Reporting Services project. If you have AdventureWorks 2008 Sample Report Project, you can use it for testing. Once the project is open, get the properties of the project. Set following properties;
Now deploy reports and see. Reports will be published to SharePoint site we created. Once published, open the home page of the site (
http://dp03:10005) and click on
Reports link in left pane. It lists all the reports published.
Click one of reports and see whether it works fine. Here is the output of
Company Sales 2008 report.
All done. Reporting Services 2008 R2 is integrated with SharePoint 2010. I will be exploring more on this, specially SharePoint related things. Once they are explored, I will publish them.
Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode________________
You will not face much issues after configuring if it is done with Standalone SharePoint 2010 architecture. If it is a SharePoint farm which components are distributed, few things should be considered. Here are some of points you need to consider when Reporting Services is configured in SharePoint 2010 Farm. Note that this was tested with a Web Application which Authentication is set as Classic and NTLM is set for Authentication Provider.
Domain accounts created
Reporting Services Account: Domain account, no admin privileges. Usually name it as a Domain\ReportingServices. Set this with Reporting Service Configuration Manager.
Application Pool Account: Domain account, no admin privileges. Usually name as Domain\SP_SSRSApp. Use this when the Web Application is created with SharePoint.
Site Collection (or Site) account: Domain account, need local admin rights. Usually name as Domain\SP_Admin.
** All accounts are maintained as Managed Accounts in SharePoint.
Reporting Services Windows Service Account
This should be a domain account. Once it is set with Reporting Services Configuration Manager, you need add the same account to SharePoint too. First, have it as a Managed Account (Central Administration –> Security –> General Security –> Configure Managed Accounts). Then add the account to the Site where you have configured Reporting Services Libraries. You can add this account to Home Members group. Errors you may get if it is not added are “The permissions granted to user ‘Domain\User’ (what ever account you used for logged in) are insufficient for performing this operation.” and “rsAccessDenied”. Note that error has no link with the service account but it is related to it. You may get this error with Report Builder or BIDS when deploying reports, or viewing reports.
In addition to that, you need to make sure that the account is added to Web Application content database which is created for Web Application. If not, you may get and error “Report Server has encountered a SharePoint error”.
Other than that, this account is in ReportServer database too. It is automatically added, but make sure that it is there.
Allow Anonymous Setting
Make sure that Allow Anonymous is set as false when the Web Application is created. I faced an issue related deployment from BIDS if this is not set to false. Once the application is created, double-check with IIS (Site –> Authentication) and see whether it is disabled. As per the Authentication and Authentication Provider we set, Only ASP.NET Authentication and Windows Authentication should be enabled.
