Problems with 10 'staging'-related workflows in the 'OpsMgr Self Maintenance MP'

Dashboard Server
, ,
1

The PowerShell script entitled ‘DWStagingTablesRowCountProbe.ps1’ driving the 10 ‘staging’-related workflows (i.e. 5 performance-collection rules & 5 related unit monitors) in the ‘OpsMgr Self Maintenance MP’, does not appear to be working properly in either of my, or my client’s SCOM 2019 Management Group. Seeing as all of the workflows target the ‘All Management Servers Resource Pool’, they should be running on 1 of the Management Servers in the context of the Management Server Action Account (i.e. MSAA). When the script is launched interactively in the context of this account, it returns the following PowerShell errors, 5 times, once for each of the 5 ‘staging’ tables queried by the script (i.e. Alert/Event/Perf/State/ManagedEntity):

“Exception calling “Fill” with “1” argument(s): “Cannot open database “OperationsManagerDW” requested by the login. The login failed.”

Based on the errors, it was quickly confirmed that the MSAA does not have a corresponding user defined in the OperationsManagerDW database (though the login is created in the DB Engine hosting the same database). Note that this is a perfect example of the necessity of properly handling errors in PoSH scripts.

The following Property Bag is also returned, which explains the 0 values returned to SCOM:

<DataItem type=”System.PropertyBagData” time=”2020-02-10T17:49:03.9113069-05:00″ sourceHealthServiceId=”67283979-CAA5-86DF-E8D1-BFB5876502DC”><Property Name=”Alert” VariantType=”3″>0</Property><Property Name=”Event” VariantType=”3″>0</Property><Property Name=”Perf” VariantType=”3″>0</Property><Property Name=”State” VariantType=”3″>0</Property><Property Name=”ManagedEntity” VariantType=”3″>0</Property></DataItem>

Though not recommended practice, shortly after granting ‘sa’ priviledged to the MSAA on the DB Engine hosting the OperationsManagerDW database, everything works as expected. Two possible solutions that might be considered, might be:

  1. Associate the “OpsMgr.Self.Maintenance.DW.DB.Staging.Table.Row.Count.Probe” Probe Action module (i.e. the one hosting the PoSH script) with one of the following existing SCOM profiles:
    – ‘Data Warehouse Account’ (i.e. RunAs=”DW!Microsoft.SystemCenter.DataWarehouse.ActionAccount”)
    – ‘Data Warehouse Report Deployment Account’ (i.e. RunAs=”DW!Microsoft.SystemCenter.DataWarehouse.ReportDeploymentActionAccount”)

I have to admit I tried this solution, but a new problem arises in that the accounts are not able to read the following registry key:

Get-ItemProperty : Cannot find path ‘HKLM:\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup’ because it does not exist.

  1. Create a new MSAA SQL user in the OperationsManagerDW database, and assign it to one of the following roles:
  • -db_datareader
  • OpsMgrReader
I’m hardly a DB expert, so there is likely a better solution to this problem.

Environement

  • SCOM 2019 (Gold)
  • OpsMgr Self Maintenance MP v3.1.0.0
Regards,

Larry

2

Hello Larry,

 

Thanks for posting the issue to Community Answers. I’ve created a bug in our system for the issue and will try to find some time to look at it shortly.

This week is pretty booked but I’ll update this Community Answers thread as soon as I have more information.

As noted in the comment below, my suggestion would be to add the MSAA account to the DW database with the OpsMgrDBReader role. It should provide the correct access without excess permissions.

 

If there are other people who would like to provide more suggestions please do, there may be other options that work well too.

 

Thanks,

Nathan Foreman

Cookdown

3

Hello Nathan,

Whatever is decided, the solution should also address an identical problem has been identified with the following additional workflows:

  • OpsMgr.Self.Maintenance.Check.Data.Warehouse.DB.Daily.Aggregation.Count.3State.Monitor
  • OpsMgr.Self.Maintenance.Check.Data.Warehouse.DB.Hourly.Aggregation.Count.3State.Monitor
  • OpsMgr.Self.Maintenance.DW.Database.Aggregation.Perf.Collection.Rule
I can't help but wonder how many other workflows might be affected by the same problem...

Regards,
Larry

4

I’ve got a couple of notes to add, however, I don’t think any of them would qualify as a perfect answer.

I will second the mention that adding sa is likely bad practice, as it is far more permission than is needed for this account.

I like the second solution suggested by Larry, adding the Management Server Action Account to the SQL Server with the role of OpsMgrReader should work well without granting excess access.