SQL Server edition not recognized if SQL Server WMI provider is inaccessible

[#1]

Summary

If the SQL Server WMI provider cannot be accessed when the FlexNet inventory agent gathers inventory from a computer then a "SQL Server (Unknown component)" application may be the only SQL Server-related application that gets recognized as installed, and no application that identifies the installed SQL Server edition is recognized.

Troubleshooting

There can be a range of causes why the SQL Server WMI provider cannot be accessed, including the provider not being installed, or some problem in the WMI subsystem.

A Windows PowerShell command like the following can be executed (with local administrator rights) to attempt to perform similar WMI queries to those used by the FlexNet inventory agent during inventory gathering to identify the SQL Server edition. 

Get-WmiObject -Namespace 'root\Microsoft\SqlServer' __NAMESPACE |
? { $_.Name -match 'ComputerManagement' } |
%{
	$ns = "$($_.__NAMESPACE)\$($_.Name)"

	Write-Host "Querying namespace $ns"

	Get-WmiObject -Namespace $ns -Class SqlServiceAdvancedProperty -Filter "SqlServiceType = 1 AND PropertyName = 'SKUNAME'" |
	Select PSComputerName, ServiceName, PropertyName, PropertyStrValue
	
	Write-Host ""
}

Successful output from this script looks like the following:

Alternatively, the wbemtest tool can also be used to test the ability to query information from the SQL Server WMI provider:

1. On the computer where SQL Server is running, launch 'wbemtest' from the run menu.
   
   
2. Click Connect... and connect to the appropriate WMI namespace according to the version of SQL Server that is installed, as per the following table:

   SQL Server version	$namespace
   2005 & earlier	root\Microsoft\SqlServer\ComputerManagement
   2008	root\Microsoft\SqlServer\ComputerManagement10
   2012	root\Microsoft\SqlServer\ComputerManagement11
   2014	root\Microsoft\SqlServer\ComputerManagement12
   2016	root\Microsoft\SqlServer\ComputerManagement13
   2017	root\Microsoft\SqlServer\ComputerManagement14
   2019	root\Microsoft\SqlServer\ComputerManagement15

   
   
3. After connecting to the namespace, click Enum Instances... and do a search for "SqlServiceAdvancedProperty" in the superclass name field.
   
   Look for any objects named "MSSQL$<instance name>" with a ServiceType of 1 in the query  results:
   

If any of the above steps fail, the FlexNet inventory agent is unlikely to be able to gather sufficient details to be able to recognize the installed edition of SQL Server.

Remediation

The following page provides helpful guidance for remediating problems with the SQL Server WMI provider: Error message when you open SQL Server Configuration Manager in SQL Server: Cannot connect to WMI provider. You do not have permission or the server is unreachable

As described on that page, executing a "mofcomp" command similar to the following may be able to be used to configure the WMI provider:

SQL Server version	Command
2005 & earlier	mofcomp "%programfiles(x86)%\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof"
2008	mofcomp "%programfiles(x86)%\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"
2012	mofcomp "%programfiles(x86)%\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof"
2014	mofcomp "%programfiles(x86)%\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof"
2016	mofcomp "%programfiles(x86)%\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"
2017	mofcomp "%programfiles(x86)%\Microsoft SQL Server\140\Shared\sqlmgmproviderxpsp2up.mof"
2019	mofcomp "%programfiles(x86)%\Microsoft SQL Server\150\Shared\sqlmgmproviderxpsp2up.mof"

For example:

Additional Information

It's possible to verify the Edition value stored in the SKUNAME property in WMI as well by running the following query after connecting to the namespace in wbemtest:

select * from SqlServiceAdvancedProperty where SQLServiceType = 1 AND PropertyName = 'SKUNAME'

If there are no problems accessing the SQL Server WMI provider, another possible reason why a SQL Server (Unknown component) application is recognized is that the ARL does not contain a rule to recognize your specific SQL Server details. Flexera Support will be able to help identify this situation if you share an inventory NDI file from an affected computer.

[vincent_herrera]

Does this fix work for SQL server 2017?  If so what is the path for step4?

[MrPotato]

I have several hundred of the unknown component installations along with regular, recognized, edition installations. I can't possibly ask SQL team to cycle through all those devices. Isn't there any better workaround?

[MrPotato]

Also, is there anything that SQL can do to prevent this issue from happening while they deploy a new instance of the SQL? Prevention is better than cure.

[Ivettediazb]

I need this solution too, please!