HookSqlDba7 (9/19/2016)
Thanks Sue. Your response helped. I may be making this too confusing but just trying to trace the SSRS Report Manager datasource login during report execution.Sue: Data source credentials are used to connect to the data sources where ever they may be to get the data for the report.
My interpretation: This is the Report Manager datasource (in my example).
Sue: Report credentials themselves are those used to connect to the report server and generate the report.
My interpretation: This is configured in the SSRS Report Manager Site Security and Folder Security. (Right now, I am using windows logins but read where I could use AD Groups.)
Yes, there is the one set of credentials for accessing the Report Server and it's objects (folders, reports, data sources). And those you manage under Site Security and Folder security, etc.
That level of security is completely separate from the data source credentials.
You can have a user that has no access to any of the data directly through the database server but has access to all reports. So they get all of their data from the reports themselves. In this case, it's the data source credentials that has access to the data, not the user.
I executed queries against the Report Server Database Views (ExecutionLog, ExecutionLog2, and ExecutionLog3). There is a UserName Column in each view. It appears to return the windows login of the user executing the report from Report Manager. QUESTION 1 - Is there a way to see (via a view, table, log file), what login is actually connecting to the database via the Report Manager Datasource? (I am hoping to see the login specified in the "Credentials stored securely in the report server" Option within the SSRS Report Manager datasource definition.) By disabling the user's windows sql server login, I think I proved that it is not the user's windows sql server login connecting to the Report Manager Datasource. I am just trying to determine if something (view, table, log file) shows this to be true. I checked the three views above, Report Server Log Files, and the SQL Server Error Logs to no avail.
Your assumption is correct. A report user doesn't connect to the data source. The report user connects to the report server. Their credentials at this point are used for the login, to determine what objects they can access on the report server.
Once they request a report, it's the data source credentials that access the data and provide the data for the report.
The user name in the event logs is the user requesting the report so it has nothing to do with the data source for the report. You won't find anything in the SSRS log when a data source credentials are used to get data.
If the data source is pointing to a SQL Server database server, you would only see the logins based on the settings for login auditing. If you have that set to log successful and failed logins, then you would see the login. If you have just failed logins and the credentials for the data source logs in successfully and gets the data then no, you will not see the login.
If you want to capture those logins, you could trace that. If the report is run from Report Manager, the application name would be .Net SQLClient Data Provider
After reading and playing with this some more, I think I cleared up some of my original confusion regarding logins/datasources. Since, I am using the Data Source within SSRS Report Manager, this means that the datasource (using the generic sql login) within the actual report (.rdl) file, is NOT being used when the user runs the report from her laptop via SSRS Report Manager.
No in the example you gave, it will use the credentials in the Data Source. The user would be using whatever credentials you setup for her within Reporting Services to access the report server itself and view whatever folders and reports. In your example, once she runs a report, it will use the credentials defined in the data source. In this case it would use the generic SQL login you setup for the data source.
Access to the report server and data sources used by the reports are two different things. Often, you don't want users having any direct access to the data but provide access to the information they need through reports. So you set it up so they can access the reports but not the data itself. The data is obtained for them using the credentials of the data source. And they get access to run the report by granting those permissions on the report server itself.
Sue