How to Speed Up SQLPS Connection Without WMI Permissions

powershellsql server

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.

Best Answer

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.

Related Question