I have seen in couple of Forum posts and in MS support cases where customer is having issues while dealing with SQL management pack workflows.
I will discuss the some of the tips and tricks which will help you to identify if we have any issues with the SQL workflows.
Firstly, we need to look at the event logs from the SQL server where we have seen the errors like following exceptions in the event logs.
Error 1:
--------- Exception: ---------- Exception Type: System.Exception Message: Unable to execute query 'SELECT ServiceName, PropertyName, PropertyNumValue, PropertyStrValue FROM SqlServiceAdvancedProperty WHERE (PropertyName = 'ISWOW64' OR PropertyName = 'LANGUAGE' OR PropertyName = 'INSTANCEID' OR PropertyName = 'SPLEVEL' OR PropertyName = 'INSTALLPATH' OR PropertyName = 'ERRORREPORTING') AND SqlServiceType = 1' for the path '\\sqlcluster.lab.com\root\Microsoft\SqlServer\ComputerManagement11'. 32 bit: False. Error code: -2147217392. Error message: Invalid class Source: Microsoft.SQLServer.Module4.Helper Stack Trace:
Error 2:
Event ID: 7102. Management Group: SCOM-PROD. Script: DiscoverSQL2008MirroringWitness.vbs. Version: 7.0.15.0. Instance: MSSQLSERVER : Mirroring witness discovery script 'DiscoverSQL2008MirroringWitness.vbs' for SQL instance 'MSSQLSERVER' failed. The next errors occurred: Cannot get target instance machine's NetBios host name. Computer name: SQLServer.domain.com Error number: -2147217392 Error description:Invalid class
Error 3:
Event ID: 7103. Management Group: SCOM-prod. Script: DiscoverSQL2012MirroringWitness.vbs. Version: 7.0.15.0. Instance: MSSQLSERVER : Mirroring witness discovery script 'DiscoverSQL2012MirroringWitness.vbs' for SQL instance 'MSSQLSERVER' failed. The next errors occurred: [Microsoft][SQL Server Native Client 11.0][SQL Server]The SELECT permission was denied on the object 'database_mirroring_witnesses', database 'mssqlsystemresource', schema 'sys'.
Error 4:
Log Name: Operations Manager Source: Health Service Script Date: 18-09-2019 08:16:52 Event ID: 4001 Task Category: None Level: Warning Keywords: Classic User: N/A Computer: SQLServer.domain.com Description: Management Group: scom-prod. Script: GetSQL2012DBFreeSpace.vbs. Version: 7.0.15.0 : The next errors occurred: Cannot connect to database 'model' Error Number: -2147467259 Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal "domain\sqlsvc" is not able to access the database "model" under the current security context. Instance: MSSQLSERVER
I will discuss here each scenario which will cover all the errors we are having in the SQL server.
So firstly, we will run a ProcMon Trace from the SQL server to identify the account which runs all the SQL workflows for that server.
Steps:
- Now download the following tool and copy it in the server
https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
- Now run the tool with admin privilege.
- Now restart the Microsoft Monitoring agent on the server.
- Please wait for the event listed above.
Once we have the event reproduced, stop the ProcMon trace and save it.
So now looked at the ProcMon trace to check the account details as below.
We could see that the workflow is running under Domain\sqlsvc account.
Now we need to run the following SQL queries using the same account. You can elevate wbemtest or you can run it from PowerShell.exe by elevating the PowerShell windows with the same account.
PS C:\Users\sqlsvc> whoami lab\sqlsvc PS C:\Users\sqlsvc> Get-WmiObject -Namespace "root\microsoft\sqlserver\computermanagement13" -Query "select * from SqlService where SQLServiceType ='1'" __GENUS : 2 __CLASS : SqlService __SUPERCLASS : __DYNASTY : SqlService __RELPATH : SqlService.ServiceName="MSSQLSERVER",SQLServiceType=1 __PROPERTY_COUNT : 15 __DERIVATION : {} __SERVER : SQLServer __NAMESPACE : root\microsoft\sqlserver\computermanagement13 __PATH : \\SQLServer\root\microsoft\sqlserver\computermanagement13:SqlService.ServiceName="MSSQLSERVER",SQLServiceType=1 AcceptPause : True AcceptStop : True BinaryPath : "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER Dependencies : {SQLSERVERAGENT} Description : Provides storage, processing and controlled access of data, and rapid transaction processing. DisplayName : SQL Server (MSSQLSERVER) ErrorControl : 1 ExitCode : 0 HostName : SQLServer ProcessId : 10472 ServiceName : MSSQLSERVER SQLServiceType : 1 StartMode : 2 StartName : LocalSystem State : 4 PSComputerName : SQLServer PS C:\Users\sqlsvc>
N.B: To get wmi namespace, you need to go to wmimgmt.msc >> WMI Control (Local) >> Properties >> Security >> Root >> Microsoft >> ComputerManagement13
You need to run the following commands as well to check if we have any failure.
Get-WmiObject -Namespace "root\microsoft\sqlserver\computermanagement13" -Query "select * from SqlService where SQLServiceType ='1'" Get-WmiObject -Namespace "root\microsoft\sqlserver\computermanagement13" -Query "Select * from SqlService" Get-WmiObject -Namespace "root\microsoft\sqlserver\computermanagement13" -Query "Select * from SqlServiceAdvancedProperty" Get-WmiObject -Namespace "root\microsoft\sqlserver\computermanagement13" -Query "Select * from ServerNetworkProtocolProperty" Get-WmiObject -Namespace "root\microsoft\sqlserver\computermanagement13" -Query "Select * from ServerNetworkProtocol" Get-WmiObject -Namespace "root\microsoft\sqlserver\computermanagement13" -Query "select * from SqlService where SQLServiceType ='9' or SQLServiceType ='3'"
The above queries might fail with the error called Invalid WMI class.
In that case, the resolution would be to recompile the MOF file.
For SQL 2008, please run the below command from elevated CMD as administrator.
cd “C:\Program Files (x86)\Microsoft SQL Server\100\Shared” mofcomp sqlmgmproviderxpsp2up.mof
For SQL 2012, please run the below command from elevated CMD as administrator.
cd “C:\Program Files (x86)\Microsoft SQL Server\110\Shared” mofcomp sqlmgmproviderxpsp2up.mof
For SQL 2014, please run the below command from elevated CMD as administrator.
cd “C:\Program Files (x86)\Microsoft SQL Server\120\Shared” mofcomp sqlmgmproviderxpsp2up.mof
For SQL 2016, please run the below command from elevated CMD as administrator.
cd “C:\Program Files (x86)\Microsoft SQL Server\130\Shared” mofcomp sqlmgmproviderxpsp2up.mof
For SQL 20017 please run the below command from elevated CMD as administrator.
cd “C:\Program Files (x86)\Microsoft SQL Server\140\Shared” mofcomp sqlmgmproviderxpsp2up.mof
For SQL 2019, please run the below command from elevated CMD as administrator.
cd “C:\Program Files (x86)\Microsoft SQL Server\150\Shared” mofcomp sqlmgmproviderxpsp2up.mof
If we have found any access related issues in WMI as access denied in ProcMon, we can provide the below permission to the SQL account Domain\sqlsvc.
grant the “Execute Methods”, “Enable Account”, “Remote Enable”, “Read Security” permissions on the following WMI for the account Domain\sqlsvc for both nodes.
root
root\cimv2
root\default
Root\Microsoft\SqlServer
root\Microsoft\SqlServer\ComputerManagement14
If we have any access issue with registry, please grant read access to the below registry paths for the SQL account.
“HKLM:\Software\Microsoft\Microsoft SQL Server”
“HKLM:\Software\Microsoft\Microsoft SQL Server\[InstanceID]\MSSQLServer\Parameters”
If we have any issues with the database permission and we would like to restrict the account permission, please follow the following article to grant the permission for the SQL account Domain\sqlsvc.
https://www.souravmahato.com/set-up-sql-monitoring-with-minimum-rights-in-scom/
I hope this will help to identify the issue for SQL workflow. I would appreciate if you could provide any feedback in this. I am looking forward to hearing from you.
Cheers!