It’s never a pretty sight when you open the Windows Application Log and discover that dreaded ‘sea of red’. Well, that is what I was witnessing yesterday across a SharePoint Farm I was troubleshooting. The content databases for this Farm environment are located on a SQL 2005 cluster, while the SSRS Reporting Services databases (which is the database in question), reside on a separate SQL 2008 R2 instance.
The Windows Event log was the following:
And this could be traced back to the following in the SharePoint ULS:
Unknown SQL Exception 297 occurred. Additional error information from SQL Server is included below. The user does not have permission to perform this action. ffcbd7d9-9e18-4488-be91-331d3ab8c302 03/18/2013 12:33:45.24 OWSTIMER.EXE (0x0D40) 0x0A68 SharePoint Foundation Database tzku High ConnectionString: ‘Data Source=SPSSRS;Initial Catalog=SP2010_Int_ReportServer_SQL2012;Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15′ ConnectionState: Closed ConnectionTimeout: 15 ffcbd7d9-9e18-4488-be91-331d3ab8c302
Fortunately, the fix is quite simple. Your SharePoint Farm account requires the ‘View server state’ Server permission, which, funnily enough, is applied at the Server Level J
The SharePoint Farm account already has this setting applied to the SQL Server where all of the content databases reside, although this error was for the ‘SSRS Reporting Server Alerting DB, which is located on a SQL 2008 R2 instance. So, it seems that when the ‘SSRS 2012 Service Application’ was instantiated, the installation failed to apply this setting.