0

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.

2. 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

Larry Leblanc answered