SQL Management Pack Troubleshooting: Basic

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!