Josh's SQL Server BI Blog

Tips and Tricks for SQL Server Reporting Services, Master Data Services and Data Quality Services

Implementing the SSRS 2012 Service Application in a SharePoint 2010 Cross-Farm Environment - Part 2 of 2

Implementing the SSRS 2012 Service Application in a SharePoint 2010 Cross-Farm Environment - Part 2 of 2

Rate This
  • Comments 1

In Part 1 of this article I went through the steps needed for setting up the SharePoint trust between the Publishing and Consuming farms. The trust is needed for the Reporting Services service application to be shared across the SharePoint farms.

For Part 2 of this article we will discuss what is needed on the Publishing Farm in order to publish the Reporting Services service application, what is needed on the Consuming Farm in order to connect to the published service application and how to setup a Report Viewer web part on the Consuming Farm to view a published report.

Setting up the Publishing Farm

First off, complete the steps from the following MSDN article to setup and configure the Reporting Services service application:

Install Reporting Services SharePoint Mode as a Single Server Farm

After creating the Reporting Services service application, you should see a new SSRS service application similar to this:

 

 

Select the service application and click on the Publish option.

Within the Publish Service Application window, you can choose for the consuming farm to connect to the service via http or https. Make sure that the Publish this Service Application to other farms is checked. You will need to copy the Published URL and use it later to connect the consuming farm to the service application.

Now that we have published the service application, we need to provide permission to the consuming farm to use it. Select the service application and click on the Permissions option.

You will need the Consuming farm's FarmID. You can get it by opening the SharePoint 2010 Management Shell on the consuming farm and running the following command:

(Get-SPFarm).Id

Now you can enter the FarmID from above into the permissions window and it will detect that it is a Remote Farm. Assign the consuming farm Full Control.

Finally, we can connect the Consuming Farm to the published SSRS service application.

From the SQL Server 2012 media you will need to install the following on the consuming farm application server:

  • Reporting Services - SharePoint
  • Reporting Services Add-in for SharePoint Products

Within Central Administration, connect to the published SSRS Service application by going to Application Management - Service Applications - Manage Service Applications and choosing SQL Server Reporting Services Service Proxy from the Connect menu.

Here is where you need the Published URL from the Publishing farm's service application Publish window. Paste it in and click OK.

On the next two screens, you will need to confirm that you want to add the service application proxy and give it a name.

You should now have the SSRS service application proxy listed under Service Applications.

How do we use it?

So now that we have a shared service application between two farms, what can we do with it? As I mentioned earlier in the post, there are some limitations. The assumption is that sharing the service means that the consuming service proxy can be used to create reports, data sources, etc in the same way you can on the publishing farm. Unfortunately, that is not the case. However, it does add the ability to view reports which are located on the publishing farm by using the Report Viewer web part on the consuming farm (the full page viewer will not work in the consumer farm). This new functionality does not exist in previous version of Reporting Services or between non-trusting farms. I will go through this process below.

      1.    Create a New Page within the web application and give it a name

      2.    Insert a SQL Server Reporting Services Report Viewer web part

      3.    Edit the Report Viewer web part

      4.    Within the web part properties pane, you can manually enter the path to the publishing farm's RDL file or browse to it

      5.    Apply the settings and you will see the publishing farm's report render within the web part

Hopefully this helps clear things up a bit and provides some good information for you on configuring the Reporting Services service application for use in a SharePoint cross-farm scenario.

Until next time...

Josh Gerszewski | Microsoft SQL Server
http://twitter.com/joshger

Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
  • Thanks for this blog posting.  I've created and published a SQL Reporting Services Service Application on one of my farms.  I'm now trying to connect to the published service application, but SQL Server Reporting Services Serice Proxy is not listed in the list of options under the Connect menu.  Do I need to install something on the consuming farm?  Say "Reporting Services - SharePoint", "Reporting Services Add-in for SharePoint Products" or both?  I'm working with SharePoint 2013 Ent and SQL Server Ent SP 1.  Thanks.

Page 1 of 1 (1 items)