Establishing SQL Server Reporting Services in SharePoint Integrated mode was something of a burden until the release of SQL 2012. With SQL 2005 and SQL 2008 the majority of the configuration steps were manual and implementing delegation for Kerberos, meant editing a number of Reporting Services configuration files – it was quite cumbersome and pretty much open to failure if you didn’t follow all of the details.
The introduction of SQL 2012 improved things dramatically as SSRS could now be implemented as a conventional SharePoint Service Application. Recently I was working with a customer who were trying to publish SSRS reports across two SharePoint 2010 Farms and experience having issues – so much so, that it eventually led to engaging Microsoft. And it’s the outcome of this engagement which is the pertinent point of this blog.
The two SharePoint Farms we attempted to instantiate in a Trusting/Publishing service application set-up were identical in terms of patch-level, while the Publishing Farm was running SQL Server Reporting Services 2012 SP1 CU4 in SharePoint Integrated mode. The Publishing Farm was only recently patched to SP1 CU4 to facilitate the introduction of Power View (this is the minimum patch level requirement).
Steps for the required configuration are captured in the following article which we attempted to follow:
Establishing a trust between the Farms was not an issue and we passed this part of the configuration with flying colours:
However, as soon as I tried to browse Cross-Farm to the library which contained the SSRS Reports on the Publishing Farm from the Consuming one, I was hit by an error. To reach this stage I would add the Report Viewer Web Part to the page (this Web Part is available once you’ve installed the SSRS Add-in to your Farm and activated the ‘Report Server Integration Feature’.
But as soon as a copied the direct link to the report in the ‘Report’ section, or even browsed for the Publishing Farm, I received the error below:
The item http://<publishingfarm>/site/report.rdl cannot be found –> Microsoft.ReportingServices.Diagnostic.Utilities.ItemNotFoundException
Interestingly, the inability to connect to remote SSRS reports was not evident if used the ‘Page Viewer’ Web Part – this worked absolutely fine. However, this web part is not really fit for purpose when trying to display SSRS Reports as it’s not dynamic in its resizing capabilities. For the business requirement, this wasn’t a workaround.
After spending a fair amount of time searching forums and hunting through the ULS Logs for a resolution, I turned to Microsoft for assistance and asked them to quantify whether any steps from the blog article were incorrect or missing?
As things transpired following a number of calls and WebEx sessions, MS came to the conclusion that the behaviour I was witnessing was caused by a code change introduced with SQL 2012 SP1. The Reporting Services product group have assessed the possibility of implementing a fix which should be addressed in the next SQL Service Pack.
So, the lesson to be learnt is stay with SQL 2012 RTM if you want to leverage SSRS as cross-Farm shared Service Application.