Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSRS Permissions to a Linked Server

Answered SSRS Permissions to a Linked Server

  • Tuesday, February 12, 2013 8:24 PM
     
     
    Sign In to Vote
    0

    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.

    PSULionRP


     

All Replies

  • Tuesday, February 12, 2013 8:43 PM
     
     Answered
    Sign In to Vote
    0

    Hi,

    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 PM
     
     
    Sign In to Vote
    0
    I forgot to add that Linked Server as a Data Source within SSRS!!!