SQL 2014 SMO can't query SQL 2012 instances: "SQL Server WMI provider is not available" - by JWeibler

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


51
0
Sign in
to vote
ID 1070235 Comments
Status Resolved Workarounds
Type Bug Repros 18
Opened 12/30/2014 2:36:08 AM
Access Restriction Public

Description

If you try to query SQL 2012 instances with powershell and the SMO you get the error "SQL Server WMI provider is not available on <hostname>" if you use the SQL 2014 SMOs.

See also http://finn.rivendel.be/?e=33 and https://sqlpowerdoc.codeplex.com/discussions/539457
Sign in to post a comment.
Posted by _dandy_ on 4/28/2017 at 1:59 PM
I've been trying to get the SQL 2012 baseline from Microsoft's Security Compliance Manager (https://www.microsoft.com/en-us/download/details.aspx?id=53353) to work for nearly a week now, but it always fails with the same "SQL Server WMI provider is not available" error. The machine running SQL 2012 is running a third-party product that installed SMO 2014 (and the corresponding CLR Types library from 2014 as well, but *only* those two components), and sure enough, that breaks the PowerShell scripts contained in the baseline. If I remove SMO 2014, it works. If I reinstall it, it breaks again.

No amount of patching 2012 or 2014 fixes it (up to the latest (as of April 2017) service pack and cumulative updates for both 2012 and 2014). Then I went back over the Steps to Reproduce section here, and noticed the reference to the 3 assemblies (Microsoft.SqlServer.Smo.dll, *.SmoExtended.dll and *.SqlWmiManagement.dll), all three under C:\Program Files (x86)\Microsoft\SQL Server\120\SDK\Assemblies.

Sure enough, they exist under the ...\110\SDK\Assemblies folder (from 2012), but NOT ...\120\SDK\Assemblies (from 2014), because I *only* have the SMO (+ CLR Types) libraries from 2014 installed. Just for testing purposes, I installed a full instance of SQL 2014 (fully patched), which--indeed--added the 3 missing files under ...\120\SDK\Assemblies, and the script started working again.

And again, just for testing, I installed SMO 2016 (+ CLR Types), and sure enough, it's also broken because it's now apparently trying to load files under ...\130\SDK\Assemblies, which are NOT there if you only install SMO/CLR Types.

I'm hoping this saves someone some time.

So what components installs these 3 assemblies? Obviously installing a full instance of SQL 2014 just to get them is complete overkill.
Posted by Microsoft on 1/30/2017 at 7:53 PM
This issue has been addressed/fixed. It will show up in the next SSMS release (likely 17.0).

Thanks,
-Matteo
Posted by John Couch on 1/8/2017 at 11:24 AM
Any idea on when this is getting fixed? I have 2014 tools installed and can't query any system running 2012 or 2014. It only works against 2008/R2.
Posted by Matt Beamish on 7/6/2016 at 5:52 PM
Issue appears to still be present in SMSS 2016 July release: https://i.imgur.com/6nLsK06.png
Posted by SQLLensman on 7/6/2016 at 1:03 AM
This issue still seems to be present in SQL Server 2016 RTM
Posted by Cody Konior on 5/12/2016 at 7:02 AM
Quick recap of the issue: There are SMO DLLs (also used by SQLPS) which use WMI to get some information from SQL Server (locally and remotely). This information is mostly anything in SQL Server Configuration Manager including getting back the services, adding trace flags, and pulling back endpoint, alias, and IP information.

In SQL 2014 it was broken so that it could no longer see SQL 2012. In SQL 2016 it's broken further so that it cannot see SQL 2012 or SQL 2014. It's responsible for the annoying extraneous SQLPS WMI errors you sometimes see when connecting to a server. The code that does this has been decompiled and provided to Microsoft, it's an obvious bug, easily fixed, and they just haven't done it.

Without it we cannot use SQLPS or SMO to reliably gather or alter this information from one server on multiple remote servers. Vote for this and help get it fixed. It needs attention.
Posted by Cody Konior on 5/11/2016 at 11:12 PM
Still broken exactly as described in SQL 2016 RC 3.
Posted by Cody Konior on 4/8/2016 at 12:09 AM
I've tested in SQL 2016 RC 2 and this is NOT fixed yet.

Dimitri will have had a different issue where one server has broken WMI. This Connect item is to track where SQLPS and SMO fail to use the proper WMI paths.
Posted by DimitriKoens on 4/4/2016 at 2:03 PM
This resolved it for me:
https://support.microsoft.com/en-us/kb/956013
Posted by Cody Konior on 1/10/2016 at 8:07 AM
Please fix or comment on this issue. It has happened again in SQL 2016 CTP 3.2 where you've overwritten the WMI path from 2014 to 2016.

So now we have:
- DLLs for 2012 that talk to 2012 and below.
- DLLs for 2014 that talk < 2012 and 2014.
- DLLs for 2016 that talk < 2012 and 2016.

It's all caused by the GetManagementPath function call. It makes it extremely difficult to get information about configurations from a varied server environment using the Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer class (it requires loading multiple sessions, one with each set of versioned DLLs, then trying to access everything in turn going down the line until something works).
Posted by Gerard Korthagen on 1/7/2016 at 5:07 AM
Hi,
What is the status of this bug, it's very annoying that I cannot communicate good with sql 2012 server with SQL 2014 client.

I installed SP1 and CU4 of SQL 2014 on the client but it still a problem..
Posted by Nancy Hidy Wilson1 on 4/28/2015 at 11:37 AM
The issue is also still there with RTM CU6 (12.00.2480).
Posted by JWeibler on 1/2/2015 at 2:08 AM
The issue is still there in SQL 2014 RTM CU5.
Posted by Sethu Srinivasan on 12/30/2014 at 4:30 PM
Hello JWeibler,
Let us know if you see this issue after applying SQL 2014 RTM CU5. You can request this CU from link http://support.microsoft.com/kb/3011055

Thanks
Sethu Srinivasan [MSFT]
SQL Server