By Garth Jones
Not too long ago S. D’Souza @sdsouza78 asked me the following:
“Is there an SCCM/SSRS article which explains the report execution flow starting at SCCM console ending with SQL Report displayed?”
By way of background, this is the third post in which I am answering all of his questions about the SQL Server Reporting Services (SSRS) report execution workflow process. These are my previous posts on this topic: How Does the ConfigMgr SSRS Report Execution Workflow Operate? and Which Security Accounts Are Used within ConfigMgr SSRS Report Execution?
I’m not a fan of executing SSRS reports from the System Center Configuration Manager (ConfigMgr) console because accessing reports from the ConfigMgr console requires a lot of extra processing to perform this task. Plus, why would you only open the console to view a report?
In this blog post not only will I show you the execution work flow from the ConfigMgr console, but I will also compare the differences between using the ConfigMgr console and the SSRS web interface.
I’ll use the same scenario as the one from my previous blog posts:
· Morgan wants to execute a report called, Overall Missing Software Update Status by Classification.
· CM12R2-CM6 is a ConfigMgr 2012 R2 Server with SQL 2012 and SSRS.
· Both SSRS and SQL are using the default instances.
· Morgan is NOT a ConfigMgr 2012 R2 administrator.
· All reports are Role-Based Administration (RBA) reports.
· My example will start with Morgan sitting at his Windows 7 desktop.
The steps below will give you an idea about how the report execution workflow from the ConfigMgr console works:
· Morgan launches the ConfigMgr console.
· ConfigMgr (CM) SMS Provider checks to see if Morgan has access to ConfigMgr and his permissions.
· Only folders Morgan has access to will be displayed.
· Morgan browses to Monitoring > Reporting> Reports.
· Once Morgan selects the Reports folder, he queries the SSRS server (http://cm12r2-cm6/reportserver) for all reports.
· His permissions are checked on the SSRS server to confirm that he has access to each report.
· A list of reports he has access to is displayed within ConfigMgr console.
· He executes the Overall Missing Software Update Status by Classification report.
· At this point the report process behaves exactly like it does when accessing it from the SSRS web site, except the results are viewed in ReportViewer.
· Each prompt is executed in the following order:
1. An internal (hidden) prompt is executed to determine who is accessing the report. This is a native SSRS feature.
2. A second internal (hidden) prompt is executed to determine the user’s security role within ConfigMgr.
3. All remaining prompts are executed.
· All datasets/SQL queries are executed against the ConfigMgr database before the report is displayed. Results are viewed in ReportViewer in the ConfigMgr console.
The flowchart below gives a visual representation of the ConfigMgr console process above.
Accessing a report via the ConfigMgr console creates a number of ways in which ConfigMgr is made to work harder than if you were to access a report through the SSRS web interface.
Here’s a list of the extra overhead as it occurs:
1. Accessing the SMS Provider to check permission to the ConfigMgr console.
2. Gathering a list of ALL SSRS reports the user can access.
3. Checking permissions for each and every report to ensure that the user has permission to see it before it gets displayed.
If you access a report through the SSRS web interface, the SMS Provider does NOT check permission to the ConfigMgr console because it is not needed, it only gathers a subset of the reports the user wants to see, and it does NOT need to check the permissions for each and every report.
S. D’Souza, I hope this helps to answer your questions about how the SSRS report execution workflow behaves from the ConfigMgr console to the ConfigMgr database.
If you have any questions, please feel free to contact me @GarthMJ.