What's wrong with SQLPS?

Updated 2017-04-26: This appears to be fixed in SSMS 2017.01 and the PowerShell Gallery version of the SqlServer module.

Updated 2016-01-13: Added SQL Server 2016 comments and created a better, safer method of modifying the broken DLL.

I've seen a few reports of an error occurring when you use the SQL Server 2014 PowerShell provider to connect to an SQL Server 2012 instance. I have also encountered a few issues like this and did some investigation into the root cause.

To reproduce the issue I set up two VMs in a simple domain, and a PowerShell script to execute on each and test the result of connecting to themselves and each other.

Host Name	Windows Version	SQL Version
W08R2S12	Windows Server 2008 R2	SQL Server 2012
W08R2S14	Windows Server 2008 R2	SQL Server 2014

Import-Module SQLPS -DisableNameChecking

"W08R2S12", "W08R2S14" | %{
	Write-Host "Testing $($env:COMPUTERNAME) to $_"
	Test-Path SQLSERVER:SQL\$_
}

Testing W08R2S12 to W08R2S12
True
Testing W08R2S12 to W08R2S14

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S14' failed with the following error: SQL Server WMI provider is not available on W08R2S14. --> Invalid namespace 

False

Testing W08R2S14 to W08R2S12

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'W08R2S12' failed with the following error: SQL Server WMI provider is not available on W08R2S12. --> Invalid namespace

False
Testing W08R2S14 to W08R2S14
True

On the second bug report it was mentioned that it's caused by an incorrect path being used in the WMI connection.

I downloaded ILSpy and started digging through the SMO DLLs and was lucky to find the relevant section of code in Microsoft.SqlServer.SqlWmiManagement.dll. It's stored in both C:\Program Files (x86)\SQL Server\120\SDK\Assemblies and more importantly in the GAC.

Common Code

// Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
private void TryConnect()
{
	Exception ex = this.TryConnectUsingPath(ManagedComputer.GetManagementPath(base.Name));
	if (ex != null)
	{
		ex = this.TryConnectUsingPath(ManagedComputer.GetManagementPathKatmai(base.Name));
		if (ex != null)
		{
			ex = this.TryConnectUsingPath(ManagedComputer.GetManagementPathPreKatmai(base.Name));
		}
	}
	if (ex != null)
	{
		throw ex;
	}
}

// Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
internal static ManagementPath GetManagementPathKatmai(string machineName)
{
	return new ManagementPath(string.Format(SmoApplication.DefaultCulture, "\\\\{0}\\ROOT\\Microsoft\\SqlServer\\ComputerManagement10", new object[]
	{
		machineName
	}));
}

// Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
internal static ManagementPath GetManagementPathPreKatmai(string machineName)
{
	return new ManagementPath(string.Format(SmoApplication.DefaultCulture, "\\\\{0}\\ROOT\\Microsoft\\SqlServer\\ComputerManagement", new object[]
	{
		machineName
	}));
}

Code in SQL Server 2012

// Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
internal static ManagementPath GetManagementPath(string machineName)
{
	return new ManagementPath(string.Format(SmoApplication.get_DefaultCulture(), "\\\\{0}\\ROOT\\Microsoft\\SqlServer\\ComputerManagement{1}", new object[]
	{
		machineName,
		11.ToString(CultureInfo.InvariantCulture)
	}));
}

Code in SQL Server 2014

// Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
internal static ManagementPath GetManagementPath(string machineName)
{
	return new ManagementPath(string.Format(SmoApplication.DefaultCulture, "\\\\{0}\\ROOT\\Microsoft\\SqlServer\\ComputerManagement{1}", new object[]
	{
		machineName,
		12.ToString(CultureInfo.InvariantCulture)
	}));
}

Code Explanation

It appears that the TryConnect() function calls a few different sub-functions in order to test out possible paths (which vary by a version number suffix) in order to retain backwards compatibility.

Unfortunately in SQL Server 2014 the programmers at Microsoft made a mistake and instead of adding another function wrapper with another test of the new version number, they've just replaced the existing "11" with "12" and broken compatibility between 2014 to 2012.

They've done the same thing in SQL Server 2016 CTP 3.2. So now we have multiple incompatible versions:

2012 can speak to <= 2012
2014 can speak to < 2012 and 2014
2016 can speak to < 2012 and 2016

I really hope they fix it themselves before RTM. However since we've been waiting for a few years now, I've come up with an alternate method of patching their DLLs.

Building a fix for SQL Server 2014

Find and backup the latest version of Microsoft.SqlServer.SqlWmiManagement.dll in your C:\Windows\assembly\GAC_MSIL directory.

# Where is it?
Get-ChildItem "C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement" "Microsoft.SqlServer.SqlWmiManagement.dll" -Recurse | 
Sort { $_.VersionInfo.ProductVersion } -Descending

# Backup the above DLL and renaming the original (it's probably locked)
Copy-Item C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll C:\Temp\Microsoft.SqlServer.SqlWmiManagement_Old.dll
Rename-Item C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll Microsoft.SqlServer.SqlWmiManagement.dll.bak -ErrorAction:SilentlyContinue

Start up one of the Visual Studio Tools Command Prompts and decompile the assembly using ildasm.exe. It comes with the free editions, as well, don't worry.

ildasm /all C:\Temp\Microsoft.SqlServer.SqlWmiManagement_Old.dll /out=C:\Temp\Microsoft.SqlServer.SqlWmiManagement_Old.il

copy C:\Temp\Microsoft.SqlServer.SqlWmiManagement_Old.il C:\Temp\Microsoft.SqlServer.SqlWmiManagement_Patched.il

Make the following changes to Microsoft.SqlServer.SqlWmiManagement_Patched.il:
- Duplicate the GetManagementPath() function into a new GetManagementPath2012() function.
- In the GetManagementPath2012() function change ldc.i4.s 12 to ldc.i4.s 11.
- In the TryConnect() function copy labels IL_0000 through IL_0013 into a new block directly below it.
- In this new block add suffixes to the labels; e.g. to become IL_0000a:, IL_0001b:, etc.
- In this new block change the GetManagementPath() function call to be GetManagementPath2012().

Compile it.

ilasm /dll C:\Temp\Microsoft.SqlServer.SqlWmiManagement_Patched.il /output=C:\Temp\Microsoft.SqlServer.SqlWmiManagement_Patched.dll

Copy the new DLL into the GAC manually. You may also need to remove the native images.

# Replace main DLL
Copy-Item C:\Temp\Microsoft.SqlServer.SqlWmiManagement_Patched.dll C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll

# Rename the native images (compiled MSIL) which take precedence
# If the Remove-File fails, it means something has locked the DLL. Exit PowerShell sessions and don't load SQLPS. Then the removal should work.
Get-ChildItem -Recurse C:\Windows\assembly -Include @("Microsoft.SqlServer.SqlWmiManagement.ni.dll", "Microsoft.SqlServer.SqlWmiManagement.ni.dll.aux") | %{
  Copy-Item $_.FullName "$($_.FullName).bak" -ErrorAction SilentlyContinue
  Remove-Item $_.FullName
}

Here's a unified patch, in case it's useful, and clear…

--- C:/Temp/Microsoft.SqlServer.SqlWmiManagement_12.0.0.0.il	Wed Jan 20 20:49:24 2016
+++ C:/Temp/Microsoft.SqlServer.SqlWmiManagement_12.0.0.0.Patched.il	Wed Jan 20 20:50:39 2016
@@ -5578,6 +5578,41 @@
     IL_0032:  /* 2A   |                  */ ret
   } // end of method ManagedComputer::GetManagementPath
 
+  .method /*06000032*/ assembly hidebysig static 
+          class [System.Management/*23000004*/]System.Management.ManagementPath/*01000018*/ 
+          GetManagementPath2012(string machineName) cil managed
+  // SIG: 00 01 12 61 0E
+  {
+    // Method begins at RVA 0x2fb0
+    // Code size       51 (0x33)
+    .maxstack  6
+    .locals /*1100000D*/ init (object[] V_0,
+             int32 V_1)
+    IL_0000:  /* 28   | (0A)000021       */ call       class [mscorlib/*23000003*/]System.Globalization.CultureInfo/*01000046*/ [Microsoft.SqlServer.Smo/*23000002*/]Microsoft.SqlServer.Management.Smo.SmoApplication/*01000045*/::get_DefaultCulture() /* 0A000021 */
+    IL_0005:  /* 72   | (70)000277       */ ldstr      "\\\\{0}\\ROOT\\Microsoft\\SqlServer\\ComputerManagement{1}" /* 70000277 */
+    IL_000a:  /* 18   |                  */ ldc.i4.2
+    IL_000b:  /* 8D   | (01)000003       */ newarr     [mscorlib/*23000003*/]System.Object/*01000003*/
+    IL_0010:  /* 0A   |                  */ stloc.0
+    IL_0011:  /* 06   |                  */ ldloc.0
+    IL_0012:  /* 16   |                  */ ldc.i4.0
+    IL_0013:  /* 02   |                  */ ldarg.0
+    IL_0014:  /* A2   |                  */ stelem.ref
+    IL_0015:  /* 06   |                  */ ldloc.0
+    IL_0016:  /* 17   |                  */ ldc.i4.1
+    IL_0017:  /* 1F   | 0C               */ ldc.i4.s   11
+    IL_0019:  /* 0B   |                  */ stloc.1
+    IL_001a:  /* 12   | 01               */ ldloca.s   V_1
+    IL_001c:  /* 28   | (0A)00007B       */ call       class [mscorlib/*23000003*/]System.Globalization.CultureInfo/*01000046*/ [mscorlib/*23000003*/]System.Globalization.CultureInfo/*01000046*/::get_InvariantCulture() /* 0A00007B */
+    IL_0021:  /* 28   | (0A)00007C       */ call       instance string [mscorlib/*23000003*/]System.Int32/*01000062*/::ToString(class [mscorlib/*23000003*/]System.IFormatProvider/*0100004A*/) /* 0A00007C */
+    IL_0026:  /* A2   |                  */ stelem.ref
+    IL_0027:  /* 06   |                  */ ldloc.0
+    IL_0028:  /* 28   | (0A)000028       */ call       string [mscorlib/*23000003*/]System.String/*01000049*/::Format(class [mscorlib/*23000003*/]System.IFormatProvider/*0100004A*/,
+                                                                                                                      string,
+                                                                                                                      object[]) /* 0A000028 */
+    IL_002d:  /* 73   | (0A)00007D       */ newobj     instance void [System.Management/*23000004*/]System.Management.ManagementPath/*01000018*/::.ctor(string) /* 0A00007D */
+    IL_0032:  /* 2A   |                  */ ret
+  } // end of method ManagedComputer::GetManagementPath
+
   .method /*06000033*/ assembly hidebysig static 
           class [System.Management/*23000004*/]System.Management.ManagementPath/*01000018*/ 
           GetManagementPathKatmai(string machineName) cil managed
@@ -5949,6 +5984,15 @@
     IL_0011:  /* 0A   |                  */ stloc.0
     IL_0012:  /* 06   |                  */ ldloc.0
     IL_0013:  /* 2C   | 27               */ brfalse.s  IL_003c
+
+    IL_0000a: /* 02   |                  */ ldarg.0
+    IL_0001a: /* 02   |                  */ ldarg.0
+    IL_0002a: /* 28   | (06)000010       */ call       instance string Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject/*02000003*/::get_Name() /* 06000010 */
+    IL_0007a: /* 28   | (06)000032       */ call       class [System.Management/*23000004*/]System.Management.ManagementPath/*01000018*/ Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer/*02000006*/::GetManagementPath2012(string) /* 06000032 */
+    IL_000ca: /* 28   | (06)000038       */ call       instance class [mscorlib/*23000003*/]System.Exception/*01000019*/ Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer/*02000006*/::TryConnectUsingPath(class [System.Management/*23000004*/]System.Management.ManagementPath/*01000018*/) /* 06000038 */
+    IL_0011a: /* 0A   |                  */ stloc.0
+    IL_0012a: /* 06   |                  */ ldloc.0
+    IL_0013a: /* 2C   | 27               */ brfalse.s  IL_003c
 
     IL_0015:  /* 02   |                  */ ldarg.0
     IL_0016:  /* 02   |                  */ ldarg.0

The changes would be very similar for 2016; simply add two more blocks in the same way; though always leave the base GetManagementPath() function untouched; as it's used elsewhere in the DLL and should always refer to the "current" version.

Rollback

# Restore main DLL
Copy-Item C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll.bak C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll

# Restore native images
Get-ChildItem -Recurse C:\Windows\assembly -Include @("Microsoft.SqlServer.SqlWmiManagement.ni.dll.bak", "Microsoft.SqlServer.SqlWmiManagement.ni.dll.aux.bak") | %{
	Copy-Item $_.FullName $_.FullName.Substring(0, $_.FullName.Length - 4)
}

Conclusion

Why can you just modify files in the GAC and have it work; doesn't it check signatures and stuff? Well I found a comment in this blog which may explain it:

Here's the result which is extremely satisfying!

Testing W08R2S14 to W08R2S12
True
Testing W08R2S14 to W08R2S14
True

Drops microphone.