This is a short post where I am sharing an issue which I phased after upgrading to SQL Server 2019 for my Configuration Manager Database. After upgrade, I wasn’t able to open any report because of the following error:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'CollectionInfo'. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object 'fnIsCas', database 'CM_MAN', schema 'dbo'.
The execute permission was denied

Initially it seems like that there are some permissions issue, however I didn’t make any changes while upgrade and all the reports were working fine previously. And I was getting this error with all the reports, not just 1 single report

Root cause of the issue

The problem occurs because of the Scalar UDF Inlining issues with SQL Server 2019. You may follow the link to know more about it which explains in detail about the error and unexpected results which I got.


Solution

Microsoft has released KB5000642 – cumulative update 9 which resolves the issue. This KB article KB4538581 explains that cumulative update 9 or later resolves this issue.

At the time of writing this post, cumulative update 12 is the latest version available for SQL Server 2019, following is the list, any of the update can be installed (though latest one is preferred):

  • SQL Server 2019 RTM Cumulative Update (CU) 12 KB5004524
  • SQL Server 2019 RTM Cumulative Update (CU) 11 KB5003249
  • SQL Server 2019 RTM Cumulative Update (CU) 10 KB5001090
  • SQL Server 2019 RTM Cumulative Update (CU) 9 KB5000642

Download the Update

Install Cumulative Update for SQL Server 2019

Once downloaded, install SQL Serer 2019 update.

While updating it you might get following error:

The 'WmiPrvSE.exe' process is not controlled by the update wizard. You have to manually stop this process to avoid a computer restart.
WmiPrvSE.exe windows management instrument service

For this you need to manually stop the Windows management instrument service. Once you try stopping this service, it will also stop following services:

  • User Access Logging Service
  • IP Helper
  • SMS Agent Host

I also got another error

The 'WAPCSvc' service is not controlled by the update wizard. You have to manually stop this service to avoid a computer restart.

For this I had to stop the service Microsoft AAD Application Proxy Connector.

WAPCSvc Microsoft AAD Connector service Application proxy

Once done, update went fine without any further issues

For installation you may check the logs located at C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log

When I launch the ConfigMgr report, it opened the report as expected.

SSRS report