SQL Server SQLPS powershell module fails connection to SQL 2012 instance - by Robert Klimes

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.


35
0
Sign in
to vote
ID 1138754 Comments
Status Closed Workarounds
Type Bug Repros 10
Opened 2/19/2015 10:14:53 AM
Access Restriction Public

Description

On a workstation that only has SQL Server 2014 tools installed using the SQLPS powershell mudule to connect to a SQL Server 2012 instance using "cd SQLSERVER:\sql\servername" results in the following.
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'servername' failed with the following error: SQL Server WMI provider is not available on servername. --> Invalid namespace

This appears to be because the wmi provider checks for the computermanagement namespaces. it tries to connect to the namespaces root\Microsoft\sqlserver\computermanagement12, root\Microsoft\sqlserver\computermanagement10, root\Microsoft\sqlserver\computermanagement which are 2014, 2008, 2005 respectively. It does not try to connect to root\Microsoft\sqlserver\computermanagement11 which is the 2012 namespace. 

If remote server was upgraded to 2012 then connections works as the root\Microsoft\sqlserver\computermanagement10 namespace still exists. If 2012 tools exist on workstation then connection works. 
Sign in to post a comment.
Posted by Microsoft on 4/27/2017 at 5:25 PM
The fix for this issue is included in the latest SqlServer module (now published to the PSGallery - not included with SSMS anymore).

Here's the latest version of the module (as of today): https://www.powershellgallery.com/packages/SqlServer/21.0.17099

Thanks,
-Matteo
Posted by John Couch on 1/8/2017 at 11:26 AM
How is this resolved/fixed? I see no resolution posted.
Posted by G787 on 6/16/2016 at 3:35 PM
My script to change service account password no longer works against SQL 2012 servers once SQL 2014 tools were installed. Actually it only works for SQL 2008 R2 now. If I execute the same script on a server with SQL 2012 tools, it functions properly against SQL 2008 R2 and SQL 2012.
Posted by Microsoft on 6/3/2016 at 6:33 PM
Hi Cody,
I just noticed this connect item. I apologize for the delay!
Let me take a look at this issue and get back to you.

Thanks,
-Matteo
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:13 PM
Also still broken exactly as described in SQL 2016 RC 3.
Posted by Cody Konior on 5/11/2016 at 11:06 PM
What "resolved it" for Dimitri is a completely different issue. Michael Bourgon is right that the two Connect items are caused by the same issue; an incorrect WMI path. SQL 2014 SMO can only query SQL 2008 R2 and under - it CANNOT connect to SQL 2012 because its BROKEN. SQL 2016 CANNOT connect to SQL 2012 OR SQL 2014 because it's BROKEN.

To get it working you have to literally decompile/hack/recompile the DLL. Method here: http://www.codykonior.com/2015/05/30/whats-wrong-with-sqlps/
Posted by DimitriKoens on 4/4/2016 at 2:02 PM
This resolved it for me:
https://support.microsoft.com/en-us/kb/956013
Posted by mbourgon on 3/20/2015 at 7:46 AM
Seems related to https://connect.microsoft.com/SQLServer/feedback/details/1070235/sql-2014-smo-cant-query-sql-2012-instances-sql-server-wmi-provider-is-not-available