September 9, 2016 at 9:25 am
We are running SQL Server 2012 EE SP3.
In the below tests, the setup on the Report Manager is as follows: I created a Datasource (I guess this is considered a Shared Datasource), uploaded the Datasets, linked each Dataset to the Data Source (created the Shared Datasets), uploaded the Reports, and linked each Report Dataset to the Shared Datasets in SSRS (created Shared Dataset Reference.) For Site Security, I added the users's Windows Login to the System User View Role and for Folder Security I added the users's Windows Login to the Browser Role. (Site and Folder Security appears to be working fine.)
I am not sure what login to use when selecting the "Credentials stored securely in the report server" Option. I have created a sql login called REPORTUser_Example and made it a member of the two database user roles (for example, DB_Read, DB_Exec). The actual report (.rdl) file is using a generic sql login to connect via the report's own datasource. This Generic_SQL_Login is also a member of the two database user roles (for example, DB_Read, DB_Exec).
The following are the results of two tests by a user from her laptop (not on the report server) using the Report Manager URL and then running the report:
--TEST1
With the "Credentials stored securely in the report server" Option selected, using the REPORTUser_Example sql login and with the User's Windows Login disabled in SQL Server (just to take it out of play). The Report executed successfully for the user.
--TEST2
With the "Credentials stored securely in the report server" Option selected, using the Generic_SQL_Login and with the User's Windows Login disabled in SQL Server (just to take it out of play). The Report executed successfully.
So, I am confused as to which sql login, the REPORTUser_Example used in the Report Manager Datasource or the generic sql login within the datasource within the actual report (.rdl) file, is being used when the user runs the report from her laptop via Report Manager while her Windows Login is disabled in SQL Server. Because I am using a Report Manager Datasource and not the datasouce embedded in the actual report, does this mean the user specified in the "Credentials stored securely in the report server" Option on the Report Server is being used to execute the report? I would like to be able to determine what user is actually connecting to the database?
Is there a view or table, that would indicate what user is being used to actually execute the report and/or connect to the database via the database?
Related question, does the Report Manager's datasource name have to be the same as the embedded datasource name within the actual report file?
Thanks in advance.
September 9, 2016 at 12:58 pm
So, I am confused as to which sql login, the REPORTUser_Example used in the Report Manager Datasource or the generic sql login within the datasource within the actual report (.rdl) file, is being used when the user runs the report from her laptop via Report Manager while her Windows Login is disabled in SQL Server. Because I am using a Report Manager Datasource and not the datasouce embedded in the actual report, does this mean the user specified in the "Credentials stored securely in the report server" Option on the Report Server is being used to execute the report? I would like to be able to determine what user is actually connecting to the database?
Is there a view or table, that would indicate what user is being used to actually execute the report and/or connect to the database via the database?
Related question, does the Report Manager's datasource name have to be the same as the embedded datasource name within the actual report file?
Thanks in advance.
You have two different credentials in use - they can be the same account but two different processes.
Data source credentials are used to connect to the data sources where ever they may be to get the data for the report. This connects to the databases for the data.
Then the report credentials themselves are those used to connect to the report server and generate the report. Connects to the Report Server for the report.
The two can be different, can be the same. Often it's different accounts that you would want to have different access to the source data.
The credentials used to execute a report can be found in the Execution log in the ReportServer database. Look at the username column in the ExecutionLog3 view in your ReportServer database.
Sue
September 19, 2016 at 9:51 am
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.)
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.
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.
Thanks in advance.
September 19, 2016 at 1:37 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply