Recently S. D’Souza @sdsouza78 sent me a tweet asking: “Is there an SCCM/SSRS article which explains the report execution flow starting at SCCM console ending with SQL Report displayed?”
I’ll try to answer this question by saying right off the bat that you should ALWAYS access the reports from the SQL Server Reporting Services (SSRS) web interface.
Accessing reports from the System Center Configuration Manager (ConfigMgr) console requires a lot of extra processing to perform this task; more overhead, if you will. Plus, why would you only open the console to view a report?
In this blog post I will demonstrate the report execution workflow from the SSRS web interface to the ConfigMgr database.
I’ll write another blog post to show you the report execution workflow from the ConfigMgr console to the ConfigMgr database, but I can tell you now, that’s a much bigger flowchart!
In this example, I will use ConfigMgr 2012 R2, but everything I show you also applies to ConfigMgr Current Branch too.
This is the scenario I’m going to use for my example:
- Morgan wants to execute a report called, Overall Missing Software Update Status by Classification, with the Home > ConfigMgr_CM6 > Enhanced Web Reporting > Software Updates folder on a server called CM12R2-CM6.
- 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 SSRS web interface report execution workflow is defined as follows:
- Morgan launches IE.
- Morgan browses to https://cm12r2-cm6/reports.
- Before Morgan can see the SSRS website, SSRS determines if he is allowed access to the site. If he does not have permission an error message is generated. If he does have permission, the process continues as follows.
- A list of folder(s) he has access to is displayed.
- He drills down on the ConfigMgr_CM6\Enhansoft\Software Updates folder.
- Once within the Software Updates folder, a list of reports he has access to is displayed.
- He clicks on the Overall Missing Software Update Status by Classification report.
- Each of the prompts below are executed in the following order:
1. An internal (hidden) prompt is executed in order to determine who is accessing the report. The results are stored within a variable. This is a native SSRS feature.
2. A second internal (hidden) prompt is executed in order to determine the user’s security role within ConfigMgr.
3. All remaining prompts are executed.
4. All datasets/SQL queries are executed against the ConfigMgr database before the report is displayed.
The flowchart below gives you a visual representation of the process above.
The following access denied error message is what you will see when a user does not have permission to access the ConfigMgr SSRS website.
Hopefully this blog post has shed some light on the SSRS web interface report execution workflow. In my blog post next week, I will look at which security accounts are used within the ConfigMgr SSRS report execution process. This question was also asked by S. D’Souza.