Thus, we have the SSRS SQL 2008R2 server (AServer) on which some reports have been deployed. The report in question runs on the AServer server and runs the stored proc on BServer to retrieve its dataset. When starting a report, it gives an error with the following
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'ReportData'. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object 'pSomeStoredProc',
database 'SomeDb', schema 'dbo'.
My biggest problem is that it does not tell me at what level permission was denied. Does the account running the report on AServer have no credentials to upgrade to BServer? Could he get into BServer but still not have perms to run this exact sproc? Are user privileges an actual view of a report delegated by BServer (I saw how similar Voodoo happens with IIS)?
What I tried and failed:
My starting point was to check the SQL logs in msdb to see where authorization could fail. No luck was found when wading through these magazines. I was expecting something like "INTRA \ sqlserviceaccount does not have sufficient permissions to run SomeDb.SomeProc."
My second goal was to make sure that the account running the SQL reports was also listed on the remote server. This is not so, so I found it in AD and added. I also added it to the db in question as db_datareader and db_datawriter (<== not necessary).
The third attack was to have the RSExec role on the remote server. This did not work either, and I was kinda lost in what I actually did differently than expanding the configuration of my server.
Any help is greatly appreciated.