How to resolve RS error "This may be due to a connection failure, timeout or low disk condition within the database."?

Question

After migrating the ReportServer database from 2014 to 2019 SQL Server Standard, I ran the Report Server Configuration Manager (on the same box as the SQL Server), Database --> Change Database --> Choose an existing report server database --> select the ReportServerTest from the drop down list using LocalSystem as Username and Authentication Type is set to Service Credentials. The Service Account is set to LocalSystem. The NT AUTHORITY\SYSTEM and NT SERVICE\SQLServerReportingServices are both DB_Owner of the ReportServerTest and ReportServerTesTempDB databases. These two accounts is also in the Local Administrator's group. However, I keep getting the following error when access the Web Service URL.

An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The wait operation timed out

I can create a new database and it works fine. However, this error only shows up when I selected the existing ReportServer Database that I restored it from the 2014 SQL Server Enterprise Edition. I've looked at this thread but it does not work in my situation.

Answer 1

If you're running in to this error and having an impossible time figuring it out (like I did), the ultimate solution was to follow the procedure here: https://learn.microsoft.com/en-us/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-ver16

The issue in my case was missing permissions on the RSExecRole role on the Master and MSDB databases. Short version is you can either make the role if it doesn't exist, or modify it's permissions if it does. Here's the SQL to make it:

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];
GOa