March 1, 2012 at 4:54 am
Hi,
We have a SSRS environment where database is seperated by firewall and only tcp/1433 is open. None of the servers are members of a domain
SSRS is configured to run as NETWORK SERVICE, database connectivity is configured with a sql user account
Reports works great, but if I try to subscribe to one of the reports, I get the error: EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
I have found post that states the SSRS service credentials must be in RSExec role on the SQL server (eg. http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/444c3bab-985b-40a0-8362-2742df1a6577) Why is that?
The SQL user account is already in the RSExec role on the 4 databases. I also tried adding NT Authority\NETWORK SERVICE and BUILD IN\Administrators to the RSExec role but still fails.
I tried set the SSRS Execution credentials to local administrator credentials (which is the same credentials on both servers) but still the same error
i cannot find any requirement information for SSRS subscriptions anywhere. http://msdn.microsoft.com/en-US/library/ms155911(v=sql.90).aspx doesnt say anything?
Does this mean that our non-domain/DMZ environment cannot support subscription then?
Thanks!
March 2, 2012 at 7:18 am
Thanks - but I followed the article already.
I have opened a case with MS, so Ill keep this thread updated when I have any news
March 16, 2012 at 7:31 am
Okay the error was that the sql user, used for connecting SSRS and SQL database, was configured but not user mapped to the master and msdb
I ran this command
USE master
GO
EXEC sp_change_users_login 'update_one', 'api_ssrs', 'api_ssrs'
GO
USE masdb
GO
EXEC sp_change_users_login 'update_one', 'api_ssrs', 'api_ssrs'
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply