Karl_mark
asked on
SSRS Legacy Report Running with no Data Source?
I've taken over the SSRS reports in our organisation and have the responsibility of developing new reports. I can do this (although not as well as the previous incumbent coming as I do from more of an SQL background).
Anyway, I'm having a problem with login credentials for some reports I've developed; we have a bespoke solution provided by our application vendor which passes credentials through to the reporting server. For some legacy reports this works fine and the reports open. However, the new reports I have created prompt for an SSRS login, UNLESS I have already opened one of the reports that does work. It seems that running a report that does work stores the credentials meaning that subsequent report requests do not require SSRS login.
So, what I would like to do is figure out why a working report does not require login credentials and see if I can replicate the setup in my new reports. Looking at one of the reports in BIDS, I can see that there is no data source shown, either embedded or shared. Obviously the report will not run in BIDS. However, the same report runs perfectly happily in Report Builder 3.0. I can't see any connection strings hidden anywhere, no parameters or variables appear to be set up, so I'm just wondering how it is possible for that report to work with no data source?
For information, here is a screenshot of the report in Report Builder:
And the Report results:
Anyway, I'm having a problem with login credentials for some reports I've developed; we have a bespoke solution provided by our application vendor which passes credentials through to the reporting server. For some legacy reports this works fine and the reports open. However, the new reports I have created prompt for an SSRS login, UNLESS I have already opened one of the reports that does work. It seems that running a report that does work stores the credentials meaning that subsequent report requests do not require SSRS login.
So, what I would like to do is figure out why a working report does not require login credentials and see if I can replicate the setup in my new reports. Looking at one of the reports in BIDS, I can see that there is no data source shown, either embedded or shared. Obviously the report will not run in BIDS. However, the same report runs perfectly happily in Report Builder 3.0. I can't see any connection strings hidden anywhere, no parameters or variables appear to be set up, so I'm just wondering how it is possible for that report to work with no data source?
For information, here is a screenshot of the report in Report Builder:
And the Report results:
ASKER
Thanks Icohan. The setup is as follows:
1. User credentials are passed from third-party app to SSRS allowing access to server.
2. Data source for reports is then connected via a SQL user account.
The reports that I have developed use a shared datasource held on the reporting server and log in using the SQL user account. I'm not sure where the other reports are connecting to SQL; I don't have access to the reporting server itself other than the logfiles (which don't reveal much when the user is prompted for a password) so I'll have to go through the DBA to find out if there are credentials held on the server. The problem is that the dba has also changed since the reports were initially set up (of course! Nothing is ever easy!).
Interestingly, I downloaded the Data Set from Reporting Services and when I opened it in Report Builder I was prompted for credentials. That gives me something to chew on!
Thanks for getting me thinking Icohan; I'll report back on what I find.
1. User credentials are passed from third-party app to SSRS allowing access to server.
2. Data source for reports is then connected via a SQL user account.
The reports that I have developed use a shared datasource held on the reporting server and log in using the SQL user account. I'm not sure where the other reports are connecting to SQL; I don't have access to the reporting server itself other than the logfiles (which don't reveal much when the user is prompted for a password) so I'll have to go through the DBA to find out if there are credentials held on the server. The problem is that the dba has also changed since the reports were initially set up (of course! Nothing is ever easy!).
Interestingly, I downloaded the Data Set from Reporting Services and when I opened it in Report Builder I was prompted for credentials. That gives me something to chew on!
Thanks for getting me thinking Icohan; I'll report back on what I find.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Icohan. I'll take a look at that Diagnostics Tool; it looks extremely useful in the screenshots!
I haven't found the solution to my problem but am going down the route of having the third-party app developer look at the authentication and doing some tweaks.
I haven't found the solution to my problem but am going down the route of having the third-party app developer look at the authentication and doing some tweaks.
These credentials are used by the report server to make a data connection to the external data source. For some types of data sources, credentials can be stored securely on the report server. These credentials enable other users to run the report without providing credentials for the underlying data connection."
https://msdn.microsoft.com/en-CA/library/ms160330(v=sql.105).aspx
For the SSRS reports that don't require to login most likely the credentials are stored within the report under security option when they were created/compiled/deployed in BIDS.
https://msdn.microsoft.com/en-CA/library/ms160330(v=sql.105).aspx