Infrastructure at your Service

Nathan Courtine

syspolicy_purge_history job and PowerShell ExecutionPolicy

Since SQL Server 2008, Microsoft has introduced a system job called “syspolicy_purge_history”. This job is installed and enabled by default, and it contains three steps scheduled at 02:00 AM:

syspolicy_purge_history

syspolicy_purge_history_steps

 

syspolicy_purge_history_schedules

What is this job? And what is it for?

Since SQL Server 2008, a new feature called Policy Based Management has been added. When your policies are run, the results are stored in the msdb. But without a purge mechanism, msdb will keep growing. So Microsoft introduced the famous system job named “syspolicy_purge_history” to clean the results older than the days defined in the “HistoryRetentionInDays” property of Policy Management.

 

Should I disable it?

Definitively not. This job is part the well-functioning of SQL Server.

 

Should I care about it?

We do recommend to monitor this job as all other dba jobs.

 

But last time, this system job failed after each automatic and/or manual executions…

syspolicy_purge_history_execution_failed

If you go in details, you notice the third steps failed at the following line: “set –executionpolicy RemoteSigned –scope process –Force”

syspolicy_purge_history_detailed

 

Apparently, the SQL Server engine was not able to modify the execution policy to “RemoteSigned”. Does the engine have enough permissions? Should I modify manually the execution policy as Administrator?

Let’s see the current “ExecutionPolicy” configuration for the SQL Server PowerShell. Open the console from SQL Server Management Studio:

sql_server_powershell

And you have a beautiful error to begin softly:

sql_server_powershell_error

 

PowerShell executed the same command as previously, but with the same success…

Hopefully we have more details: Windows PowerShell tried to change the “ExecutionPolicy” parameter, but a policy have overridden the change…

 

Let’s see the “ExecutionPolicy” depending on the different scopes:

sql_server_powershell_executionpolicy

The “ExecutionPolicy” at the “MachinePolicy” scope is set to “AllSigned” (which is more restrictive), and may override the configuration at the “Process” scope. By setting the “ExecutionPolicy” to “RemoteSigned” at the “MachinePolicy” scope, the problem may be resolved…

sql_server_powershell_modify_executionpolicy

 

…or not! We cannot change manually the setting, even if the console is opened as Administrator!

But as you can see, we have more details: we cannot change the “ExecutionPolicy” this way, but we must change it through Group Policy.

Let’s open Local Group Policy Editor, and let’s browse to “Local Computer Policy\Computer Configuration\Administrative Templates\Windows Components\Windows PowerShell”.

Group_Policy

 

But no policy is configured…

 

If I cannot change the “ExecutionPolicy” at the “MachinePolicy” scope through the SQL Server PowerShell console, I propose you to change it directly in the registry ;-)

sql_server_powershell_executionpolicy_registry

…the policy is correctly set to “RemoteSigned”, but this value is overridden from somewhere else… which is apparently not locally because I found no GPO configured… Someone must have set a GPO for the “ExecutionPolicy” on the Domain Controller…

 

And indeed, there is a GPO which affects all the servers of the organization:

GPO_AD

 

 

For this resolution, Microsoft proposes two workarounds in the KB2995870:

  • Create a New Organization Unit for this server…
  • Or simply disable this GPO…

 

To be honest, the domain administrator does not want to create a New Organization Unit for only one server. But he also does not want to disable this GPO for all the servers of the Organization.

 

Hopefully, it does not mean we are stuck ;-) Even if the Active Directory is able to redefine the Policy on all the servers in an Organization, the local computer can always have the last word!

I force the configuration of the “ExecutionPolicy” policy directly in the local registry where SQL Server is installed:

executionpolicy_local_policy

 

And the miracle happens:

sql_server_powershell_executionpolicy2

syspolicy_purge_history_execution_success

 

 

To conclude, I will simply and shortly say: Registry is my best friend ;-)

 

 

Leave a Reply


× six = 42

Nathan Courtine
Nathan Courtine

Consultant