SSRS 2017 subscriptions stopped working after installing KB4583457

Question

2 weeks ago we installed KB4583457 on SQL Server 2017 Standard (14.0.3294.2). For some reason, it broke something in the system databases of SQL Server and the server became unavailable. We had to uninstall this update to resolve the issue. It helped and everything, except SSRS subscriptions, started working fine again. But SSRS subscriptions behave weird since then. No email has been sent and if we try to trigger any subscription manually (using [Run Now] button), it doesn't work, or to be more precise - it gets stuck in 'Running' state.

I realize something's probably wrong with our instance of SQL Server/SSRS. But I'd really appreciate if someone can suggest an idea or advise what else we can check. Here's what we've checked so far:

Logs of SSRS in Program files - nothing there.
Windows events logs - nothing relevant there.
SQL Configuration Manager - all services (except SQL Server Browser) are up & running.
Reporting Services Configuration Manager - nothing has changed and everything looks OK.
SSRS Web Portal - the reports work as expected, including the export to various formats.
SSRS API - no issues.
The history of SQL Agent jobs created by SSRS subscriptions - all jobs continue to run by schedule. So the history of SQL agent jobs is not consistent with what is displayed on SSRS portal in the 'Last run' column of any subscription.
The owner of all SQL agent jobs linked to SSRS subscriptions is 'NT SERVICE\SQLServerReportingServices', which has db_owner permissions to ReportServer and ResportServerTemp databases.
ReportServer database - we used the script bellow to find any clue that might point us in the right direction. But it doesn't show any useful error messages.

select 
    c.Name as Report,
    s.LastRunTime,
    s.SubscriptionID,
    s.*
from  [ReportServer].[dbo].[Subscriptions] as S
    inner join [ReportServer].[dbo].[Catalog] as c
        on c.ItemID = s.Report_OID
order by s.LastRunTime desc

If someone has any other ideas, please feel free to share.

Since that was a security update, I would try setting the user on your data sources to someone else and back to what they were. Same for the agent jobs. Might overwrite bad data in some xml config somewhere. — Russell Fox
Have you tried running SQLServer2017-KB4583457-x64.exe /?, on another machine or VM of course, to see which parameters it supports? A lot of MS patches can be extracted to a folder so that you can inspect the files and configuration they include. — AlwaysLearning

Alexander Alexander · Accepted Answer · 2021-02-04T07:26:13

Thanks, it turned out the issue was related to the permissions of NT Service\SQLServerReportingServices user. The screenshot below shows what had to be updated - Execute permission on dbo.sp_verify_job_identifiers stored procedure. Hopefully it will save someone hours or even days.

$Required changes of NT Service\SQLServerReportingServices permissions on sp_verify_job_identifiers$

It's important to note we would be able to identify the root problem earlier if we had looked through the Logs of SSRS in Program files folder not only for the current day but also the old ones.

C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles

SSRS 2017 subscriptions stopped working after installing KB4583457

1 Answers