3

I am trying to use the SqlServer PowerShell module, available on the PowerShell Gallery.

After installing, either to -Scope CurrentUser or in an admin session, doing Import-Module SqlServer only results in three commands being imported into the session:

PS C:\Users\nreiling> Get-Command -Module SqlServer

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Alias           Decode-SqlName                                     21.0.17262 sqlserver
Alias           Encode-SqlName                                     21.0.17262 sqlserver
Function        SQLSERVER:                                         21.0.17262 sqlserver

This is in spite of the fact that doing (Get-Module -Name SqlServer -ListAvailable)[0].ExportedCommands.Values lists out all of the commands that are supposed to be included:

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Cmdlet          Add-RoleMember                                     21.0.17262 SqlServer
Cmdlet          Add-SqlAvailabilityDatabase                        21.0.17262 SqlServer
Cmdlet          Add-SqlAvailabilityGroupListenerStaticIp           21.0.17262 SqlServer
Cmdlet          Add-SqlAzureAuthenticationContext                  21.0.17262 SqlServer
Cmdlet          Add-SqlColumnEncryptionKeyValue                    21.0.17262 SqlServer
Cmdlet          Add-SqlFirewallRule                                21.0.17262 SqlServer
Cmdlet          Add-SqlLogin                                       21.0.17262 SqlServer
Cmdlet          Backup-ASDatabase                                  21.0.17262 SqlServer
Cmdlet          Backup-SqlDatabase                                 21.0.17262 SqlServer
                                        ...
Cmdlet          Test-SqlSmartAdmin                                 21.0.17262 SqlServer
Cmdlet          Write-SqlTableData                                 21.0.17262 SqlServer
Function        SQLSERVER:                                         21.0.17262 SqlServer
Alias           Decode-SqlName                                     21.0.17262 SqlServer
Alias           Encode-SqlName                                     21.0.17262 SqlServer

I tried installing an older version of the SqlServer module, as well as tried a different user account on my machine. Lots of reboots, new PS sessions, etc. Running Import-Module in verbose/debug mode doesn't throw any errors, it just imports only the function and aliases; none of the Cmdlets. The problem factor seems to be this particular Windows system, which is Windows 10 Pro 1803.

It is also worth noting that I'm only trying to use this module because the sqlps module that comes installed with SQL Server Management Studio stopped working on this machine. I don't know why that was the case; my guess was relating to a Windows or SSMS update, but in order to get to the point here where at least the SqlServer module installs without errors and warnings about clobbering, I removed that module from my $PSModulePath. Something else that broke seemingly at the same time was that my WSMan TrustedHosts list was reset (to blank), causing issues with PowerShell remoting that I had solved previously (this machine is not domain-joined).

How in the heck do I troubleshoot this?

NReilingh
  • 5,737
  • 3
  • 27
  • 52
  • Are you merely using this Windows 10 machine as a client for client level access, etc? Or does this Windows 10 machine have critical DBs on it that's used as a server? It's interesting that the `sqlps` stopped working but hopefully this is just a client machine rather that being used as a critical server since it is a Windows 10 machine rather than Windows Server. This sounds like a doozy though but too bad you couldn't try to get the `sqlps` working instead. If you don't have much luck here, don't forget about https://dba.stackexchange.com/ if needed though. – Vomit IT - Chunky Mess Style Jun 03 '18 at 00:04
  • @PimpJuiceIT Right, I only have the management tools installed on this machine. So far this smells like a PowerShell problem and not a SQL Server problem, which is why I didn't post in DBA.SE. I think I would like to test installing/importing a PS module that also uses compiled Cmdlets instead of just functions and see if that works, but I'm not sure where to look for that. – NReilingh Jun 03 '18 at 00:08
  • What version of PowerShell are you running by the way? I assume 5.0 but might as well confirm I suppose. You can run ... `$PSVersionTable` – Vomit IT - Chunky Mess Style Jun 03 '18 at 00:11
  • 5.1.17134.48 — pretty sure that's the current stable version. – NReilingh Jun 03 '18 at 00:12
  • I just tested this and I'm not getting the same result as you. I did run PowerShell ISE as administrator, and then installed it via `Install-Module -Name SqlServer` but I've already had the NuGet package installed or whatever for other modules I've installed that way rather than downloading manually, etc. if that even matters any. It also seems that I did not have to `Import-Module SqlServer` next though and just went right to `Get-Command -Module SqlServer` and it displayed all of them. – Vomit IT - Chunky Mess Style Jun 03 '18 at 00:21
  • @NReilingh did you ever find a resolution to this? I'm running into a similar issue after a windows update (1803) with another binary Powershell module that no longer exports any CmdLet and have exhausted all options. I appreciate any tips. – ysalmi Aug 22 '18 at 14:46
  • @ysalmi No dice. My current solution has been to blow away the VM and build it again. Sounds like this needs a GitHub issue, though. – NReilingh Aug 22 '18 at 14:53
  • @NReilingh thanks for the update. Your post here and this one (https://forums.aws.amazon.com/thread.jspa?threadID=270534&tstart=0) are the only similar cases I have found online. I'm actually dealing with the same AWS Powershell module. It feels like a broken windows update, but really hard to track down the details. – ysalmi Aug 22 '18 at 15:08
  • @ysalmi I added a UserVoice issue that you can vote on: https://windowsserver.uservoice.com/forums/301869-powershell/suggestions/35256367-module-cmdlets-missing-after-import-module – NReilingh Sep 07 '18 at 02:01
  • @NReilingh I finally "resolved" this by doing a Windows 10 Repair reinstall. I narrowed it down to an issue with .Net 4.7.2 but in windows 10 I had no means to repair/reinstall it because it's a core part of the OS. And because this PC is quite old, originally a Windows 7 PC, it accumulated a lot of .NET versions and I got stuck in a permissions deadlock: trying to repair .NET 4.7.2 made windows try to uninstall .NET 4.6.1 which would then throw an error that .NET 4.5 is a core OS feature now, which can only be _enabled_ by going into the Windows Features. – ysalmi Sep 07 '18 at 09:10
  • oh yeah, I also noticed that I could at least import the AWS Powershell tools if I ran Powershell V2 using `powershell.exe -version 2` – ysalmi Sep 07 '18 at 09:20

1 Answers1

4

The source of the problem seems to be a broken installation of .Net 4.7.2 that occasionally happens with installing the 1803 Windows 10 Update.

The only way I've managed to resolve this is with a Windows 10 Repair Install. This reinstalls all the core OS files and configuration but does not remove your Apps or data. But you should still make sure backup any important documents before doing it.

Others have found success with:

You may want to try these steps before doing the Repair Install of Windows. They didn't work for my situation.

ysalmi
  • 156
  • 4
  • 1
    Amazing. The Repair Install worked just great — thanks so much for your efforts in figuring this out. – NReilingh Sep 08 '18 at 00:38