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

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 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.

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 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.

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 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.

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.

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.

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

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.

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.

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.

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

Conclusions

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