I reviewed my daily report and saw that some jobs failed on six instances overnight, the common factor being that they all used Powershell. None of them had failed recently or been changed recently, and the same code executed fine on many other instances in the same time frame. Here is the error message:
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.
As you’ll see, it’s an error message that gives you clues, though it seems more obvious in hindsight!
What changed? We started digging and soon correlated it to what seemed like a minor change, the ops team removing SSMS from six instances to align with the standard configuration used elsewhere . We changed the execution policy on the instance, confirmed it was changed, tried the job again. Failed. Restarted the agent, failed. Finally it dawned that it was SQLPS failing, which is almost but not quite the same as Powershell, especially when it comes to the location of the execution policy. It turns out the the policy for SQLPS is stored here:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps110\ExecutionPolicy=Unrestricted (or whatever policy you want)
We made the change and the job ran, no Agent restart needed.
It looks like the uninstall of the tools removes the key. Presumably putting the tools back would have fixed it but it would have been overkill.