{"id":105,"date":"2015-03-04T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/running-powershell-in-a-sql-agent-job"},"modified":"2026-03-18T21:51:38","modified_gmt":"2026-03-18T21:51:38","slug":"running-powershell-in-a-sql-agent-job","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/03\/04\/running-powershell-in-a-sql-agent-job\/","title":{"rendered":"Demonstration"},"content":{"rendered":"

When creating a\u00a0SQL Agent Job to execute\u00a0a 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.<\/p>\n

tl;dr<\/h2>\n

Executable
\nExecution Policy
\nPowerShell Version<\/p>\n

2008 R2 \u2013 PowerShell type
\nSQLPS.exe
\nRemoteSigned
\n2.0<\/p>\n

2008 R2 \u2013 CmdExec type
\npowershell.exe via cmd.exe
\nSystem Configured
\nLatest Installed<\/p>\n

2012 \u2013 PowerShell type
\nSQLPS.exe
\nRemoteSigned
\n2.0<\/p>\n

2012 \u2013 CmdExec type
\npowershell.exe via cmd.exe
\nSystem Configured
\nLatest Installed<\/p>\n

2014 \u2013 PowerShell type
\nSQLPS.exe
\nRemoteSigned
\n4.0<\/p>\n

2014 \u2013 CmdExec type
\npowershell.exe via cmd.exe
\nSystem Configured
\nLatest Installed<\/p>\n

Demonstration<\/h1>\n

First we\u2019ll 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.<\/p>\n

param([string]$filePath)\n[environment]::commandline | Out-File $filePath\n$version = $PSVersionTable.PSVersion\n\"Version: $version\" | Out-File $filePath -Append\n[string]$executionPolicy = Get-ExecutionPolicy\n\"Execution Policy: $executionPolicy\" | Out-File $filePath -Append<\/code><\/pre>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
Test 1: SQL Server 2008 R2 using PowerShell job step<\/h2>\n
On a SQL Server 2008 R2 instance I have created a job with a single step which is configured as follows. Note the\u00a0Type<\/strong><\/em> selection.<\/p>\n
\"\"<\/a><\/p>\n
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<\/a>. The mini-shell is also run in RemoteSigned mode, regardless of the system configured value.<\/p>\n
\"\"<\/a><\/p>\n
Test 2: SQL Server 2008 R2 using Operating System\u00a0job step<\/h2>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
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.<\/p>\n
\"\"<\/a><\/p>\n
Test 3: SQL Server 2012 using PowerShell job step<\/h2>\n
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\u00a0the mini-shell for assembly packaging.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/p>\n
The results here were unexpected. I was having a discussion with Allen White (b<\/a> | t<\/a>) 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.<\/p>\n
\"\"<\/a><\/p>\n
Test 4: SQL Server 2012 using Operating System job step<\/h2>\n
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\u00a0and SQL Server versions do not affect cmd.exe at all.<\/p>\n
\"\"<\/a><\/p>\n
As expected, this method, in SQL Server 2012, grants you full control of the version and settings at the system level.<\/p>\n
\"\"<\/a><\/p>\n
Test 5: SQL Server 2014 using\u00a0PowerShell job step<\/h2>\n
Once again we will redo test #1 but, this time, it will run on a SQL Server 2014 instance.<\/p>\n
\"\"<\/a><\/p>\n
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\u2019t have to add initialization to our scripts such as\u00a0Import-Module SQLPS -DisableNameChecking<\/em>.<\/p>\n
\"\"<\/a><\/p>\n
Test 6: SQL Server 2014 using Operating System job step<\/h2>\n
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\u00a0and SQL Server versions do not affect cmd.exe at all.<\/p>\n
\"\"<\/a><\/p>\n
As expected, this method, in SQL Server 2014, grants you full control of the version and settings at the system level.<\/p>\n
\"\"<\/a><\/p>\n
Conclusions<\/h2>\n
Use the below table to identify how SQL Server Agent jobs will utilize PowerShell in different configurations.<\/p>\n
Executable
\nExecution Policy
\nPowerShell Version<\/p>\n
2008 R2 \u2013 PowerShell type
\nSQLPS.exe
\nRemoteSigned
\n2.0<\/p>\n
2008 R2 \u2013 CmdExec type
\npowershell.exe via cmd.exe
\nSystem Configured
\nLatest Installed<\/p>\n
2012 \u2013 PowerShell type
\nSQLPS.exe
\nRemoteSigned
\n2.0<\/p>\n
2012 \u2013 CmdExec type
\npowershell.exe via cmd.exe
\nSystem Configured
\nLatest Installed<\/p>\n
2014 \u2013 PowerShell type
\nSQLPS.exe
\nRemoteSigned
\n4.0<\/p>\n
2014 \u2013 CmdExec type
\npowershell.exe via cmd.exe
\nSystem Configured
\nLatest Installed<\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
When creating a\u00a0SQL Agent Job to execute\u00a0a 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-105","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/105","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=105"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/105\/revisions"}],"predecessor-version":[{"id":367,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/105\/revisions\/367"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}