Problem
As I was bored this weekend, I decided to nose around the logs files on some of my servers and I noticed that SYSPOLICY_PURGE_HISTORY SQL Server Agent Job had started failing. I don’t really work much with SQL Server 2014 so I did a little research to see what the problem might be. The SQL server was trying to run a PowerShell script to do the maintenance and was failing because of the PowerShell restriction policy on this machine.
Solution
In SQL Server 2014 SQL PS tries to load module SQLPS but the execution policy is set to “Restricted” at the process level. Manually setting the execution policy to allow PowerShell scripts to run doesn’t apply to the environment that SQL server is running in. The work around I found was to set a registry value to overcome this.
The problem is the registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120” either does not exist or contains the value “ExecutionPolicy”=”Restricted”. In both cases, no PS-scripts can be executed and SSMS-Jobs scripted with PS like “syspolicy_purge_history” will fail.
Changing this Registry entry solves the problem by resetting the ExecutionPolicy to the same level as the PowerShell command shell:
Add or change the following registry key as listed below.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120] “ExecutionPolicy”=”RemoteSigned”Once you have made the above-mentioned change, exit the registry editor and run the SYSPOLICY_PURGE_HISTORY Job and you should see it complete without errors.
