November 18, 2023 at 11:54 pm
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.
November 19, 2023 at 1:04 am
easy google - should always be first point of reference
November 19, 2023 at 1:26 am
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.
November 19, 2023 at 9:36 am
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.
November 19, 2023 at 10:34 am
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.
November 19, 2023 at 11:15 am
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:
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.
November 19, 2023 at 11:28 am
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.
November 27, 2023 at 12:05 am
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
January 2, 2024 at 9:01 pm
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:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply