How to ensure that all the SSRS features are working while using Always On Availability Groups OR have an RSExecRole as a default

Introduction

This blog post addresses SSRS on Always On and RSExecRole. To make sure I’m staying laser focused on this topic, I need to cover some fundamentals.

SSRS installation (SQL Server 2017 and later)

Starting from SQL Server 2017, Microsoft decided to separate SSRS from a SQL Server installation container (ISO). Keeping the pros and cons of this change aside, that means that Microsoft decided some customers would not need SSRS, and adding a separate step to install it would effectively block an accidental “next-next” type of installation. If a separate SSRS installer is triggered, it will add all the necessary SSRS plumbing to SQL Server database engine, such as ReportServer and ReportServerTemp databases to support SSRS, database roles. It will also add many other pieces that are not related to SQL Server, such as web service, web application, etc…

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-reporting-services?view=sql-server-ver15

Always On Availability Groups (a.k.a. AG)

Always On Availability Groups is the latest and greatest Microsoft HADR (High Availability and Disaster Recovery) solution to keep SQL Server running in case of failure. AlwaysOn Availability groups allows to enroll databases into a group that would be kept in sync between the active server (a.k.a. primary, supporting both read and write) database and passive (a.k.a. replica, supporting read only) databases.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver15

SSRS Subscriptions

SSRS subscription is a saved configuration that is designed to simplify report rendering and delivery. You choose a report, format, schedule and a method of delivery, and suddenly your users are more self sufficient and you have more free time.

https://docs.microsoft.com/en-us/sql/reporting-services/subscriptions/subscriptions-and-delivery-reporting-services?view=sql-server-ver15

Problem

Once you have mission critical databases included in availability groups it makes a total sense to add both ReportServer (storage for datasources, datasets, reports, etc…) and ReportServerTempDB databases (TempDB for SSRS)  as well. This way in case of a disaster recovery, your data-sources (OLTP databases for example) and reporting platform (SSRS) are up and running at the same time without any manual steps.

With Always On Availability Groups (a.k.a. AlwaysOn AG), both the data and schema are constantly (synchronous mode) or almost constantly (asynchronous mode) in-sync, but there is one user requirement. Always On requires same logins (stored in master that is not part of AG) and SQL Server Agent jobs (stored in msdb that is not part of AG). While it might look like that adding ReportServer and ReportServerTemp databases to AG would be enough to have SSRS fully supported for HADR, unfortunately that is not entirely true.

With both SSRS databases on AG, most of the functionality is available, so SSRS Report Portal will serve reports (records from the datasource, datasets, and catalog tables) and obey the security mechanism (users, policies, role tables), but SSRS subscription will suddenly stop working (despite the subscription table being in-sync). Any existing SSRS subscriptions will stop working regardless if SSRS GUID job (unfortunately, Microsoft never got to make SSRS jobs user-friendly) is enabled or not. Trying to create a new SSRS subscription will be greeted with a very cryptic error message (Fig #1).

Little known fact about SSRS: Outside of ReportServer database, SSRS is not only dependent on jobs and logins, but also on a custom database role called RSExecRole that is created by the SSRS installer. This role needs to exist in both master and msdb and since both of their databases are normally not in AG (per server and not in-sync) and SSRS is not actually installed on the replica, SSRS subscriptions would no longer work.

Solution

The solution is fairly easy and will involve following Microsoft documentation that is designed for a scenario of moving SSRS from one server to another.

https://docs.microsoft.com/en-us/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-ver15ecrole?view=sql-server-ver15

The following code will create RSExecRole database role in both master and msdb databases and will grant all the necessary permissions needed for SSRS subscription functionality.

Source code

USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
    CREATE ROLE [RSExecRole];
END
GRANT EXECUTE ON dbo.xp_sqlagent_enum_jobs TO [RSExecRole];
GRANT EXECUTE ON dbo.xp_sqlagent_is_starting TO [RSExecRole];
GRANT EXECUTE ON dbo.xp_sqlagent_notify TO [RSExecRole];
GO
USE msdb;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
    CREATE ROLE [RSExecRole];
END
GRANT EXECUTE ON dbo.SP_ADD_CATEGORY TO [RSExecRole];
GRANT EXECUTE ON dbo.SP_ADD_JOB TO [RSExecRole];
GRANT EXECUTE ON dbo.SP_ADD_JOBSCHEDULE TO [RSExecRole];
GRANT EXECUTE ON dbo.SP_ADD_JOBSERVER TO [RSExecRole];
GRANT EXECUTE ON dbo.SP_ADD_JOBSTEP TO [RSExecRole];
GRANT EXECUTE ON dbo.SP_DELETE_JOB TO [RSExecRole];
GRANT EXECUTE ON dbo.SP_HELP_CATEGORY TO [RSExecRole];
GRANT EXECUTE ON dbo.SP_HELP_JOB TO [RSExecRole];
GRANT EXECUTE ON dbo.SP_HELP_JOBSCHEDULE TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_verify_job_identifiers TO [RSExecRole];
GRANT SELECT ON dbo.syscategories TO [RSExecRole];
GRANT SELECT ON dbo.sysjobs TO [RSExecRole];
GO

Of course it would be nice if RSExecRole database role and permissions were created by default, regardless of the SSRS installation, by making it part of the “system” objects. That would be a small price to pay for a happier DBA. Wouldn’t it be nice? As the Beach Boys would sing it, “you know its gonna make it much better”!