Configure SQL Server Reporting Services (SSRS) 2012 Integration with SharePoint 2013 – Step by Step Guide

This implementation guide walks through step by step on configuring SQL Server Reporting Services 2012 Integration with SharePoint 2013.

What’s New in SSRS 2012 with SharePoint 2013?

Unlike its previous versions, SSRS 2012 with SharePoint 2013 installation & configuration has been greatly simplified.  There are lots of changes in installing & Configuring Reporting services 2012 compared with its previous version SQL Server 2008 R2.  Reporting Services is now a service application in SharePoint 2013!

No more SQL Server instance of type SharePoint integrated Mode! Just install features “Reporting Services – SharePoint” and “Reporting Services Add-in for SharePoint Products” during SQL Server 2012 installation.

We don’t have to touch “Reporting Services Configuration Manager” to create Reporting Services databases, setup service accounts, etc. Also, we don’t have to go to Central Admin >> General Application Settings >> and Configure Reporting Services Integration, as they are part of service applications now!  These steps should be performed for SQL Server 2008 R2 Reporting services integration with SharePoint 2013/2010 still! But not for SQL Server 2012!!

SQL Server Reporting Services with SharePoint Integration – Topology:

Steps Overview

1. Install the Reporting Services Features in SharePoint Application Server.
2. Register SSRS Service in SharePoint App Server
3. Create Reporting Services Service Application in SharePoint Central Administration

Step 1: Install the Reporting Services Features in SharePoint Application Server.

I’ve used my SharePoint Central Administration Server as a Report Server. You can also choose a dedicated App server where SharePoint 2013 is installed for Report Server role. Lets start.

1. Login to your Identified Report Server as a Administrator, Mount SQL Server installer media, Start the SQL Server 2012 installer and click on “New SQL Server Stand-alone Installation or Add features to an existing installation” link
2. Choose either New installation or add feature to existing installation based on your scenario. In the Reporting Services Configuration step, Install features “Reporting Services – SharePoint” and “Reporting Services Add-in for SharePoint Products”.
3. Run through the installer to complete the installation. You have to Install SSRS Add-in for SharePoint products on all other WFE servers either by running above wizard and choosing only “Reporting Services Add-in for SharePoint Products” or from a separate Add-in download from internet.

Step 2: Register SSRS Service in SharePoint App Server

Open SharePoint 2013 Management shell as an administrator, Run these three PowerShell cmdlets to Install SSRS Service & Proxy on SharePoint Application Server which serves a Reporting Server.

Install-SPRSService

Install-SPRSServiceProxy

Get-SPServiceInstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

This installs the core SSRS services into the SharePoint Farm, provisions and starts the service instance.

Now, if you go to Central Administration >> Services on server, you’ll see “SQL Server Reporting Services Service” is started.

Step 3: Create Reporting Services Service Application in SharePoint Central Administration

Now, the SSRS service is installed and available to SharePoint. Go to:

1. Central Admin >> Manage Service Applications >> New >> SQL Server Reporting Services Service Application.
2. Create the new SQL Server Reporting services service application by filling details such as Service application Name, Service account, Database Name (remember to remove the GUID from the database Name!), Web Applications to associate.
3. Wait for the successfully created service application message. Click on the “Provision Subscriptions and Alerts” link (This step is optional, however).
4. Click on the “Download Script” button.  Copy the SQL Query and execute it in your SQL server. This script adds the RsExecRole to the Reporting Services dbs. Fill in the User name, Password to Run SQL Server agent and check the box for windows credentials.
5. Now we’ve the Service application ready. You can go back to Manage Service Applications and configure SSRS specific settings.
   

Verify the Reporting services Integration features

• Navigate to Site Settings >> Site Collection Features.
• Click on Site Collection Features under site collection administration
• Check if the Report Server Integration Feature is Active, if not just click activate

Unit Testing:
Create a new document report library, Enable content type and add “SQL Server Reporting Services Report” content in it. Build a SSRS report using either Report Builder tool or with Visual Studio. Verify whether the SSRS runs smooth and reports are getting rendered on the site.  I’ll publish an another article on building SSRS reports on SharePoint 2013.

References:

• https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-the-first-report-server-in-sharepoint-mode?view=sql-server-2016
• https://technet.microsoft.com/en-us/library/jj219068.aspx