Unable to manage subscription.

  • 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]

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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:

    Create the RSExecRole

    Sue

     

  • 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]

  • 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

  • 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.

    • This reply was modified 1 month ago by  Syed_SQLDBA.

    "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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply