SQLPS (and SQL Agent PowerShell steps) don't work after Windows upgrade to 2019

  • We were running SQL2016 on Windows 2012R2 without issues. We've just had an OS upgrade to Windows 2019 and now we can't run SQLPS.exe or any SQL Agent PowerShell steps. SQLPS.exe 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

    Running "Get-ExecutionPolicy -List" in SQLPS returns:

    Process Restricted

    whereas on our other SQL servers it's:

    Process RemoteSigned

    This appears to be set in the registry at:

    HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps130

    on the server that's working, but it's completely missing on the upgraded server that no longer works.

    Is there a way to repair SQLPS, if that would help?

    Thanks.

  • I'd already found that and it doesn't work. If I run PowerShell, I get:

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

    which means "Bypass" is in effect. However in SQLPS, I get:

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

    and no way to change it - because "Process" doesn't apply to the whole machine. Changing it in my session won't fix SQL Agent.

  • look at this link then https://www.pythian.com/blog/powershell-error-sqlps-ps1-cannot-loaded-running-scripts-disabled-system

     

    but heed the advise on the bottom - do not use native SQL PowerShell and instead use a command file to run with the most recent PowerShell version - where you can then also set required command line options to avoid the security issue you have here.

  • Thanks for the response. That registry hive was missing from the server after the upgrade - maybe "HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds" got replaced by the upgrade?

    However we tried recreating it previously but that didn't solve the problem. I'm guessing there's an install/repair that needs to be done as other registry entries may also be missing?

    I basically need to know how the SQLPS subsystem is installed so maybe I can just run that again and see if it helps.

    I have tried "PS> Install-Module SqlServer -Force -AllowClobber" but it didn't solve the problem.

    I agree on running everything in a *real* PowerShell script but we have the same tasks running in SQL2016 and SQL2019 (on Windows 2012R2/2019/2022) without issue. SQLPS has been broken by the Windows Upgrade, so my priority is finding a way to fix it.

    Thanks again.

  • May have found an answer.

    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.

  • install-module won't work for this - sqlps is not the same as sqlserver modules. if this was SQL 2022 you could tell SQL Agent to use SQLSERVER module instead of SQLPS - but not on 2016 as you are.

     

    SQLPS is installed as part of the SQL Server install - a repair of it could potentially fix the registry entries - not sure though. As far as I am aware there isn't a "separate" install or command line you could use to install only the SQLPS module.

    but from what you said above maybe your problem with the upgrade was a twofold one - and both the wmi fix and the registry entries were required.

  • WMI provider errors could mean a lot of things.   One of them could be your SQL install got damaged some what.

    Try this solution from Microsoft:

    To fix a corrupt SQLPS module, you can try the following steps:

    Uninstall the SQL Server PowerShell module using the following command:

    Uninstall-Module -Name SQLPS -Force

    Reinstall the SQL Server PowerShell module using the following command:

    Install-Module -Name SqlServer

    This will install the latest version of the SQL Server PowerShell module.

    -----------------------------------------------------------------------------------------------------------------------------------

    You may need to upgrade or re-install your SQL server.   Always use the latest SQL Service pack when upgrading.

    You are running SQL server 2016 on windows server 2019.   It should be OK.

    DBASupport

  • We encountered this same issue with some servers we in place upgraded from Windows Server 2012R2 to 2019.  Our server was initially SQL 2014 that was upgraded to 2016, which is why I took two registry keys and ran the command in #2 twice.

    I did the following to resolve the issue:

    1. Exported the registry nodes from our production server and imported them to our dev and stg servers:

      1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120
      2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps130

    2. Ran the following commands from an elevated command prompt:

      1. mofcomp "C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof"
      2. mofcomp "C:\Program Files (x86)\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"

    3. Copied the SQL Server Configuration Manager start menu shortcut from our production server.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply