January 25, 2021 at 7:48 pm
I am working on troubleshooting an issue with the report server. I am able to view the report from the browser, however; when I am trying to manage the subscription, I get an error.
This is error from the log file
'library!ReportServer_0-2!e2c!01/25/2021-12:46:38:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.;
library!ReportServer_0-2!e2c!01/25/2021-12:46:38:: e ERROR: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
I am able to ping the SQL server from the report server. I am an admin on that test server where this report server points to, even the service account has admin rights. I even for the sake of it, granted execute permission on the object mentioned in the error log but still no luck. Any help is highly appreciated.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 25, 2021 at 9:00 pm
Did you restart the reporting service after granting the elevated rights? Verify permissions on the accounts NT SERVICE\SQLSERVERAGENT and NT SERVICE\MSSQLSERVER.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 25, 2021 at 9:11 pm
The error can crop up, and often with subscriptions, when you have the RSExecRole not configured correctly. Review the following documentation - there is a script at the end of the article which can help. You might be missing the role, missing permissions for the role or it's just messed up:
Sue
January 26, 2021 at 4:02 pm
This role doesn't exist on Dev and Prod but I am able to manage the subscription. However, I am going to create this role as well as grant permissions
NT**** already have admin rights on that box.
1 more thing I might add if it helps. We recently migrated SQL Server from old hardware to a new hardware. SQL Server still runs on 2016 Enterprise. Reporting server was never updated. But again, Dev and Prod are working without any issues....
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 26, 2021 at 4:24 pm
If the roles don't exist and things seem to mostly work, permissions are likely fairly messed up. As the documentation indicates, the role is in master and msdb and the Report Server service account is a member of the roles. When you mention which accounts are admins, I'm not sure if you are referring to sysadmins in SQL Server or administrators in Windows. In either case, the Report Server service account does not need to be an member of either of those. You may want to go through the permissions and set them up as the documentation explains. The permissions all get configured during the initial setup and any changes to the service account needs to be done using the Report Manager Configuration Manager. This ensures the correct permissions stay in place.
Sue
January 27, 2021 at 6:13 pm
I followed this RSExecRole document, recreated the role, granted all necessary permissions, restarted reporting server but getting the same error. I have admin rights on the server. The service account, reporting service runs under has sa rights on sql server instance.
When I looked at the error message, I see mssqlsystemresource as a database. Where did that come from? See error message below.
ERROR: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
I am not sure who else or which account will need elevated permission? I don't' mind reconfiguring reporting server services again but what will happen to old reports? I don't want to lose them.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply