Hi, We have updated our Power BI Report Server from January 2023 to September 2023, email delivery data-driven subscriptions (pageinated reports) were working, but not now!
After the update, reports displayed properly. I could download and upload report definitions after making changes.
There was an issue with regular email subscriptions not triggering but I found references to permission problems on the MSDB and Master databases for SQL Agent and granted them. Errors like this:
System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_help_category'
after fixing that, then had this one;
System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs'
Permissions granted:
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
use master;
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [RSExecRole]
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [RSExecRole]
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [RSExecRole]
GRANT EXECUTE ON master.dbo.sp_helplogins TO [RSExecRole]
After the permission updates the email subscriptions started working. Test of File share type subscriptions were also successful.
So far I have tested my data-driven subscription, the data source\dataset SELECT - it still works for the user defined. I can't seem to see anything else in SQL Server that may be the problem.
Any ideas where to look next? Should we just rollback the server and try the update again? Completely uninstall and reinstall?
Thanks
Gord
Error log details:
------------------------------------------------
schedule!WindowsService_2!5d0!10/25/2023-14:30:09:: i INFO: Handling event with data: TimeEntered: 10/25/2023 14:30:09, Type: Event, EventType: TimedSubscription, SubscriptionID: 992cbf9a-d7ec-4456-91da-a7087a3c6306.
library!WindowsService_2!5d0!10/25/2023-14:30:09:: i INFO: Schedule 037d0970-8df9-4bc0-9006-54effd424f0c executed at 10/25/2023 14:30:09.
schedule!WindowsService_2!5d0!10/25/2023-14:30:09:: i INFO: Creating Time based subscription notification for subscription: 992cbf9a-d7ec-4456-91da-a7087a3c6306
library!WindowsService_2!5d0!10/25/2023-14:30:09:: i INFO: Schedule 037d0970-8df9-4bc0-9006-54effd424f0c execution completed at 10/25/2023 14:30:09.
schedule!WindowsService_2!de8!10/25/2023-14:30:09:: i INFO: Handling event with data: TimeEntered: 10/25/2023 14:30:09, Type: Event, EventType: DataDrivenSubscription, SubscriptionID: 992CBF9A-D7EC-4456-91DA-A7087A3C6306.
library!WindowsService_2!1100!10/25/2023-14:30:09:: i INFO: Handling data-driven subscription 992cbf9a-d7ec-4456-91da-a7087a3c6306 to report /SubscriptionTesting/SSRSReportTesting, owner: (xxxADxxx)\(xxxUserxxx), delivery extension: Report Server Email.
library!WindowsService_2!1100!10/25/2023-14:30:09:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
dataextension!WindowsService_2!1100!10/25/2023-14:30:09:: i INFO: Opening SqlConnection with ClientConnectionId f3c89dba76ae4b07a895ccd8d1eae9ba
library!WindowsService_2!694!10/25/2023-14:30:09:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
notification!WindowsService_2!694!10/25/2023-14:30:09:: i INFO: Handling subscription 992cbf9a-d7ec-4456-91da-a7087a3c6306 to report SSRSReportTesting, owner: (xxxADxxx)\(xxxUserxxx), delivery extension: Report Server Email.
emailextension!WindowsService_2!694!10/25/2023-14:30:09:: e ERROR: Error sending email. Exception: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification). Additional Information:
notification!WindowsService_2!694!10/25/2023-14:30:30:: e ERROR: Error occurred processing subscription 992cbf9a-d7ec-4456-91da-a7087a3c6306: Failure sending mail: Object reference not set to an instance of an object.Mail will not be resent.
notification!WindowsService_2!694!10/25/2023-14:30:30:: i INFO: Notification f85c3820-d88a-4b79-92e7-bb9bd16caaed completed. Success: False, Status: Failure sending mail: Object reference not set to an instance of an object.Mail will not be resent., DeliveryExtension: Report Server Email, Report: SSRSReportTesting, Attempt 0
