How can I speed SQLPS connection when I don't have WMI permissions at the server

Question

When connecting SQLPS to a SQL Server, it will try to run some WMI service status queries. In an environment where one does not have permissions for those, PowerShell issues a bunch of warnings, and it's VERY slow to connect.

It's possible to suppress the warning messages with $WarningPreference = 'SilentlyContinue', but is there any way to disable the actual checks, in order to speed performance?

Example:

PS SQLSERVER:\> set-location \sql\myserver\default WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Scenario: SQL Agent job using a PowerShell job step takes 30+ seconds just to spin up and connect to the local SQL Server, before doing any work.

Answer 1

Apparentely, after some testing...there is a way to speed up SQLPS load time (tremendously) and prevent the WMI warnings (if you don't need the SQLAS module loaded.

How I tested this on my laptop, was simply by shutting down the WMI service: Stop-Service winmgmt -Force. I then proceeded to edit the module files for SQLPS for my SQL Server 2012 local instance.

As soon as I load the module initially, I get the same warning messages you show in your question. The one that causes the specific problem is the SqlPsPostScript.PS1 file. This file is a nested module based on the module manifest, so everytime you load SQLPS this script is going to be executed.

This SqlPsPostScript.PS1 file is also why when the module is loaded it changes your location in your prompt. The first line of the file is Set-Location SQLSERVER:. [If you comment out that line it can also speed up the load time for the module a good bit.]

The lines that are causing the WMI warnings are from the two lines of code that will load the SQLAS module. Which if you look at the documentation for AS PowerShell in BOL it states it is loaded with SQLPS here. It also goes to mention when configuring remote administration for AS that in step 3 WMI Service has to be running. Which if you don't have access to WMI equates to the service not appear as running.

So in the end, as long as you don't need Analysis Services PowerShell module you can edit the SqlPsPostScript.PS1 file, found under:

C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\PowerShell\Modules\SQLPS\

And comment out the following lines:

$m = Get-Module -ListAvailable | where {$_.Name -eq "SQLASCmdlets"}
if($m -ne $null) { Import-Module $m -Global }

Once I did that and then saved the file, I no longer received the warnings when loading SQLPS.

Answer 2

I found the major culprit to this slow connection to be the port number (lack of port number) in the Set-Location command. That is, for a default instance on a custom port:

Set-Location \sql\$server\default\databases\mydb         #very slow
Set-Location \sql\$server`,$port\default\databases\mydb  #speedy quick

Escaping the comma is annoying, yet important. Not sure the back end detail about this, if it's hammering on ports or if it's slow to use the Browser service to resolve the port number, but providing the port number solves the performance issue reliably.