FAQ - SQL Reporting Services Integration with System Center Configuration Manager 2007 R2

[Yvette O'Meally]

First published on CloudBlogs on May, 14 2009

[Today's post is provided by Bhaskar Krishnan ]

At MMS 2009 we announced that, with the next release of Configuration Manager, SQL Reporting Services integration, introduced in Configuration Manager 2007 R2, will replace the existing ASP-based reporting feature. So I thought this would be a really good time to come up with a FAQ that would make your transition easier and also outline some caveats that you should be aware of.

FAQ

1) Will both the reporting points exist in future releases?

No - The ASP based reporting point will be deprecated in the next release and we are making more investments in integrating with SQL Reporting Services 2008. But as of Configuration Manager 2007 R2 release both the reporting points are supported and can co-exist on the same machine.

2) How do I transition to the new reporting services point?

a) Install Configuration Manager 2007 R2.

b) Check out the online documentation on how to configure the role and how to use the Copy Reports Wizard to convert and copy existing reports to SQL Reporting Services server.

3) Can the Copy Reports Wizard convert and copy all reports including custom reports authored in SMS 2003 and Configuration Manager 2007?

a) Out-of-box reports

All out-of-box reports shipped with Configuration Manager 2007 R2 should get converted successfully into SQL Reporting Services reports.

b) Custom reports (two caveats)

i) All custom reports that leverage temporary tables (as part of the SQL statement behind the report) will fail to convert since the wizard cannot correctly identify the report fields to be displayed as part of the report.

ii) All custom reports that leverage multiple SELECT statements (to incorporate multiple datasets within a report) and the data fields in the final report span across the multiple datasets, may be converted successfully to SQL Reporting Services reports (with a warning) but the runtime display for such reports is not guaranteed to show the correct data fields.

Known Issue

Some of the converted reports may not have report links since these are explicitly removed during the report conversion process. The reason these links are removed traces back to a fundamental way in which SQL Reporting Services handles report links.

An example would make this issue clearer. Let us say Report A links to Report B via parameters Param1 and Param2. Also let us say that Param1 and Param2 which are parameters for Report B2 take a valid set of values (basically these parameters offer a list of possible values for the users to select by running a SQL query). Quite often it so happens that Report A has blank values for these parameters in some of the rows and when you drill down from these rows to Report B, SQL Reporting Services throws an exception that the values for these parameters are not valid. This is because it matches the value passed from Report A to the list of plausible values by running the SQL Query behind that report parameter for Report B and finds that a blank value is not in the list of valid values. The legacy ASP-based reporting point handled this gracefully and would allow passing blank/null parameter values to the linked report. But SQL Reporting Services enforces a tighter restriction on what gets passed into the linked report to avoid any possibilities of SQL injection attacks.

A good example where this issue manifests is with reports that deal with status messages. Internally many of the reports define status messages to be a list of valid values (like 0=Information, 1=Warning, 2=Error etc.). If you look at any parent report that provides a high level view of say something like patch deployment status you may see that this report links to a drill-down for a machine specific report for patch deployment. Now it is quite likely that at some point there is no status message available for a particular machine (may be because the patch deployment was delayed for some reason) and that row shows up as blank in the parent report but if you drill down this report SQL Reporting Services will throw an exception that the blank value for status message ID is not acceptable.

Workaround: Run the linked report directly by passing the appropriate set of report parameter values.

4) What about custom reports created under new folders on the old reporting point site?

Yes - all custom reports can be migrated to the SQL Reporting Services server but when you run the Copy Reports Wizard, these custom reports are displayed under their respective report categories and not under their report folders. Basically the Copy Reports wizard classifies all the old reports based on report category and not on report folder.

5) Can I view and run my custom reports authored using SQL Reporting Services tools from the Configuration Manager Administration console?

Yes you can.

a) While configuring the reporting services point role a root folder is configured on the SQL Reporting Server under which all the Configuration Manager reports get deployed.

b) As long as all the custom reports authored using various SQL Reporting Services tools are deployed under the report folder configured in a) you will be able to view and run them from the Configuration Manager Administration console.

6) Are named report server instances supported with Configuration Manager 2007 R2 release?

With the R2 release there is no explicit way to specify a named report server instance anywhere. What this means is that we query WMI to retrieve the list of report server instances and pick the first instance returned by WMI.

Implication: If the report server machine has 2 instances of reporting services running on it (let us say the default instance and another named instance) and if WMI returns the default instance as the first instance then that would be the instance used to host all the Configuration Manager reports. So usage of a named instance is non-deterministic and is dependent on what is returned by WMI.

There were some issues that were uncovered after we shipped R2 which introduces a caveat:

a) If the report server machine hosts only a named instance, then we will not be able to detect the named instance since the WMI query we use to enumerate instances requires the presence of the default instance.

Workaround: So in order to detect the named instance, you should have the default instance installed on the same machine.

7) Does Configuration Manager 2007 R2 release support SQL Reporting Services 2008?

Yes - Configuration Manager 2007 R2 release supports both SQL Reporting Services 2005 and SQL Reporting Services 2008. However there are some known issues with leveraging SQL Reporting Services 2008 that are outlined later in this FAQ.

8) What is the support story for SxS (Side-By-Side) scenarios for SQL Reporting Services 2005 and SQL Reporting Services 2008 on the same machine?

As outlined in 6) above, we use WMI to enumerate the list of SQL Reporting Services instances running on a machine. However, there is a preference set internally for this to favor SQL Reporting Services 2008 over SQL Reporting Services 2005. What this means is if you have a SQL Reporting Services 2008 default instance running on the same machine as a SQL Reporting Services 2005 instance, we will first query the WMI namespace for SQL Reporting Services 2008 and use the first instance of SQL Reporting Services 2008 returned by WMI. If no instances of SQL Reporting Services 2008 are detected, we will query the WMI namespace for SQL Reporting Services 2005 and use the first instance returned by WMI.

9) Do we support https enabled report server instances?

There are known issues in the R2 release with https enabled report server instances. There are some methods in the reporting services webservices  API that are inherently secure  methods and if SSL is configured on a report server instance these methods  cannot be invoked via http and require https connections. Internally, since we leverage some of these APIs, we fail to perform some actions.

Workaround: Unfortunately there is no easy workaround for this other than to not use SSL enabled report server instances. However there is a configuration setting that can be tweaked to get around this problem but it is NOT recommended to tweak this setting if the report server instance is hosting other reports (not specific to Configuration Manager).

a) Open the rsreportserver.config file using notepad and search for the property "SecureConnectionLevel". This value should be set to 3 (or some value > 0) for secured report server URLs. Change this value to 0 and save the rsreportserver.config file.

(The default location for the default instance is under [SystemDrive]:Program FilesMicrosoft SQL ServerMSRS10.MSSQLSERVERReporting ServicesReportServer ).

10) What are the various options available to author new SQL Reporting Services reports?

a) Business Development Studio (bundled with SQL Server installation)

b) Report Builder (free standalone tool)

c) Report creation wizards shipped with Configuration Manager 2007 R2 release.

11) Will all reports run successfully from both the Configuration Manager Administration console and the SQL Reporting Services Web UI?

At the time Configuration Manager 2007 R2 was released, some tools like Report Builder 2.0 weren't released yet and we still weren't supporting SQL Server 2008 at that time. So there are some caveats to authoring and running reports in a SQL Reporting Services 2008 environment.

Full support for authoring and running reports in a SQL Server 2005 environment

- Any out-of-box reports or custom reports written in SMS 2003/Configuration Manager 2007 using the report wizard provided in SMS 2003 and available in Configuration Manager 2007 as well should be converted properly to SRS reports using the Copy Reports Wizard available in R2 and these should run fine both from the Configuration Manager administration console as well as SQL Reporting Services web UI.

- Any custom report authored using the report wizards provided in the R2 release (the SQL based report wizard and the Model based report wizard using the 2 shipped models in R2) should run properly from both the admin console as well SQL Reporting Services Web UI.

o If you plan on creating custom report models then the process entails a few extra steps to make them available in the administration console. The details are provided in our online documentation .

- Any custom report authored in a SQL Reporting Services 2005 environment (like using Business Intelligence Development studio or Report Builder 1.0) should run fine from the admin console.

Partial support for authoring and running reports in a SQL Server 2008 environment

- The Configuration Manager 2007 R2 release uses the SQL Reporting Services 2005 report schema underneath and hence reports authored using Business Development Studio 2008 (as bundled with SQL Server 2008) and Report Builder 2.0 will not run from the Configuration Manager 2007 R2 administration console. However you can author reports using any of the tools available with SQL Reporting Services 2008 and can run these reports from the SQL Reporting Services Web UI directly.

-- Bhaskar Krishnan

This posting is provided "AS IS" with no warranties and confers no rights.