New-SCOMManagementGroupConnection failing due to insufficient permissions

Error: Could not connect to SCOM zone in another domain using credentials of the same domain. even though bidirectional trust was present.

If the sdk service is running under local system account.

 

Error during powershell connection to scom zone.

 

PS C:\> New-SCOMManagementGroupConnection -ComputerName SCOMMG2.consoso.com
New-SCOMManagementGroupConnection : The user does not have sufficient permission to perform the operation.
At line:1 char:1
+ New-SCOMManagementGroupConnection -ComputerName SCOMMG2.ms-Contoso ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.Syste...nnectionCommand:NewSCManagementGroupConnectionComma
   nd) [New-SCManagementGroupConnection], UnauthorizedAccessEnterpriseManagementException
    + FullyQualifiedErrorId : ExecutionError,Microsoft.SystemCenter.Core.Commands.NewSCManagementGroupConnectionComman

 

Event id 4 generated in System Log.

The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server SCOMMG2$. The target name used was MSOMSdkSvc/SCOMMG2.consoso.com. This indicates that the target server failed to decrypt the ticket provided by the client. This can occur when the target server principal name (SPN) is registered on an account other than the account the target service is using. Ensure that the target SPN is only registered on the account used by the server. This error can also happen if the target service account password is different than what is configured on the Kerberos Key Distribution Center for that target service. Ensure that the service on the server and the KDC are both configured to use the same password. If the server name is not fully qualified, and the target domain (CONSOSO.COM) is different from the client domain (CONTOSO.AL.GOV), check if there are identically named server accounts in these two domains, or use the fully-qualified name to identify the server.

 Verified that the id had permissions to SCOM zone.

The Scom SDK service was running under local system account. The spn for sdk service was not registered in AD. Verified by setspn -L SCOMMG2.

Event id 26371 generated in Operations manager log.

 

Date and Time: 7/15/2023 1:15:28 AM Log Name: Operations Manager Source: OpsMgr SDK Service Event Number: 26371 Level: 2 Logging Computer: SCOMMG1.contoso.com User: N/A

Description: The System Center Data Access service failed to register an SPN. A domain admin needs to add MSOMSdkSvc/SCOMMG1 and MSOMSdkSvc/SCOMMG1.contoso.com to the servicePrincipalName of CN=SCOMMG1,OU=Server Accounts,OU=Prod,OU=Contoso Servers,DC=-Contoso,DC=COM

Event Data:

< DataItem type =" System.XmlData " time =" 2023-07-15T01:15:28.0000000-05:00 " sourceHealthServiceId =" 2765519A-33B2-441C-F345-0FF0CEB2E109 " >

< EventData >

  < Data > SCOMMG1 </ Data >

  < Data > SCOMMG1.contoso.com </ Data >

  < Data > CN=SCOMMG1,OU=Server Accounts,OU=Prod,OU=Contoso Servers,DC=-Contoso,DC=COM </ Data >

  </ EventData >

  </ DataItem >

Solution:

·         Open ADSIDEDIT. Find the server object open properties  and grant SELF read write access in security tab.

·         Registered spn for the sdk and all management server computer accounts using.

Setspn.exe -S MSOMSdkSvc/SCOMMG1 SCOMMG1

Setspn.exe -S MSOMSdkSvc/SCOMMG2 SCOMMG2

 

Connection was successful in powershell.

 

 

 

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.

SCOM DataWare House grooming.

Query to check the size of the tables.

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

Get size of the database.
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
GO

Query to check DW data dates.

Select     min(datetime)as MinDate, max(datetime)as MaxDate ,   datediff(d,min(datetime),max(datetime)) AS NoOfDaysInDataSet from Perf.vPerfHourly

My query showed that there was 424 days of data.

MinDate    MaxDate    NoOfDaysInDataSet
2015-10-09 21:00:00.000    2016-12-06 13:00:00.000    424

Tool for modifying the datawarehouse grooming settings can be downloaded here.

https://blogs.technet.microsoft.com/momteam/2008/05/13/data-warehouse-data-retention-policy-dwdatarp-exe/

Command to run the dwdatarp.exe to get the current sizes of datasets.

C:\temp>dwdatarp.exe -s servername\instancename -d operationsmanagerdw > c:\temp\dwoutput.txt

Dataset name                          Aggregation name     Max Age         Current Size, Kb

Alert data set                             Raw data                           400          104,752 (  0%)
Client Monitoring data set     Raw data                           30              0 (  0%)
Client Monitoring data set     Daily aggregations          400            96 (  0%)
Configuration dataset             Raw data                           400            485,120 (  1%)
DPM event dataset                  Raw data                           400             0 (  0%)
Event data set                           Raw data                           100             12,315,568 ( 14%)
Performance data set              Raw data                           10                4,316,832 (  5%)
Performance data set              Hourly aggregations       400            44,009,336 ( 50%)
Performance data set              Daily aggregations          400            2,049,856 (  2%)
State data set                            Raw data                           180            121,784 (  0%)
State data set                            Hourly aggregations       400           22,979,912 ( 26%)
State data set                            Daily aggregations          400           1,395,216 (  2%)

Changing the grooming settings. Adjust the time according to my reporting requirements.

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "Alert data set" -a "Raw data" -m 180

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "Performance data set" -a "Hourly aggregations" -m 90

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "Performance data set" -a "Daily aggregations" -m 365

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "Event data set" -a "Raw Data" -m 30

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "State data set" -a "Raw data" -m 90

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "State data set" -a "Hourly aggregations" -m 90

dwdatarp.exe -s SERVERNAME\INSTANCENAME -d OperationsManagerDW -ds "State data set" -a "Daily aggregations" -m 90