SQL 2012 SP1 ‘breaks’ Cross-Farm SSRS Service Application

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:

http://blogs.msdn.com/b/joshger/archive/2012/08/23/implementing-the-ssrs-2012-service-application-in-a-sharepoint-2010-cross-farm-environment-part-1-of-2.aspx

http://blogs.msdn.com/b/joshger/archive/2012/08/23/implementing-the-ssrs-2012-service-application-in-a-sharepoint-2010-cross-farm-environment-part-2-of-2.aspx

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’.

9

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.

 

 

 

 

Reporting Services is sleeping…and won’t wake up

I experienced an issue recently where, after extended periods of inactivity, our SharePoint 2010 Reporting Services (SQL 2012 RTM) environment would essentially ‘go to sleep’ and was showing no signs of waking up. Each morning, following an overnight ‘quiet period’ my users would be on the phone complaining that their Reports weren’t functioning and the error message they received was as a generic one. Under the SharePoint hood, this is what we were witnessing in the ULS:

  • Server Reporting Services     Report Server Catalog             0000    Unexpected    Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: , Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. —> System.Runtime.InteropServices.COMException (0x800703FA): Retrieving the COM class factory for component with CLSID {BDEADF26-C265-11D0-BCED-00A0C90AB50F} failed due to the following error: 800703fa.     at Microsoft.SharePoint.Library.SPRequest..ctor()     at Microsoft.SharePoint.SPGlobal.CreateSPRequestAndSetIdentity(SPSite site, String name, Boolean bNotGlobalAdminCode, String strUrl, Boolean bNotAddToContext, Byte[] UserToken, String userName, Boolean bIgnoreTokenTimeout, Boolean bAsAnonymous)     at Microsoft.SharePoint.SPRequestManager.GetContextRequest(SPRequestAuthenticationMod…    0dedb9f0-be02-4297-b334-2594a9681274

    As a workaround while this was investigated, I was using Windows Task scheduler to action a restart of SSRS Service Application at regular intervals overnight:

    Stop-SPServiceInstance -Identity d8f38b2d-9588-4e83-8031-8a6ed33559ed -Confirm:$false

    Start-Sleep -s 180

    Start-SPServiceInstance -Identity d8f38b2d-9588-4e83-8031-8a6ed33559ed

    The SSRS layer had recently been upgraded from SQL 2008R2 to SQL 2012 and thus, the application was now being leveraged as a SharePoint Service Application. And one key change was the fact that the SSRS Application was now running within its own IIS App Pool under a dedicated domain service account. And it was a setting in IIS which was proving to be the root of the problems.

    The Application Pool(s) running SSRS need to have the ‘Load User Profile’ setting set to True

    IIS will not load the domain user profile, although a user profile has to be created to store temp data in either the profile directory or registry hive. By default, if you were using ‘Network Service’, this would always be available as it was created by the system, but in this instance we’re using a domain service account. Only the OOTB standard IIS pools (Default and Classic .Net have user profiles on disk but of course, nothing is generated when you manually create an AppPool. And this is where the ‘Load User Profile’ attribute comes into play and should be set to ‘True’

Which SharePoint Server is publishing my SSRS Reports?

To provide resilience and to share performance load, the recommended approach is to have multiple (well, more than one anyway!) SharePoint Servers publishing your SSRS reports (in SharePoint mode).

The introduction of SQL 2012 certainly enhanced the load-balancing capabilities which weren’t as stable and effective in SQL 2008 R2, but regardless of which version of SQL you are leveraging, there is no quick way of identifying which server was publishing your report. But here’s the trick, instead of trawling through the ULS logs, put SharePoint to one side for a minute and kindly ask a DBA to open SQL Management Studio for you.

And run the following SQL query (uses the ‘Execution_log’ View)

/****** Script for SelectTopNRows command from SSMS ******/
SELECT
TOP 100 [InstanceName], [ReportID], [UserName], [RequestType], [Format], [Parameters], [TimeStart], [TimeEnd], [TimeDataRetrieval], [TimeProcessing], [TimeRendering], [Source], [Status], [ByteCount], [RowCount]
FROM
[My_SSRS_ReportServerdb].[dbo].[ExecutionLog]