RiskFabric Processing hangs during cube processing

Products

• Information Centric Analytics
• Data Loss Prevention Core Package

Issue/Introduction

No results are returned in the Analyzer in the Information Centric Analytics (ICA) console, or the data are stale and haven't been updated overnight. In addition, the RiskFabric Processing job appears to run indefinitely. The job's history in the SQL Server Agent shows CustomComplianceData (step 15) is the last step to have completed, with processing hung on Risk Fabric cube processing (step 16). Running a SQL Server Profiler trace against the SQL Server Analysis Services (SSAS) server hosting the RiskFabric cube shows no processing activity.

Environment

Version : 6.x

Component : Microsoft SQL Server

Cause

Cube processing durations increase or processing hangs after installing the security update for SQL Server 2016 SP2 CU7, published July 7, 2019, to address CVE-2019-1068, per Microsoft document KB4505222:

https://support.microsoft.com/en-us/help/4505222/security-update-for-sql-server-2016-sp2-cu7

NOTE: link available as of 2023-02-25

The following builds are affected:

• SQL Server 2016: all cumulative updates for SP2 from CU7 (13.0.5337.0) through CU13 (13.0.5820.21)
• SQL Server 2017: all cumulative updates from CU15 (14.0.3162.1) through CU22 (14.0.3356.20)
• SQL Server 2019: all cumulative updates through CU8 (15.0.4073.23)

SQL Server 2022 is not known to be affected by this issue.

Resolution

This issue is resolved by upgrading both Microsoft SQL Server and Microsoft SQL Server Analysis Services to the latest available build. Per Microsoft document KB4589170, a fix for this issue was initially released in the following updates:

• SQL Server 2016: CU14 (13.0.5830.85)
• SQL Server 2017: CU23 (14.0.3381.3)
• SQL Server 2019: CU9 (15.0.4102.2)

https://support.microsoft.com/en-us/topic/kb4589170-fix-indefinite-hang-during-cube-processing-after-applying-ssas-2016-sp2-cu7-a6d637ea-6bc3-f7f1-98d0-813dddd2a686

NOTE: link available as of 2023-02-25

For some environments, including those with fewer than 16 CPU cores, it's also necessary to disable the CoordinatorSafeJobUnblocking setting on the Analysis Services (SSAS) server after installing a patched version of SQL Server. To disable this setting, follow this procedure:

1. On the server hosting SSAS, open Notepad or another text editor as an administrator by right-clicking the application executable (i.e., Notepad.exe) and selecting Run as administrator
2. From the File menu, select Open...
3. Navigate to the location of the msmdsrv.ini file. The default location is here:

   %SystemDrive%\Program Files\Microsoft SQL Server\MSAS<n>.MSSQLSERVER\OLAP\Config\

   (where <n> refers to the version of SQL Server)
   
   
4. Set the explorer window to show All Files and double-click the file msmdsrv
5. Search for the following string:

   CoordinatorSafeJobUnblocking

6. By default, this key is set as follows:

   <CoordinatorSafeJobUnblocking>1</CoordinatorSafeJobUnblocking>

7. Edit this key by changing the bit to 0:

   <CoordinatorSafeJobUnblocking>0</CoordinatorSafeJobUnblocking>

8. From the File menu, select Save
9. Open Command Prompt as an administrator by right-clicking the application executable (i.e., cmd.exe) and selecting Run as administrator
10. If the RiskFabric cube is installed in the default Analysis Services instance, copy and paste or enter the following command:

    NET STOP "SQL Server Analysis Services" && NET START "SQL Server Analysis Services"

11. If the RiskFabric cube is installed in a named Analysis Services instance, copy and paste or enter the following command, replacing the <instance name> variable with your instance name (e.g., (ICA)):

    NET STOP "SQL Server Analysis Services (<instance name>)" && NET START "SQL Server Analysis Services (<instance name>)"

12. Press Enter to execute the command and restart the Analysis Services service for the setting change to take effect

Additional Information

The attached document from the Bay Dynamics consulting team provides a root cause analysis of cube processing issues experienced after installing cumulative updates 7 through 13 for Microsoft SQL Server 2016 SP2. These issues were caused by a security patch for SP2 CU7 released by Microsoft in July 2019 which had adverse effects on cube processing in SQL Server Analysis Services (SSAS). Note that this issue also affects builds of SQL Server 2017 and 2019. Refer to the Cause section of this article for affected versions and builds.

After applying the CU7 patch to SQL Server environment, SQL Server Analysis Services is adversely affected during ICA's nightly processing job, resulting in cube processing hanging indefinitely. After adjusting settings in Analysis Services and rebooting the server, manually processing the cube proved successful, however only in a sequential fashion. By nature, serial processing proved to be much more time consuming, increasing the overall cube processing time from 30 minutes under the former parallel setting, to an hour and a half. This behavior was later replicated in Bay Dynamics’ internal environment by applying the same SQL patch. After removing that patch, cube processing was allowed to continue successfully, further underscoring the point that this issue was directly related to the security update provided by Microsoft.

If patching SQL Server is not possible, the process in the attached document can be followed as a workaround for the issue described above, which will enable the cube to process successfully. Note that as previously mentioned, this will increase the duration of cube processing; however, it will allow data in Risk Fabric to update.