In attempting to create Report subscriptions in our new SharePoint 2013 production environment, we received and error:
The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
Initially I thought that we just needed our DBA to grant the reporting services execution account EXECUTE rights on the extended stored procedure noted. There was a little more to the solution.
The Reporting Services Execution Account needs to have access to the SQL Server Agent but the access is granted through the RSExec role. We noticed that this role existed on the reporting services databases but wasn't on any of the system databases (e.g. master, msdb). SharePoint 2013 makes this easy to resolve! You can download a script that incorporates the execution account, adds the roles, and grants all of the proper permissions.
Simply navigate to the Reporting Services Application in Central Admin and click on Provision Subscriptions and Alerts:
If you have database access, Open the file or Save it locally and send it to your DBA:
Run the script on your SharePoint database server:
After the script is run, the ability to create Reporting Services Subscriptions from SharePoint 2013 should work like a charm!
Note: If the reporting services managed account does not exist in all of your environments, you should download the script in each one (Stage, QA, Production, etc.) and run them separately.
The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
Initially I thought that we just needed our DBA to grant the reporting services execution account EXECUTE rights on the extended stored procedure noted. There was a little more to the solution.
The Reporting Services Execution Account needs to have access to the SQL Server Agent but the access is granted through the RSExec role. We noticed that this role existed on the reporting services databases but wasn't on any of the system databases (e.g. master, msdb). SharePoint 2013 makes this easy to resolve! You can download a script that incorporates the execution account, adds the roles, and grants all of the proper permissions.
Simply navigate to the Reporting Services Application in Central Admin and click on Provision Subscriptions and Alerts:
Click on the Download Script button:
Run the script on your SharePoint database server:
The script obtains the account using a user guid. You can verify that this account is your Reporting Services Execution Account by issuing a SELECT against the SUSER_SNAME function:
After the script is run, the ability to create Reporting Services Subscriptions from SharePoint 2013 should work like a charm!
Note: If the reporting services managed account does not exist in all of your environments, you should download the script in each one (Stage, QA, Production, etc.) and run them separately.
Excellent! I'm happy i happened upon this site rather than just applying the first "solution" i found!
ReplyDeleteHi Steve! thanks for this post! one question here: you have "SQL Server Agent state cannot be determined" - is that OK? Should not it read that SQL Agent is online or smth alike? Doesn't it mean there is some issue in communication between SQL and SP server? some ports blocked maybe?
ReplyDeleteWhere do you see that message? It would probably be an issue on your SQL Server box. It probably just needs to be started.
Deletei see that message on your screenshot in this post. the picture right under "Click on the Download Script button:" line - in the upper right side of the pic. hence are my questions - is that OK? Should not it read that SQL Agent is online or smth alike? Doesn't it mean there is some issue in communication between SQL and SP server? some ports blocked maybe?
DeleteYeah. Once you run the script then SP will be able to get the status and state of the agent.
DeleteI ran the script and it says it completed successfully but I'm still getting "SQL Server Agent state cannot be determined" - I'm about to pull my hair out...any ideas?
ReplyDeletedid you try rebooting your SharePoint servers?
DeleteYes :) this was the answer in case anyone else runs into the issue:
DeleteAfter setting the SPN and activating delegation, I still got this Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' -message.
So I decided to check from the IIS management console what authentication providers are set for SP Central Adm. To my surprise, the NTLM -option was on top of the list. As soon as I changed the Negotiate-option as first provider,I got this: SQL Server Agent is running -message
Source: https://social.technet.microsoft.com/Forums/office/en-US/b5d025fb-7b98-4e84-9c51-bdb4a1c25a03/reporting-services-2012-for-sharepoint-and-sql-server-agent-subscriptions-and-alerts?forum=sharepointadminprevious