Linked-Server stored procedure data missing in SSRS

Question

I am building a report in SSRS using Visual Studio 2010 Design view. I am using a stored procedure as my data set. When I run out the stored procedure in SQL Server, it runs fine. When I run it out in preview, I am missing some columns.

In the stored procedure, I join a table that lives on a linked-server through a synonym. These seem to be the missing columns in the preview.

Is there a known issue where linked-server data will not get returned in SSRS even if it compiles in SQL Server?

Answer 1

This is probably due to permissions if you have the linked server set to execute as the context of the current user - it will use the credentials of the Data Source in SSRS.

Is your data source configured to use a specific user? Try running the sproc as that user?

-- Edit based on Comments --

It appears the issue is probably with the linked server authentication. There are a few ways round this;

• Set up Kerberos pass-through authentication so you can tick 'Be made using the login's current security context' in the linked server properties https://blogs.msdn.microsoft.com/farukcelik/2008/01/02/how-to-set-up-a-kerberos-authentication-scenario-with-sql-server-linked-servers/ https://www.databasejournal.com/features/mssql/article.php/3696506/Setting-Up-Delegation-for-Linked-Servers.htm
• Use a SQL (not Windows) account on the remote server by ticking 'Be made using this security context' and entering the sql accounts details
• Use a scheduled job of some kind to transfer the data the sproc returns over to one server so that linked server connection is not required
• Change the report to query both data servers separately (as two different data sources and data sets), then join the results within the report (this is a bit fiddley and not really recommended)