SYSPOLICY_PURGE_HISTORY job fails in SQL Server 2014

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.

About The Author

I have worked in the computer repair field since the early '90s. I was a technician and then Coordinator of Computer Repair for Regional Education Service Agency II (RESA 2) for 18 years. I am currently working for Wayne County Schools as a Network Engineer/Technology Specialist. I am responsible for the schools' Internet access, Wi-Fi, networks, servers, laptops, desktops, and operating systems and software.

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *

sixty two − = fifty four