2

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
1
  • You sir, are a life saver! I had to add the key for Microsoft.SqlServer.Management.PowerShell.sqlps110 as well as the strings for Path and ExecutionPolicy, then changed the group policy to Undefined. At that point, the script ran perfectly!
    – Elsimer
    Feb 23, 2016 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.