An interesting issue came across my desk this week. One of our clients has a number of PowerShell tasks running on SQL Server Agent job. One day, the jobs suddenly stopped working due to the error below: [caption id="attachment_104229" align="aligncenter" width="1351"]
Powershell Task Fails on SQL Server Job[/caption]
Note: If the registry key for the SQLPS execution policy does not exist, you won't be able to load SQLPS and the execution policy for the process scope will toggle to restricted. You would receive an error similar to below:
On the left, PowerShell type job step. This will fail if execution policy for SQLPS is set to restricted or does not exist. On the right is the workaround.[/caption]
Note: Microsoft.SqlServer.Management.PowerShell.sqlps### the ### will change depending on your SQL server version. You can change the ExecutionPolicy key The default value is RemoteSigned.
There may be instances when the key is completely removed, as was the case with our client. To get it working again, you'll need to add the key back to fix SQLPS. Make sure the path value points to your actual sqlps.exe location.
Powershell Task Fails on SQL Server Job[/caption]
Message Executed as user: NT Service\SQLSERVERAGENT. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'File C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170. File C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170. '. Process Exit Code -1. The step failed.
Problem:
SQL Job failing with sqlps.ps1 Cannot be Loaded Because Running Scripts is Disabled on this System.Probable cause:
From the error itself, you can see that it can't load the SQLPS module due to execution policy. That means the execution policy for process scope on the server is set to a strict policy, restricted or signed. Some possible scenarios: a. GPO Policy - These security settings on the server can be overridden and set by GPO policy on your domain. If this is the case, you'll need to coordinate with your system admin and negotiate the proper policy that you require. If you can't get the policy changed, you can check the workaround below. b. The policy was changed or removed manually on the server or by a SQL-related installation.How to check:
Execution policy has different scopes. You can check the settings by running get-executionpolicy -list. If you are running it via the SQL server job agent, then the policy that will apply is process scope. Try checking the policy on PowerShell. Take note of the value and enter the SQLPS module by issuing sqlps command. Notice the change in the Execution Policy for the scope "Process." This is the Execution Policy configured for the SQLPS mini-shell. PowerShell type job steps will run using this policy since it will run inside the SQLPS mini-shell.
Note: If the registry key for the SQLPS execution policy does not exist, you won't be able to load SQLPS and the execution policy for the process scope will toggle to restricted. You would receive an error similar to below:
import-module : File C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170. At line:1 char:1 + import-module SQLPS + ~~~~~~~~~~~~~~~~~~~ + CategoryInfo : SecurityError: (:) [Import-Module], PSSecurityException + FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand
Workaround:
A quick workaround to the issue without messing around with the policy settings on the server is to bypass the execution policy. You can do this by saving your code into a PowerShell script file and converting the SQL job step into a cmdexec step type and writing it as such: powershell.exe -ExecutionPolicy Bypass ."Script path here" [caption id="attachment_104235" align="aligncenter" width="1483"] On the left, PowerShell type job step. This will fail if execution policy for SQLPS is set to restricted or does not exist. On the right is the workaround.[/caption]
Fix:
As mentioned above, if this is a GPO you'll need to get your system admin to change the policy. Even if you change it on the server, the GPO will just change it back. To fix the issue, you'll have to change the policy for SQLPS. SQLPS gets its execution policy from the registry: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps130
Note: Microsoft.SqlServer.Management.PowerShell.sqlps### the ### will change depending on your SQL server version. You can change the ExecutionPolicy key The default value is RemoteSigned.
There may be instances when the key is completely removed, as was the case with our client. To get it working again, you'll need to add the key back to fix SQLPS. Make sure the path value points to your actual sqlps.exe location.
Side bar:
If you ask any decent DBA about running PowerShell tasks through SQL server job as a PowerShell type job step, the advice will always be - don't. PowerShell type steps calls the SQLPS mini-shell and runs your command from there. SQLPS for SQL 2012 and older versions do not play nice with other modules. A better way is to run it as a CmdExec type for a few reasons:-
- This way your code is running inside PowerShell instead of SQLPS mini-shell.
- The script and the job are more maintainable. If you need to change or update your scripts, there is no need to modify the SQL job, just the PowerShell script file.
- Copy/pasting commands to the job step properties or T-SQL is prone to typographical error and there is no parse to check your script.
- Error catching from PowerShell in SQL server agent is not reliable unless you do a try-catch or specify the -erroraction parameter.
