Sql-server – unable to run step 3 of syspolicy_purge_history job on SQL Server 2012

powershellsql serversql-server-2012

After installing an instance of SQL Server 2012 (SP3) on a server that had previously had SQL Server 2008R2 on it, I began encountering the following error while attempting to run step 3 of syspolicy_purge_history:

A job step received an error at line 1 in a PowerShell script. The
corresponding line is 'import-module SQLPS -DisableNameChecking'.
Correct the script and reschedule the job. The error information
returned by PowerShell is: 'File C:\Program Files (x86)\Microsoft SQL
Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded
because the execution of scripts is disabled on this system. Please
see "get-help about_signing" for more details. File C:\Program Files
(x86)\Microsoft SQL
Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded
because the execution of scripts is disabled on this system. Please
see "get-help about_signing" for more details. '

I logged into the server as the SQL Server Agent account and ran the SQLPS executable directly as administrator. I saw that the Process scope was set to Restricted with all others Undefined. After changing group policy for the computer to RemoteSigned, the error changed to the following:

A job step received an error at line 1 in a PowerShell script. The
corresponding line is 'set-executionpolicy restricted -scope process
-Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Security error. '

This occurs even after modifying the job so that it is simply running Get-ExecutionPolicy -List. I've checked the registry tag and tried both deleting the registry key as well as changing it to Unrestricted and Bypass.

The only thing I haven't tried yet is attempting to upgrade the instances to 2014. It is a production box. Any suggestions other than disabling that step or running it using xp_cmdshell?

Best Answer

Please look at this connect item

The SQL PowerShell wrapper SQLPS.EXE seems to execute a PS command Set-ExecutionPolicy restricted -scope Process -Force during its initialization. Then PS engine should then be initialized with command import-module SQLPS -DisableNameChecking which is blocked because of the "Restricted" execution policy. This raises a critical error and the PowerShell engine terminates.

The workaround is adding/changing a registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110]
"ExecutionPolicy"="RemoteSigned"

See this and this for additional reference.

Related Question