Unknown SQL Exception 297 occurred

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.

 

 

EXECUTE permission denied on SharePoint Config DB

Whilst troubleshooting a SharePoint 2010 throttling issue earlier this week, I stumbled across some totally unrelated SQL exceptions in the Windows Event log:

Insufficient SQL database permissions for user ‘Name: mydomain\domain_user SID: S-1-5-21-348440682-330175067-1304115618-117920 ImpersonationLevel: Impersonation’ in database ‘Config_DB_SQL_SharePoint’on SQL Server instance ‘SQL_SharePoint’. Additional error information from SQL Server is included below.

The EXECUTE permission was denied on the object ‘proc_GetProductVersions’, database ‘Config_DB_SQL_SharePoint’, schema ‘dbo’

It seems that this exception is generated when you click on the ‘Create Site’ option and Silverlight kicks-in (even though it doesn’t appear to be denying me access to anything)

 

The Farm I was troubleshooting against is patched to ‘February 2012 CU’, so I cannot confirm at this stage whether this has been fixed in a more recent update. If you are precious about what is appearing in your Windows Event Logs and want to mitigate against the ‘red-herrings’, you can apply the following SQL fix (but be extra careful as this is against the ‘Config_DB’)

  • Expand your SharePoint Configuration database and navigate to ‘proc_GetProductVersions’ under Stored Procedures
  • Right-click proc_GetProductVersions’ and choose Permissions
  • Select the Search button and browse for ‘WSS_Content_Application_Pools’
  • Provide ‘Execute’ permissions for ‘WSS_Content_Application_Pools’
  • Click OK