Associate SCOM Data Warehouse Profile using PowerShell

In this blog, I will explain an issue where all your Data Warehouse workflows are failing.
I will also provide a PowerShell script to fix that particular problem.

Data Warehouse workflows are extremely critical in SCOM and they needs special attention. There are many monitors which are created in order to assess the health of Data Warehouse objects and also generate alerts.

Today, I will explain a particular scenario, where one fine day you might see all your Data Warehouse workflows are failing.

There are flood of error events in the Operations Manager event log in the series of 3155*. Below is an example.

The description clearly states there is a permission issue on the Data Warehouse Database.

Let us jump into the SQL instance. But….Hold On!!!!!

There is something fishy out here…… The account in the description (NFS\2019-scomsvc-ms) is my Management Server Action Account. But ideally the Data Warehouse workflows should run under the writer account….Hmmm…that’s right.

Possible causes??

1. Someone deleted the writer account – Possible
2. Someone changed the writer account to Management Server Action Account – Possible.
   Let us check both the points.

   I can see my Data Warehouse Action Account still present and using the writer account. Let us move on.

3. Someone removed the association of the Data Warehouse Run As Profile in SCOM –  Possible because in that case it will try to use the Management Server action account.
   In case you want more explain why? Read my other blog.
   http://88i.5b8.mywebsitetransfer.com/scomrunasaccountandprofile/

   Bingo!!!

Now the easy way to fix this is to compare with a working scenario and re-associate the Run As Account. But would not it be great if we just run a PowerShell script and that does the everything for us. So here goes the script.

Instructions:

1. Either copy and save the script and run it in Powershell.exe.
2. Or copy it PowerShell ISE and run it.

#Associate  Run As Account association in Data Warehouse and Report Deployment Run As Profile.
Write-Host "Script started.." -ForegroundColor Green

Import-Module OperationsManager

#Get the run as profiles
$DWActionAccountProfile = Get-SCOMRunAsProfile -DisplayName "Data Warehouse Account"
$ReportDeploymentProfile = Get-SCOMRunAsProfile -DisplayName "Data Warehouse Report Deployment Account"

#Get the run as accounts
$DWActionAccount = Get-SCOMrunAsAccount -Name "Data Warehouse Action Account"
$DWReportDeploymentAccount = Get-SCOMrunAsAccount -Name "Data Warehouse Report Deployment Account"

#Get all the required classes
$CollectionServerClass = Get-SCOMClass -DisplayName "Collection Server"
$DataSetClass = Get-SCOMClass -DisplayName "Data Set"
$APMClass = Get-SCOMClass -DisplayName "Operations Manager APM Data Transfer Service"
$DWSyncClass = Get-SCOMClass -DisplayName "Data Warehouse Synchronization Server"

#Setting the association
Write-Host "Setting the Run As Account Association for Data Warehouse Account Profile" -ForegroundColor Cyan
Set-SCOMRunAsProfile -Action "Add" -Profile $DWActionAccountProfile -Account $DWActionAccount -Class $CollectionServerClass,$DataSetClass,$APMClass,$DWSyncClass
Write-Host "Setting the Run As Account Association for Data Warehouse Report Deployment Account Profile" -ForegroundColor Cyan
Set-SCOMRunAsProfile -Action "Add" -Profile $ReportDeploymentProfile -Account $DWReportDeploymentAccount -Class $CollectionServerClass,$DWSyncClass


Write-Host "Script end.." -ForegroundColor Green

In case you are using Git you can pull it from the below branch.
https://github.com/Udish17/SCOM-PowerShell-Scripts/blob/SCOM-PowerShell-Scripts/master/DWRunAsProfile.ps1

Hope this was helpful.

Thanks!

Explore, Learn, Share, Repeat!