SharePoint 2013 Hosting :: Configure SQL Server Reporting Services With SharePoint 2013

In this article we learn how to configure reporting services integration with SharePoint 2013. SQL Server 2012 Service Pack 1 (SP1) is a version of Microsoft SQL Server 2012 that supports Microsoft SharePoint 2013 Excel Services usage of Excel workbooks containing data models and Reporting Services Power View reports.

The Reporting Server is essentially an Application Server that is joined to the SharePoint farm. It will run the SSRS Service Application. Using the SSRS integrated mode now means that it is fully under SharePoint.

Installation to be done on each server

Web Front End

Use the following procedure to install the Web Front End:

  • Install SP2013 Prerequisites
  • Install SharePoint Server 2013
  • SharePoint Products Configuration Wizard
  • Create Root Web Application (Port 80)
  • Create Root Site Collection
  • Create all Service Applications manually (this was for other purposes; Reporting Services was not one of them yet)
  • Install SQL Server 2012 selecting only the Reporting Services Add-In for SharePoint
  • Install SQL Server 2012 SP1 for SharePoint 2013 Support
  • Application Server

Use the following procedure to install the Application Server:

  • Install SP2013 Prerequisites
  • Install SharePoint Server
  • SharePoint Products Configuration Wizard
  • Install SQL Server 2012, selecting both Reporting Services and the Reporting Services Add-In for SharePoint
  • Install SQL Server 2012 SP1 for SharePoint 2013 Support
  • We have the option to use SQL Server 2012 with Service Pack 1.

Database Server

Use the following procedure to install the Database Server:

  • Install SQL Server 2012 Selecting all the options including SSRS and integration and analysis services
  • Install Service pack1

Power Shell Changes

Make the followng changes for/using Power Shell:

  • Click the Start button.
  • Click the Microsoft SharePoint 2010 Products group.
  • Right-click SharePoint 2010 Management Shell then click “Run as administrator”.
  • Run the following PowerShell command to install the SharePoint service. A successful completion of the command displays a new line in the management shell. No message is returned to the management shell when the command completes successfully:

Install-SPRSService

  • Run the following PowerShell command to install the service proxy:

Install-SPRSServiceProxy

  • Run the following PowerShell command to start the service or see the following notes for instructions to start the service from SharePoint Central Administration:

get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance

  • You can also start the service from SharePoint Central Administration rather than running the third PowerShell command. The following steps are also useful to verify that the service is running.
  • In SharePoint Central Administration, click “Manage Services on Server” in the “System Settings” group.
  • Find SQL Server Reporting Services Service and click “Start” in the Action column.
  • The status of the Reporting Services service will change from Stopped to Started. If the Reporting Services service is not in the list, use PowerShell to install the service.

Central Administration Changes

Make the fllowing Central Administration Changes (If you are done with Power Shell then there is no need to do this configuration):

  • In SharePoint Central Administration, click “Manage Services on Server” in the System Settings group.
  • Find SQL Server Reporting Services Service and click “Start” in the Action column.
  • The status of the Reporting Services service will change from Stopped to Started. If the Reporting Services service is not in the list, use PowerShell to install the service
  • From the Manage Services on the Server click on the SQL Server reporting Services, as shown below:

Figure 1.jpg

  • In the screen, you can provide the Service name, database name and managed account details and click “Ok” to start the service; see:

Figure 2.jpg

  • It will take some time to configure this; it will look like:

Figure 3.jpg

  • Once all configurations are done successfully, you will get the following screen:

Figure 4.jpg

  • You can verify the same in the Manage services session. You will see that the services are started and working fine, as in:

Figure 5.jpg