SQL Server Agent PowerShell job step

I have had some problems executing a PowerShell script in a SQL Server Agent job step.
The errormessage I got – several times – was
Executed as user: SYS\ServiceDefaultSQL. The process could not be created for step 1 of job 0x5D25506B0FA13F45A770C7CD8B3BA051 (reason: The system cannot find the file specified). The step failed.
After some experiments I thought there was a NTFS security problem, so that the domain account the SQL Server Agent service was running in didn't have access rights to the PowerShell filestructure. At my test environment PowerShell is installed after the SQL Server services.
While I was browsing the NTFS rights, I asked a friend (Jakob Bindslet). He pointed me right to the solution.

The solution is somewhat awkward, while it's based on calling cmd.exe inside a CmdExec job step with the command
cmd.exe /c %SystemRoot%\system32\windowspowershell\v1.0\powershell.exe -NoLogo -NoProfile -Noninteractive "&'Z:\SQLAdmin\Jobs\HelloWorld.ps1'"
After beeing pointed in the right direction, I found out this is also working
C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe -NoLogo -NoProfile -Noninteractive "&'T:\SQLAdmin\Jobs\HelloWorld.ps1'"

Well – actually the documentation in Books Online clearly states that I "must provide the full path to the executable if the executable is not located in a directory specified in the system path or the path for the user that the job step runs as." (Creating Job Steps).
I think a downside could be the static path, that might not be alike for other Windows hosts.
But at my job we are using a single SQL Server Agent service for the entire production environment. This makes the last statement with the full PowerShell path usefull.

It does not work when using %ComSpec% or %SystemRoot%\system32\ because the environmental variables is not known to the SQL Server Agent job step.
BTW - the variables known to a job step is called tokens and macros. You can read more in Books Online > Using Tokens in Job Steps.

No comments: