SCOM 2022 add management server failed

Adding a new management server fails with the following error in the 

OpsMgrSetupWizard.log

[10:24:57]:    Error:    :RunWindowsLoginCreateSP failed: Threw Exception.Type: System.Data.SqlClient.SqlException, Exception Error Code: 0x80131904, Exception.Message: User does not have permission to perform this action.
[10:24:57]:    Error:    :StackTrace:   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Microsoft.EnterpriseManagement.OperationsManager.Setup.DWConfigurationHelper.DWConfigurationProcessor.RunWindowsLoginCreateSP(Boolean useOMStoredProc, String sqlServer, Nullable`1 sqlPort, String databaseName, String logOnName, String roleName)
[10:24:57]:    Error:    :Failed while setting DW security: Threw Exception.Type: System.Data.SqlClient.SqlException, Exception Error Code: 0x80131904, Exception.Message: User does not have permission to perform this action.
[10:24:57]:    Error:    :StackTrace:   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Microsoft.EnterpriseManagement.OperationsManager.Setup.DWConfigurationHelper.DWConfigurationProcessor.RunWindowsLoginCreateSP(Boolean useOMStoredProc, String sqlServer, Nullable`1 sqlPort, String databaseName, String logOnName, String roleName)
   at Microsoft.SystemCenter.Essentials.SetupFramework.InstallItemsDelegates.OMDataWarehouseProcessor.SetDataWarehouseSecurity()
[10:24:57]:    Error:    :FATAL ACTION: SetDataWarehouseSecurity
[10:24:57]:    Error:    :FATAL ACTION: DWInstallActionsPostProcessor
[10:24:57]:    Error:    :ProcessInstalls: Running the PostProcessDelegate returned false.
[10:24:57]:    Always:    :SetErrorType: Setting VitalFailure. currentInstallItem: Data Warehouse Configuration
[10:24:57]:    Error:    :ProcessInstalls: Running the PostProcessDelegate for OMDATAWAREHOUSE failed.... This is a fatal item.  Setting rollback.
[10:24:57]:    Info:    :SetProgressScreen: FinishMinorStep.
[10:24:57]:    Always:    :!***** Installing: POSTINSTALL ***
[10:24:57]:    Info:    :ProcessInstalls: Rollback is set and we are not doing an uninstall so we will stop processing installs
[10:24:57]:    Always:    :****************************************************************
[10:24:57]:    Always:    :****Starting*RollBack*******************************************

Solution:

Run the install under an account which has write permissions to the database.

Orchestrator fails to run powershell cmdlets

Scenario: After you add a runbook activity which runs a powershell script. The activity fails with error.

e.g connecting to Azure using an app id.

The term 'Connect-AzAccount' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

 

If you open the runbook using runbook tester it may work fine.

But when you click run in the Designer it will fail.

This happens because the required modules are not installed in the runbook service context.

To do this add a new  Run . Net script activity and add the lines to install those modules in the it.

You might also get the following  error.

 Install-NuGetClientBinaries -CallerPSCmdlet $PSCmdlet -Proxy $Proxy -ProxyCredential $ProxyCredential
 Exception: Exception calling "ShouldContinue" with "2" argument(s): "A command that prompts the user failed because the host program or the command type does not support user interaction. The host was attempting to request confirmation with the following message: PowerShellGet requires NuGet provider version '2.8.5.201' or newer to interact with NuGet-based repositories. The NuGet provider must be available in 'C:\Program Files (x86)\PackageManagement\ProviderAssemblies' or 'C:\Windows\system32\config\systemprofile\AppData\Local\PackageManagement\ProviderAssemblies'. You can also install the NuGet provider by running 'Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force'. Do you want PowerShellGet to install and import the NuGet provider now?"

 

 

Giving the lines here for the lazy ones.

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201  -scope CurrentUser  -Force

Install-module Az -scope CurrentUser -Force