SQL Server 2016: SQLPS and SQL Agent PowerShell steps broken after Windows upgrade

Question

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
https:/go.microsoft.com/fwlink/?LinkID=135170.
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:

Scope          ExecutionPolicy
-----          ---------------
MachinePolicy  Undefined
UserPolicy     Undefined
Process        Undefined
CurrentUser    Undefined
LocalMachine   Bypass

whereas SQLPS returns:

Scope          ExecutionPolicy
-----          ---------------
MachinePolicy  Undefined
UserPolicy     Undefined
Process        Restricted
CurrentUser    Undefined
LocalMachine   Bypass

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.

Thanks.

Answer 1

May have found an answer - just after posting the question!

I hadn't opened SQL Server Configuration Manager since the upgrade but when I tried, I got the error:

Cannot connect to WMI provider

Googling that, I found the solution:

https://learn.microsoft.com/en-us/troubleshoot/sql/tools/error-message-when-you-open-configuration-manager

Having run:

mofcomp "sqlmgmproviderxpsp2up.mof"

both problems were resolved. Don't know if this was required in conjunction with the Install-Module or whether it was only this but as I have no other server to try the upgrade on until we do the PROD server, I can't be sure.