How to Configure SQL Server Reporting Services 2012 for use in SharePoint 2010

SQL Server Reporting Services (SSRS) has come a long way from its first version, as an add-in to SQL Server 2000, to its current day form SSRS 2012. Starting in SSRS 2005, Microsoft offered an additional option that allowed you to be able to install an SSRS instance in SharePoint Integrated Mode – which allows you to utilize SharePoint as your report manager. While there were many benefits to SharePoint mode, installation was always confusing, requiring multiple steps both in SharePoint & SSRS.

Starting in SSRS 2012, installation has been greatly simplified. Gone are the days requiring a separate SSRS instance set up just for SharePoint. Now, SSRS is just another SharePoint service application like PowerPivot, Excel Services, etc., which fits nicely into the SharePoint model.

Here is a guide to configuring the new way of setting up SSRS 2012 in SharePoint 2010:

Prerequisites

– SharePoint 2010 installed and deployed, logged into a farm machine as a farm administrator.

– SQL Server 2012 installed and deployed with SSRS Integration features selected as highlighted.

Provision Service Application / Proxy

– Open the SharePoint Management Console by right clicking the icon and selecting, ‘Run as Administrator’. Once the shell window has opened, install the service application by entering Install-SPRSService. One done, install the proxy by entering Install-SPRSServiceProxy.

– To verify that they are installed enter the following command into the console: Get-SPServiceInstance -all |where {$_.TypeName -like “SQL Server Reporting*” }

You should get something similar to this:

Create Service Application

– Navigate to your SharePoint farm’s Central Administration and go to the service applications page:

– Select the new dropdown and choose SQL Server Reporting Services Application.

– Type a unique, recognizable name for the new service application. Then choose or create a new SharePoint application pool that you would like the SSRS service to run under.

– Next, choose the database server where you would like the SSRS configuration database to be created. Finally, choose the SharePoint web applications that you would like this instance to serve.

– Once created, if you go to Manage Service Applications, you should see your newly created instance & proxy.

Start SSRS Service

– If the service is not started, you can go to Home -> System Settings -> Manage Services on Server, then click the Start hyperlink to the right of the service.

Note: if your service is hung in the ‘starting’ phase, you may need to restart the SharePoint Timer Windows Service and try again.

Configure SSRS

– Now that the service is started, you can go back to Manage Service Applications and configure SSRS specific settings.

– One of the more important settings is Subscriptions and Alerts. Without provisioning this, you cannot set up subscriptions or alerts for any reports. To do this, click on the Provision Subscriptions and Alerts from the main settings page. Before doing anything, make sure that the SQL Server Agent is running on the SQL Server that contains the database that was created earlier.

Once the agent is running, click the ‘Download Script’ button to get a dynamically generated sql script. Run this script on the SQL Server that contains the database that was created earlier. Once done, enter a sql or windows login into the form, making sure that the account has enough access as described in the above screenshot.

That’s it – you can now upload SSRS RDL files to any document library in your provined sites and run them. Happy Reporting!

Phone: 312-602-4000
Email: marketing@westmonroepartners.com
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons