Install and Configure SQL Server Reporting Services 2012 SP1 and 2014 in SharePoint Integrated Mode Part 1

By:   |   Updated: 2015-01-26   |   Comments (6)   |   Related: > Reporting Services Installation


Problem

In a previous tip on Configure Reporting Services to Run in SharePoint Integrated Mode, we've seen how we can integrate Reporting Services with SharePoint. However, a lot has changed between SQL Server 2008 and SQL Server 2012 with SP1 and even SQL Server 2014 as far as SharePoint integration is concerned. How can we install and configure Reporting Services 2012 SP1/2014 in SharePoint-Integrated Mode?

Solution

Starting with SQL Server 2012, Reporting Services in SharePoint-integrated mode has been changed from a Windows Service (like what we have in native mode) to a SharePoint service application. A more detailed explanation of the changes in the architecture is available from Jaime Tarquino's MSDN blog post. This also means that the way we install and configure Reporting Services in SharePoint-integrated mode is totally different than the way we did it in the past.

This tip is the first of a series of tips that walks you through installing and configuring Reporting Services in SharePoint-integrated mode. While the RTM version of SQL Server 2012 works just fine, I recommend using the one with Service Pack 1 or higher when you're doing the installation as it also includes support for running Excel workbooks containing Power View reports. I am using SQL Server 2014 for the demonstration, but the steps are the same for SQL Server 2012. I am also using SharePoint Server 2013, but it also works well for SharePoint Server 2010. My recommendation is to keep everything current - both SQL Server and SharePoint - to take full advantage of the different business intelligence (BI) features introduced in the latest versions.

The installation and configuration is subdivided into multiple sections – installation of Reporting Services in SharePoint-integration mode, installation of the Reporting Services Add-In, installing and starting the Reporting Services SharePoint Service (optional, depending on the installation process) and creation of a Reporting Services service application. Prior to installation, make sure that you have a domain account available for the Reporting Services service application pool account that has the appropriate permissions to the SQL Server instance where the Reporting Services databases will be created - db_creator and securityadmin server roles are the minimum permissions required.

Installing the Reporting Services in SharePoint-integration mode

Installation of Reporting Services in SharePoint-integration mode should be done on the SharePoint servers that will run the Reporting Services service application. For a multi-tiered farm, this should be done on all of the application servers for both scalability and availability. It is assumed at this point that the application servers have been joined to the SharePoint farm.

To install Reporting Services in SharePoint-integrated mode

  1. Run setup.exe from the installation media to launch SQL Server Installation Center. Click on the Installation link on the left-hand side
  2. Click on the New SQL Server stand-alone installation or add features to an existing installation link. This will run the SQL Server Setup wizard


    3. 1

  3. In the Product Key dialog box, enter the product key that came with your installation media and click Next.
  4. In the License Terms dialog box, click the I accept the license terms check box and click Next.


    5. 2

  5. In the Microsoft Updates dialog box, you have the option to Use Microsoft Updates to check for updates (recommended) in the installation process. If your servers are connected to the Internet, the installation media will connect to Microsoft Update and check for available SQL Server updates. You can opt to install the detected updates as part of the installation process. Click Next.


    6. 3

  6. In the Install Rules dialog box, validate that the checks return successful results. If the checks returned a few warnings, make sure you fix them before proceeding with the installation. Click Next.


    7. 4

  7. In the Setup Role dialog box, select the SQL Server Feature Installation option and click Next.


    8. 5

  8. In the Feature Selection dialog box, select Reporting Services - SharePoint. Click Next.


    9. 6

    NOTE: Unless the application server also functions as a web front end, the Reporting Services Add-In for SharePoint Products need not be installed, as is the case here.

  9. In the Reporting Services Configuration dialog box, validate that the option Install only under Reporting Services SharePoint Integrated Mode section is selected. Click Next.

    The beauty of this option is that you don't have any other options to choose from because you've chosen the Reporting Services - SharePoint feature in the previous dialog box.

    10. 7

  10. In the Feature Configuration dialog box, verify that all the rules have passed. Click Next.
  11. In the Ready to Install dialog box, verify that all configurations are correct. Click Install.


    12. 8

  12. In the Complete dialog box, click Close. This concludes the installation of Reporting Services in SharePoint-integrated mode.


    13. 9

    NOTE: A server reboot might be required after the installation.

Installing the Reporting Services Add-In for SharePoint Products

Installation of Reporting Services Add-In for SharePoint Products should be done on the SharePoint web front end servers and is a required component for a Reporting Services instance running in SharePoint-integrated mode. The add-in provides a way to manage Reporting Services and view related files within a SharePoint site. It is also responsible for features such as Power View, the Report Viewer web part and the URL proxy endpoint that are specific to the SharePoint site collection using it. The Reporting Services Add-In for SharePoint Products (rsSharePoint.msi) can be installed using the SQL Server installation media. Alternatively, it can be downloaded from the Microsoft Download Center.

TIP: Installing the Reporting Services Add-In for SharePoint Products on multiple web front end servers will be much faster when the rsSharePoint.msi file is copied on all of the servers and installed manually as compared to using the SQL Server installation media directly.

To install the Reporting Services Add-In for SharePoint Products:

  1. Run the rsSharePoint.msi file.
  2. In the Welcome to the Installation Wizard for SQL Server RS Add-in for SharePoint dialog box, click Next.


    3. 10

  3. In the License Agreement dialog box, select the I accept the terms in the license agreement option, click Next.


    4. 11

  4. In the Ready to Install the Program dialog box, click Install.


    5. 12

  5. In the Completing the SQL Server RS Add-in for SharePoint installation dialog box dialog box, click Finish. This concludes the installation of the SQL Server RS Add-in for SharePoint on the web front end servers.

In the next tip in this series, we will register and start the Reporting Services SharePoint service on the appropriate servers in our SharePoint farm. We will also look at creating a Reporting Services service application, both using SharePoint Central Administration and the Windows PowerShell cmdlets for the Reporting Services SharePoint-integrated mode.

Next Steps
Check out the following items


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2015-01-26

Comments For This Article




Thursday, July 27, 2017 - 6:48:03 PM - bass_player Back To Top (63430)

This is specifically for SQL Server 2012 (with SP1) and SQL Server 2014 Reporting Services, not SQL Server 2008 nor SQL Server 2008 R2

Wednesday, July 26, 2017 - 5:07:14 PM - Orhan Erten Back To Top (63297)

Is SP1 mean SQL Server Reporting Services 2008 R2 or SQL Server 2008 R2 update service pack?

 

Thank you and Regards,

 

         Orhan Erten

Monday, April 25, 2016 - 12:06:33 PM - bass_player Back To Top (41326)

 The RSSharePoint.msi is the proxy to access the Reporting Services service application. Below is a reference from SQL Server 2008 R2. Quite old but still applies

https://msdn.microsoft.com/en-us/library/aa905871(v=sql.105)

The reason behind it is because you can easily scale out your farm should the workload increases over time. When the proxy and the Reporting Services service application are in the same machine, you are sharing resources for both. As the workload increases, you will still need to either scale up by adding CPU and memory or scale out.

Some follow what is known as the Streamlined Topology Guidance for SharePoint 2013 where the proxy and the Reporting Services service application are in the same machine for low latency workloads.

https://www.microsoft.com/en-us/download/confirmation.aspx?id=37000

You decide which one is applicable to you.

Friday, April 22, 2016 - 2:57:56 AM - Mark Back To Top (41301)

Hi I have checked out this tips and the part 2. You said  rssharepoint.msi should be installed at SharePoint web front end server while the SSRS should be installed at application server.

Could you please explain the reason behind? Any official reference? Thanks for your time!

 

Thursday, January 29, 2015 - 8:32:17 AM - karabo Thari Back To Top (36100)

Wow brillant step by step guide cant wait for creating report services service application in SharePoint Tip

Thursday, January 29, 2015 - 8:29:45 AM - karabo Thari Back To Top (36099)

Wow thank you so much this is  really perfect step by step guide














get free sql tips
agree to terms