Troubleshooting Configuration Manager: Resolving Report Execution Failures

Table of Contents

Introduction to Report Execution Challenges¶

Microsoft Endpoint Configuration Manager (MECM) serves as a cornerstone for device management, providing comprehensive capabilities ranging from software deployment to compliance enforcement. A critical aspect of its functionality lies in its robust reporting infrastructure, powered by Microsoft SQL Server Reporting Services (SSRS). These reports offer invaluable insights into the health, status, and configuration of an organization’s IT environment. However, when report execution failures occur, they can significantly hinder an administrator’s ability to monitor and manage the enterprise infrastructure effectively, leading to operational blind spots and delayed problem resolution. This article addresses a specific, yet impactful, issue where reports fail to run for collections when SQL Server 2019 is utilized with Configuration Manager, providing a detailed breakdown of symptoms, underlying causes, and comprehensive resolution strategies.

Scope of the Issue¶

This troubleshooting guide primarily applies to environments leveraging Microsoft Endpoint Configuration Manager in conjunction with Microsoft SQL Server 2019. The problem manifests specifically when attempting to run various collection-based reports within the Configuration Manager console, impacting data visibility and administrative efficiency. Understanding the interplay between these two powerful Microsoft technologies is crucial for diagnosing and rectifying the observed anomalies.

Unveiling the Symptoms¶

Administrators encountering this issue will observe distinct error messages and log entries indicating a failure in report processing. These symptoms collectively point towards a permission-related problem during the execution of underlying SQL Server functions that support Configuration Manager reports. Recognizing these indicators early is key to a swift diagnosis and remediation.

Immediate Error Messages in Configuration Manager Console¶

When an attempt is made to generate a report for a collection, the Configuration Manager console or the web-based reporting interface will display one or more of the following critical error messages. These messages are direct indicators of a processing abortion due to insufficient permissions within the SQL Server database. The primary message signals a general processing failure, while subsequent messages pinpoint the exact cause related to denied execution permissions on specific database objects.

The most prominent error message observed is:

An error has occurred during report processing. (rsProcessingAborted)

This general error is typically followed by more specific details that elucidate the root cause of the processing abortion. These detailed messages explicitly indicate a denial of EXECUTE permissions on critical database functions, which are integral to Configuration Manager’s reporting mechanism. Specifically, you may encounter:

The EXECUTE permission was denied on the object ‘fnIsCas’, database ‘CM_LKD’, schema ‘dbo’

And, in some instances, a similar permission denial on another core function:

The EXECUTE permission was denied on the object ‘fnIsPrimary’, database ‘CM_IDR’, schema ‘dbo’

These specific errors highlight that the service account or user context under which the report is being executed lacks the necessary privileges to invoke certain user-defined functions (UDFs) within the Configuration Manager database. The databases CM_LKD and CM_IDR represent the Configuration Manager database instance, typically named CM_<SiteCode>.

Deep Dive into ReportingServicesService.log¶

Beyond the immediate console errors, detailed diagnostic information is consistently logged in the ReportingServicesService.log file on the reporting services point. This log file is invaluable for pinpointing the exact query and database object causing the failure. Administrators should always consult this log for a comprehensive understanding of the error context, as it provides stack traces and specific SqlException details.

The ReportingServicesService.log file is typically located in C:\Program Files\Microsoft SQL Server\MSRS15.MSSQLSERVER\Reporting Services\LogFiles\ (the MSRS15.MSSQLSERVER part may vary based on your SQL Server instance name and version, MSRS15 denotes SQL Server 2019). Within this log, you will find entries similar to the following, indicating a ReportProcessingException stemming from a System.Data.SqlClient.SqlException:

processing!ReportServer_0-2!18fc!<Date>-<Time>:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'DeploymentSummary'.

   ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'fnIsCas', database 'CM_LKD', schema 'dbo'.

processing!ReportServer_0-2!18fc!<Date>-<Time>:: e ERROR: An exception has occurred in data set 'DeploymentSummary'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'DeploymentSummary'.

   ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'fnIsCas', database 'CM_LKD', schema 'dbo'.

processing!ReportServer_0-2!18fc!<Date>-<Time>:: v VERBOSE: An exception has occurred. Trying to abort processing. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'DeploymentSummary'.

   ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'fnIsCas', database 'CM_LKD', schema 'dbo'.

These log entries consistently highlight the Query execution failed for dataset 'DeploymentSummary' followed by the explicit SqlException detailing the denied EXECUTE permission on fnIsCas. The presence of these specific error messages in the logs confirms that the issue is indeed related to insufficient privileges for the reporting service account to execute essential SQL functions, which has been exacerbated by a change in query processing behavior in SQL Server 2019.

The Root Cause: Scalar UDF Inlining in SQL Server 2019¶

The underlying cause of these report execution failures can be traced back to a specific performance optimization feature introduced in SQL Server 2019: Scalar UDF Inlining. While designed to enhance performance by transforming scalar User-Defined Functions (UDFs) into equivalent relational expressions, this feature can, in certain complex scenarios, lead to unexpected query execution plans, errors, or incorrect results.

Understanding Scalar UDF Inlining¶

Traditionally, scalar UDFs are executed row-by-row, leading to significant overhead, especially in large datasets. Scalar UDF Inlining aims to mitigate this by “inlining” the UDF’s logic directly into the calling query’s execution plan. This means the query optimizer treats the UDF’s code as part of the main query, potentially allowing for more efficient processing by removing the context switching overhead associated with UDF calls. This optimization is automatically applied by the SQL Server 2019 query optimizer for eligible scalar UDFs when the database compatibility level is set to 150 (the default for SQL Server 2019).

How UDF Inlining Impacts Report Execution¶

The issue arises because when fnIsCas or fnIsPrimary (which are scalar UDFs) are inlined, the way their underlying logic is processed changes within the query plan. In some edge cases, particularly those involving complex permissions or specific query patterns, the inlined code might inadvertently lead to permission checks being evaluated differently, or it might attempt to access resources in a way that wasn’t anticipated by the original permission grants. Even if the service account had permission to execute the UDF itself, the inlined query might reveal or require permissions on underlying objects or data accessed by the UDF that were not explicitly granted to the execution context of the report. This effectively surfaces a “denied permission” error that wouldn’t have occurred with traditional UDF execution.

Known Issues and Microsoft Advisories¶

Microsoft has acknowledged that the Scalar UDF Inlining feature in SQL Server 2019 can sometimes produce errors or unexpected results. This is often due to the complexity of translating procedural T-SQL logic into a purely relational form. Such issues have been documented, and Microsoft has released updates to address these specific scenarios. The problem described here is a classic example where a performance optimization inadvertently interferes with the established permission model or query behavior within a complex application like Configuration Manager. This necessitates a strategic approach to either disable the inlining feature for the affected database or apply updates that resolve the underlying bugs in the inlining mechanism.

Comprehensive Resolution Strategies¶

Resolving report execution failures caused by Scalar UDF Inlining requires a multi-faceted approach, combining direct permission adjustments with strategies to manage or mitigate the UDF inlining behavior. Each strategy offers a different level of intervention, from targeted permission grants to broader system updates.

Strategy 1: Addressing Permissions Directly¶

While Scalar UDF Inlining is the trigger, the immediate error is a permission denial. It’s crucial to ensure that the SQL Server Reporting Services (SSRS) service account, which executes the reports, possesses all necessary permissions on the Configuration Manager database, including EXECUTE rights on relevant functions. This is often the first and most direct step in troubleshooting.

Verifying Required Permissions for Reporting Services¶

The SSRS service account typically requires specific roles within the Configuration Manager database (CM_xxx) to function correctly. The ConfigMgr_Drs_Public_Role and rsExecRole are two primary roles that provide the necessary database access for reporting. The rsExecRole is particularly important as it grants permissions required for report execution. You should verify that your SSRS service account or the security group it belongs to is a member of these roles.

To check the database role membership for the SSRS service account:

1. Open SQL Server Management Studio (SSMS).
2. Connect to the database engine instance hosting the Configuration Manager database.
3. Navigate to Databases > CM_<SiteCode> > Security > Roles > Database Roles > rsExecRole.
4. Right-click rsExecRole and select Properties.
5. In the Database Role Properties window, go to the Members page to verify that the SSRS service account (or a group it’s a member of) is listed. Repeat this for ConfigMgr_Drs_Public_Role.

If the account is not a member, add it. This ensures that the foundational permissions are in place before delving deeper.

Granting EXECUTE Permissions on Affected Functions¶

Even if the service account is part of rsExecRole, the inlining process might expose a need for direct EXECUTE permissions on the specific functions (fnIsCas, fnIsPrimary). This can happen if the role’s permissions are not sufficiently granular for the inlined query’s context. Manually granting these permissions can resolve the immediate error.

To grant EXECUTE permissions on fnIsCas and fnIsPrimary:

1. Open SQL Server Management Studio (SSMS).
2. Connect to the database engine instance hosting the Configuration Manager database.
3. Open a New Query window and ensure you are connected to the CM_<SiteCode> database.

4. Execute the following T-SQL commands, replacing [Your_SSRS_Service_Account] with the actual login name of your SQL Server Reporting Services service account (e.g., Domain\SSRS_Account or NT SERVICE\ReportServer):

   USE CM_<SiteCode>;
   GRANT EXECUTE ON OBJECT::dbo.fnIsCas TO [Your_SSRS_Service_Account];
   GRANT EXECUTE ON OBJECT::dbo.fnIsPrimary TO [Your_SSRS_Service_Account];
   GO
   

   Alternatively, if the rsExecRole is used by the SSRS account, you can try granting permissions to this role:

   USE CM_<SiteCode>;
   GRANT EXECUTE ON OBJECT::dbo.fnIsCas TO rsExecRole;
   GRANT EXECUTE ON OBJECT::dbo.fnIsPrimary TO rsExecRole;
   GO
   

   After granting permissions, restart the SQL Server Reporting Services service to ensure the changes take effect. This direct grant explicitly provides the required execution rights to bypass the permission denial.
   

Ensuring Proper Service Account Configuration¶

While less directly related to EXECUTE permissions, ensuring the overall health and proper configuration of the SSRS service account is a good practice. This includes verifying that the account has sufficient privileges on the local server where SSRS is installed, proper Service Principal Names (SPNs) registered for Kerberos authentication if your environment uses it (though usually not for this specific error), and no password expiration issues. A healthy service account reduces the likelihood of broader service interruptions.

Strategy 2: Managing Scalar UDF Inlining Behavior¶

Given that Scalar UDF Inlining is the root cause, a more robust solution involves either applying SQL Server updates that fix known issues with inlining or adjusting the database compatibility level to prevent inlining for the Configuration Manager database.

Applying Latest SQL Server Cumulative Updates¶

Microsoft frequently releases Cumulative Updates (CUs) for SQL Server that address bugs and improve performance. Many issues related to Scalar UDF Inlining, including those leading to incorrect results or errors, have been fixed in specific CUs for SQL Server 2019. Applying the latest available Cumulative Update for SQL Server 2019 is often the most recommended and sustainable solution. This approach allows you to retain the performance benefits of UDF inlining for other queries while ensuring that the problematic behavior impacting Configuration Manager reports is resolved.

Before applying any CU, it is imperative to:

• Backup your SQL Server databases (especially the Configuration Manager database).
• Test the CU in a non-production environment if possible, to ensure compatibility with your Configuration Manager version and other applications.
• Review the CU release notes for any known issues or specific instructions.

Consult Microsoft’s official documentation for the latest SQL Server 2019 CUs and their respective fixes. A CU that addresses Scalar UDF Inlining issues (such as the one referenced in the original KB article, KB4538581, which points to CUs for SQL Server 2019) would be ideal.

Modifying Database Compatibility Level¶

As a temporary or alternative workaround, you can disable Scalar UDF Inlining for the Configuration Manager database by setting its compatibility level to an earlier version (e.g., SQL Server 2017’s compatibility level, 140). This prevents SQL Server 2019 from applying the inlining optimization for queries against that database.

To change the database compatibility level:

1. Open SQL Server Management Studio (SSMS).
2. Connect to the database engine instance.
3. Navigate to Databases > CM_<SiteCode>.
4. Right-click CM_<SiteCode> and select Properties.
5. In the Database Properties window, go to the Options page.
6. Under the Compatibility level dropdown, select SQL Server 2017 (140).

7. Click OK.

   -- Alternative via T-SQL:
   ALTER DATABASE CM_<SiteCode>
   SET COMPATIBILITY_LEVEL = 140;
   GO
   

   After changing the compatibility level, restart the SQL Server service and the SQL Server Reporting Services service. This change will affect all queries against the CM_<SiteCode> database, potentially impacting performance in other areas if those queries were benefiting from UDF inlining. Therefore, this is generally considered a workaround until a suitable CU can be applied.

Using Query Hints (Less Applicable for Built-in Reports)¶

For custom reports or specific problematic queries, you can disable Scalar UDF Inlining at the query level using the DISABLE_SCALAR_UDF_INLINING query hint. This is highly granular and ideal for targeted fixes without affecting the entire database.

Example syntax:

SELECT /*+ DISABLE_SCALAR_UDF_INLINING */ *
FROM YourTable
WHERE dbo.fnIsCas(Column) = 1;

However, this method is generally not feasible for built-in Configuration Manager reports, as you cannot directly modify their underlying SQL queries. It’s mentioned here for completeness as a general strategy for UDF inlining issues.

Strategy 3: General Troubleshooting and Verification¶

After implementing any of the above strategies, it’s crucial to perform general troubleshooting steps to ensure the resolution is complete and stable.

Restarting Key Services¶

Always restart the following services after making significant configuration changes to SQL Server or SSRS:

• SQL Server (MSSQLSERVER or your instance name): This ensures that database compatibility level changes or permission grants are fully recognized.
• SQL Server Reporting Services (ReportServer or your instance name): This reloads the report definitions and permissions, ensuring the reporting service operates with the updated environment.

Reviewing Additional Logs¶

While ReportingServicesService.log is primary, other logs can provide supplementary information:

• SQL Server Error Log: Provides details on SQL Server startup, shutdown, and any database-level errors.
• Configuration Manager Component Status: Check the status of the Reporting Services Point and Site Server components in the Configuration Manager console.
• ConfigMgr Log Files: Specific Configuration Manager logs like srsrp.log on the Site Server can offer insights into the health of the Reporting Services Point component.

Validating Database Integrity¶

As a general best practice, especially after any SQL Server updates or significant changes, running DBCC CHECKDB on your Configuration Manager database can help ensure its integrity and rule out any underlying database corruption that might indirectly contribute to reporting issues.

Best Practices to Prevent Future Issues¶

To maintain a healthy and functional Configuration Manager reporting environment and minimize the recurrence of such issues, consider the following best practices:

• Regularly Apply Cumulative Updates: Stay current with SQL Server Cumulative Updates. Microsoft continuously releases fixes and improvements, and applying these proactively can prevent known issues from surfacing.
• Monitor SQL Server and ConfigMgr Logs: Establish a routine for reviewing key log files, including the SQL Server Error Log, Reporting Services logs, and Configuration Manager component logs. Early detection of warnings or errors can prevent minor issues from escalating.
• Follow Microsoft’s Recommended Configurations: Adhere to Microsoft’s guidelines for Configuration Manager and SQL Server co-existence and performance. This includes recommended hardware, security configurations, and database settings.
• Implement a Robust Backup Strategy: Regular and tested backups of your Configuration Manager database and SQL Server system databases are critical for disaster recovery and can save significant time during unexpected issues.
• Test Changes in Staging Environments: Whenever possible, test major SQL Server updates or Configuration Manager changes in a non-production or staging environment before deploying them to your production system.

Conclusion¶

Report execution failures in Microsoft Endpoint Configuration Manager, particularly when utilizing SQL Server 2019, can significantly impede administrative oversight. The specific error concerning EXECUTE permission denial on functions like fnIsCas or fnIsPrimary is a strong indicator of an interaction with the Scalar UDF Inlining feature introduced in SQL Server 2019. By systematically addressing permissions, applying relevant SQL Server Cumulative Updates, or, as a temporary measure, adjusting the database compatibility level, administrators can effectively resolve these critical reporting issues. Adopting best practices for SQL Server and Configuration Manager maintenance will ensure the continued stability and reliability of your reporting infrastructure, providing the essential insights needed for effective IT management.

We Value Your Insights!¶

Have you encountered similar report execution failures in your Configuration Manager environment? What strategies did you find most effective in resolving these issues? Share your experiences, tips, and questions in the comments section below. Your contributions are invaluable to the community!