Reporting Services issues after changes to system databases

• MattF

  SSCrazy

  Points: 2285

  More actions

  October 6, 2016 at 3:53 pm

  #313777

  We had an issue where Reporting Services was installed on a SQL 2012 server, but not configured. After vendor software was installed on the same SQL Server, it was then decided to utilize RS. There were multiple problems with configuring the Report Server as follows:

  The vendor software installation modified the Model, MSDB and Master system databases requiring the following to be resolved:

  1. Error received when creating a new ReportServer database "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation."

  It was found the Model database contained objects which use a different collation sequence to the default SQL collation. This prevented creation of a new ReportServer database from the Model template. Resolution: Create new SQL 2012 Report Server database on another SQL 2012 server and migrate to the new server.

  2. Master and MSDB system databases modified by vendor software install - resulted in multiple issues with creating report subscriptions beginning with this error:

  "rsReportServerDatabaseError" - The Execute permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

  Various workarounds were required to mitigate each subsequent issue related to permissions for the RsExecRole in the system databases - scripts below:

  use [master]

  GO

  GRANT EXECUTE ON [sys].[xp_sqlagent_notify] TO [RSExecRole]

  GO

  use [msdb]

  GO

  GRANT EXECUTE ON [dbo].[sp_help_category] TO [RSExecRole]

  GO

  GRANT SELECT ON [dbo].[syscategories] TO [RSExecRole]

  GO

  GRANT EXECUTE ON [dbo].[sp_add_category] TO [RSExecRole]

  GO

  -- error for Category does not exist in MSDB - required manual update to insert missing category:

  sp_configure 'allow update', 1

  go

  reconfigure with override

  go

  SET IDENTITY_INSERT syscategories ON

  go

  insert into syscategories(category_id, category_class, Category_type, Name)

  values(100, 1, 1, 'Report Server')

  go

  SET IDENTITY_INSERT syscategories OFF

  go

  exec sp_configure 'allow update', 0

  go

  reconfigure with override

  go

  -- more permissions errors followed

  GRANT EXECUTE ON [dbo].[sp_add_job] TO [RSExecRole]

  GO

  GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [RSExecRole]

  GO

  GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [RSExecRole]

  GO

  GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [RSExecRole]

  GO

  These fixes resulted in the Report Server subscriptions running without re-installation, I would also be concerned whether the Model database collation sequence changes would allow a re-install?.

  Matt

  MattF