Thursday, 9 January 2014

SP 2013 SSRS failing after RBS enabled and disabled

Problem: I have SSRS (SharePoint mode) enabled on my SharePoint 2013 farm using SQL 2012 which was working, I enabled AvePoint's RBS provider on the farm and enabled RBS on 1 out of 2 web applications.  I then disabled RBS on the web applications and assumed all was good, RBS stopped working and threw 1 of 2 errors on the RBS enabled then disabled web application:
"For more information about this error navigate to the report server on the local server machine, or enable remote errors" or ....

Note: Rdl in the system before RBS was enabled work, during RBS don't work and rdl's added after disabling RBS both fail

Initial Hypothesis/Error tracking:
1.> SSRS and WCA errors unfortunately don't get correlationId's, so I turned off all the SSRS SSA instances except 1 so I know which server to find the error on in ULS log. 
2.> I ran the request for the report (rdl, this report just displays a label so I know it is good) again so the error is captured in the ULS log.
3.> I painfully open the latest log using ULS viewer and scan for errors and I find: 
System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'rbs_fn_get_blob_reference', database 'SP_Content_PaulXX', schema 'mssqlrbs'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

4.> Now I am going nuts as RBS has been disabled and I decide to trace the request in SQL Profiler, I can't find the call in SQL Server profiler and while looking for it I realise the function is not in the database.  I also find a post suggesting changing permissions but as I don't have the function, permissions isn't my issue. 
5.> I start looking at RBS on the farm using PowerShell PS>
cls
$cdbs=Get-SPContentDatabase
foreach ($cdb in $cdbs)
{
 $rbs=$cdb.RemoteBlobStorageSettings
 Write-Host "Content DB:" $cdb.Name
 Write-Host "Enabled:" $rbs.Enabled

}
I notice that the content database  'SP_Content_PaulXX' mentioned in the ULS log has the RemoteBlobStorageEnabled flag set to true.

Resolution:

*************************************************
Problem: I have SP2013 + SP2012, I am using SSRS in SP mode.  My app pool accounts for my web app and my SSRS SSA are different and on separate servers.  So for this to occur, you need SP2013, SSRS, RBS Enabled (or the Content database still thinks RBS is enabled), additionally the service account used by the SSRS SSA needs to have minimal permissions.  Existing rdl files display correctly however any rdl files added throw an exception.  The diagram below further explains the scenario:
Initial Hypothesis: Trawling through the ULS logs show the error: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'rbs_fn_get_blob_reference', database...

A snippet of the ULS is shown below:

 
Resolution:  The app pool account used by the SSRS Service Application needs to have permissions to run the function. 
1.> Figure out the app pool account used by the SSRS SSA if you don't know it as shown below:

2.> Give the SP_Services account permissions over the erroring execution calls.  To prove it give the account dbo rights.

 
Thanks to Sam Keytel and Mark Oburoh for looking at this with me.
 

More Info:
 
 


0 comments:

Post a comment