Following a Windows upgrade from 2012R2 to 2019, we are having issues with SQLPS and any SQL Agent PowerShell job step. Running SQLPS returns:
import-module : File E:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot
be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at
At line:1 char:1
+ import-module SQLPS
+ CategoryInfo : SecurityError: (:) [Import-Module], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand
This appears to be related to the Process ExecutionPolicy set to "Restricted" in SQLPS. Note that PowerShell returns:
whereas SQLPS returns:
I've been pointed to the registry hive (HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps130) but this was missing after the Windows upgrade. Having created it, the problem was not resolved. I'm guessing that there are other registry entries that need to be recreated after the upgrade but I can't find any reference.
I've also tried:
Install-Module SqlServer -Force -AllowClobber
but that hasn't resolved the problem.
Is there a way to reinstall/repair the SQLPS installation - not sure how it gets installed in SQL Server 2016 - which might repair the missing registry entries (if any), etc.
BTW - I've noted the feedback I've had that SQLPS and SQL Agent PowerShell steps should be replaced by proper PowerShell scripts but that's not what I currently need to fix right now.