Tuesday, February 12, 2013 8:24 PM
I have a situation in which I have a SSRS Report that uses a Stored Procedure that Executes on a Linked Server. All of my Stored Procedures are working fine within SQL Server Management Studio with my security credentials. When I attempt to preview the report within Microsoft Visual Studio using SSRS, it spins and spins and spins and I never get a result back. I know it executes in less than 20~25 seconds running it thru SQL Server Management Studio. Does SSRS somehow need permissions to run against this Linked Server? I have made sure that "RPC" and "RPC Out" have both been set to "True" on the Linked Server Properties. Is there something else I need to concern myself with here? Some other Security Flag that I'm not sure about?
Thanks for your review and am hopeful for a reply.
Tuesday, February 12, 2013 8:43 PM
In that case, you need to define a linked server (on your SSRS box pointing to your destination SQL box).
You need to grant it data access, RPC and RPC out.
Plus, check that the linked server login has the right to log to the destination database, execute a stored procedure there and read all the necessary data.
Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu
Tuesday, February 12, 2013 8:50 PMI forgot to add that Linked Server as a Data Source within SSRS!!!