Running PowerShell in a SQL Agent Job

When creating a SQL Agent Job to execute a PowerShell script, you have to decide which way that you want the PowerShell to run. Depending upon which version of SQL Server that you are using and which job step type that you choose, you might be running in different versions of PowerShell with different execution policies. I will demonstrate the behaviors.

tl;dr

Executable Execution Policy PowerShell Version
2008 R2 – PowerShell type SQLPS.exe RemoteSigned 2.0
2008 R2 – CmdExec type powershell.exe via cmd.exe System Configured Latest Installed
2012 – PowerShell type SQLPS.exe RemoteSigned 2.0
2012 – CmdExec type powershell.exe via cmd.exe System Configured Latest Installed
2014 – PowerShell type SQLPS.exe RemoteSigned 4.0
2014 – CmdExec type powershell.exe via cmd.exe System Configured Latest Installed
2016 – PowerShell type SQLPS.exe RemoteSigned 4.0
2016 – CmdExec type powershell.exe via cmd.exe System Configured Latest Installed

Demonstration

First we’ll create a simple PowerShell script which will output the executable path and script path, PowerShell version and execution policy to a log file. We will then run this script through the SQL Agent on a SQL Server 2008 R2, 2012, and 2014 instance. We will also test different job step types.

param([string]$filePath)
[environment]::commandline | Out-File $filePath
$version = $PSVersionTable.PSVersion
"Version: $version" | Out-File $filePath -Append
[string]$executionPolicy = Get-ExecutionPolicy
"Execution Policy: $executionPolicy" | Out-File $filePath -Append

For setup purposes, this is what my PowerShell environment looks like outside of SQL Server. NOTE: The powershell.exe is being used, we are using PowerShell version 4.0, and the system execution policy is set to Unrestricted.

Test-PoSh-Console

Test-PoSh-Console-log

Test 1: SQL Server 2008 R2 using PowerShell job step

On a SQL Server 2008 R2 instance I have created a job with a single step which is configured as follows. Note the Type selection.

Test-PoSh-2008R2

Here is the output. What you will notice is that SQL Server 2008 R2 has taken all of our system configurations out of our hands. Powershell.exe is no longer even being called, instead SQLPS.exe is being called. SQLPS.exe is a mini-shell which was made before the SQLPS module was created and is compiled with a limited sub-set of PowerShell version 2.0 cmdlets. See more details here. The mini-shell is also run in RemoteSigned mode, regardless of the system configured value.

Test-PoSh-2008R2-log

Test 2: SQL Server 2008 R2 using Operating System job step

In this test we will experiment with the Operating System (CmdExec) job step with no other changes except for modifying the script for cmd.exe syntax.

Test-cmdln-2008R2

As you can see below, by using the Operating System job step, we have taken back control over the system settings and ran the script in our currently install version of PowerShell. What is important to note with this configuration is that, in 2008 R2, this is the only way that you can use a version of PowerShell other than 2.0. Also, it is the only way for you to use the system defined setting for execution policy.

Test-cmdln-2008R2-log

Test 3: SQL Server 2012 using PowerShell job step

Now we will redo test #1 but, this time, it will run on a SQL Server 2012 instance. We expect to see a behavior change because, with version 2012, the SQLPS module was created which removed the need of the mini-shell for assembly packaging.

Test-PoSh-2012

agent-2012

The results here were unexpected. I was having a discussion with Allen White (b | t) several months ago and our belief was that SQL Server 2012 would use the native PowerShell.exe rather than SQLPS.exe. This appears to not be true, I wonder if SQL Server 2014 implemented that feature or not.

allen-white-posh-agent-tweets

Test 4: SQL Server 2012 using Operating System job step

In this test we will be using the Operating System (CmdExec) method again, just in SQL Server 2012. Here we expect no change from test #2 because both are essentially using cmd.exe and SQL Server versions do not affect cmd.exe at all.

Test-cmdln-2012

As expected, this method, in SQL Server 2012, grants you full control of the version and settings at the system level.

Test-cmdln-2012-log

Test 5: SQL Server 2014 using PowerShell job step

Once again we will redo test #1 but, this time, it will run on a SQL Server 2014 instance.

agent-2014-step

What is interesting here is that SQLPS.exe is still used but the PowerShell Version is 4.0. It appears that the SQLPS.exe mini-shell was updated with SQL Server 2014 to use the newer PowerShell version rather than start using PowerShell.exe. It is my guess that this was the chosen method because SQLPS.exe guarantees that all of the appropriate assemblies are available for interaction with SQL Server. We wouldn’t have to add initialization to our scripts such as Import-Module SQLPS -DisableNameChecking.

agent-2014

Test 6: SQL Server 2014 using Operating System job step

In this test we will be using the Operating System (CmdExec) method once again, just in SQL Server 2014. Here we expect no change from test #2 & #4 because both are essentially using cmd.exe and SQL Server versions do not affect cmd.exe at all.

Test-cmdln-2012

As expected, this method, in SQL Server 2014, grants you full control of the version and settings at the system level.

Test-cmdln-2012-log

Conclusions

Use the below table to identify how SQL Server Agent jobs will utilize PowerShell in different configurations.

Executable Execution Policy PowerShell Version
2008 R2 – PowerShell type SQLPS.exe RemoteSigned 2.0
2008 R2 – CmdExec type powershell.exe via cmd.exe System Configured Latest Installed
2012 – PowerShell type SQLPS.exe RemoteSigned 2.0
2012 – CmdExec type powershell.exe via cmd.exe System Configured Latest Installed
2014 – PowerShell type SQLPS.exe RemoteSigned 4.0
2014 – CmdExec type powershell.exe via cmd.exe System Configured Latest Installed
2016 – PowerShell type SQLPS.exe RemoteSigned 4.0
2016 – CmdExec type powershell.exe via cmd.exe System Configured Latest Installed

This article has 2 comments

  1. […] to work on multiple rows”) Suggest Compression Strategies for Tables and Indexes Running PowerShell in a SQL Agent Job Bike Tour Business Intelligence: Part 4 [Populating a staging table] Avoid Scripting: Use […]

  2. Great article Derik, but I am afraid there is one factor you might have missed: The version of Windows the SQL Server is running on.
    My tests show that SQL Server 2014 running on Windows Server 2008R2, still uses PowerShell 2.0 and not 4.0.

Leave a Reply