September 30, 2014 at 12:39 pm
The report is using storedprocedure from report database. The login which execute the report has databaseowner permission for that database. But the storedprocedures has to read the data from another server. The login has not created where the storedprocedure is getting data. Do we need to create that login under the server where the data is coming for the sps or we don't need?
September 30, 2014 at 2:02 pm
How is your stored procedure accessing the data on the other server? Did you setup a linked server for that? If so: the stored procedure will read the data from the other server using the authorization defined in the linked server connection.
September 30, 2014 at 4:51 pm
Yes. Through linked server
October 1, 2014 at 1:19 am
HanShi (9/30/2014)
Did you setup a linked server for that? If so: the stored procedure will read the data from the other server using the authorization defined in the linked server connection.
So it should work. Did you test the report? Did you execute the stored procedure using the same credentials as in your report? Did you encounter any errors?
What have you tried and tested uptill now? What are the results of your effort sofar?
October 1, 2014 at 1:34 pm
In visual studio, I am getting the preview but when I upload the same rdl to the report manager, it is showing that an error has occured during report processing. (rsprocessingAborted)
Query execution failed for dataset ' '(rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server or enable remote errors
October 1, 2014 at 1:49 pm
Look in the error log of the SQL instance where the linked server is connecting to. Are there any errors at the moment you ran your report?
This could be related to a "double hop" problem. A short description and needed setup for this is on http://www.sqlservercentral.com/blogs/sqlsandwiches/2011/06/20/double-hop-of-doom/.
October 1, 2014 at 2:02 pm
No errors in visual studio but from report manager only.
October 1, 2014 at 2:44 pm
ramana3327 (10/1/2014)
No errors in visual studio but from report manager only.
I don't mean look for errors in VS or report manager. Look for errors in the error log on the instance the linked server is connecting to. Connect to this instance using SQL Server Management Studio. Look for the "SQL Server Logs" under "management".
October 1, 2014 at 4:08 pm
I am assuming that linked server problem. When I test it is showing that Test connection successful but in the production the linked server created using the security option be made using the security connection ( 4th option) but when I created that I use be made using the the login's current security context ( 3rd option)
Might be that is the reason I am getting error msg in report manager
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply