Powershell: Module "SqlServer" on PowerShell Core 6.1 doesn't import all commands

[Hermholtz]

Steps to reproduce

Install-Module sqlserver # (as admin)
Import-Module sqlserver
Get-Command -module sqlserver invoke*

Expected behavior

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Cmdlet          Invoke-ASCmd                                       21.0.17279 sqlserver
Cmdlet          Invoke-PolicyEvaluation                            21.0.17279 sqlserver
Cmdlet          Invoke-ProcessASDatabase                           21.0.17279 sqlserver
Cmdlet          Invoke-ProcessCube                                 21.0.17279 sqlserver
Cmdlet          Invoke-ProcessDimension                            21.0.17279 sqlserver
Cmdlet          Invoke-ProcessPartition                            21.0.17279 sqlserver
Cmdlet          Invoke-ProcessTable                                21.0.17279 sqlserver
Cmdlet          Invoke-Sqlcmd                                      21.0.17279 sqlserver
Cmdlet          Invoke-SqlColumnMasterKeyRotation                  21.0.17279 sqlserver
Cmdlet          Invoke-SqlVulnerabilityAssessmentScan              21.0.17279 sqlserver

Actual behavior

(nothing printed)

Environment data

> $PSVersionTable
Name                           Value
----                           -----
PSVersion                      6.1.0
PSEdition                      Core
GitCommitId                    6.1.0
OS                             Microsoft Windows 10.0.17134
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

There are 42 missing commands. PowerShell for Windows shows 105 entries, PowerShell Core 6.1 only 63. Not sure what's the culprit here. The full list of missing commands is as follows. Note: the version 21.0.17279 is the latest at the moment.

Cmdlet          Add-RoleMember                                     21.0.17279 sqlserver
Cmdlet          Add-SqlAzureAuthenticationContext                  21.0.17279 sqlserver
Cmdlet          Add-SqlFirewallRule                                21.0.17279 sqlserver
Cmdlet          Backup-ASDatabase                                  21.0.17279 sqlserver
Cmdlet          Complete-SqlColumnMasterKeyRotation                21.0.17279 sqlserver
Cmdlet          Disable-SqlAlwaysOn                                21.0.17279 sqlserver
Cmdlet          Enable-SqlAlwaysOn                                 21.0.17279 sqlserver
Cmdlet          Export-SqlVulnerabilityAssessmentBaselineSet       21.0.17279 sqlserver
Cmdlet          Export-SqlVulnerabilityAssessmentScan              21.0.17279 sqlserver
Cmdlet          Import-SqlVulnerabilityAssessmentBaselineSet       21.0.17279 sqlserver
Cmdlet          Invoke-ASCmd                                       21.0.17279 sqlserver
Cmdlet          Invoke-PolicyEvaluation                            21.0.17279 sqlserver
Cmdlet          Invoke-ProcessASDatabase                           21.0.17279 sqlserver
Cmdlet          Invoke-ProcessCube                                 21.0.17279 sqlserver
Cmdlet          Invoke-ProcessDimension                            21.0.17279 sqlserver
Cmdlet          Invoke-ProcessPartition                            21.0.17279 sqlserver
Cmdlet          Invoke-ProcessTable                                21.0.17279 sqlserver
Cmdlet          Invoke-Sqlcmd                                      21.0.17279 sqlserver
Cmdlet          Invoke-SqlColumnMasterKeyRotation                  21.0.17279 sqlserver
Cmdlet          Invoke-SqlVulnerabilityAssessmentScan              21.0.17279 sqlserver
Cmdlet          Merge-Partition                                    21.0.17279 sqlserver
Cmdlet          New-RestoreFolder                                  21.0.17279 sqlserver
Cmdlet          New-RestoreLocation                                21.0.17279 sqlserver
Cmdlet          New-SqlColumnEncryptionKey                         21.0.17279 sqlserver
Cmdlet          New-SqlColumnEncryptionKeyEncryptedValue           21.0.17279 sqlserver
Cmdlet          New-SqlColumnEncryptionSettings                    21.0.17279 sqlserver
Cmdlet          New-SqlVulnerabilityAssessmentBaseline             21.0.17279 sqlserver
Cmdlet          New-SqlVulnerabilityAssessmentBaselineSet          21.0.17279 sqlserver
Cmdlet          Remove-RoleMember                                  21.0.17279 sqlserver
Cmdlet          Remove-SqlFirewallRule                             21.0.17279 sqlserver
Cmdlet          Restore-ASDatabase                                 21.0.17279 sqlserver
Cmdlet          Restore-SqlDatabase                                21.0.17279 sqlserver
Cmdlet          Save-SqlMigrationReport                            21.0.17279 sqlserver
Cmdlet          Set-SqlAuthenticationMode                          21.0.17279 sqlserver
Cmdlet          Set-SqlColumnEncryption                            21.0.17279 sqlserver
Cmdlet          Set-SqlNetworkConfiguration                        21.0.17279 sqlserver
Cmdlet          Start-SqlInstance                                  21.0.17279 sqlserver
Cmdlet          Stop-SqlInstance                                   21.0.17279 sqlserver
Cmdlet          Test-SqlAvailabilityGroup                          21.0.17279 sqlserver
Cmdlet          Test-SqlAvailabilityReplica                        21.0.17279 sqlserver
Cmdlet          Test-SqlDatabaseReplicaState                       21.0.17279 sqlserver
Cmdlet          Test-SqlSmartAdmin                                 21.0.17279 sqlserver

[vexx32]

See #7759 for a similar issue.

Is this module also using a wildcard in their *ToExport lines in the module manifest? If so, that's something the module authors need to fix. It's explicitly stated in the manifest template that wildcarded export statements are not properly supported and should be avoided.

[Hermholtz]

@vexx32 no, I didn't find any wildcard, but I found other interesting thing in SqlServer.psd1 file - see below. So we clearly have something wrong in manifest file, but still - PowerShell for Windows manages to get around it somehow, while PowerShell Core does not... And the list of missing cmdlets doesn't match the part of the list below the problematic place - they're also from above this place.

As the SqlServer module is not open source, we need to contact the authors/maintainers in Microsoft, and I don't know where to start...

[vexx32]

I don't have that module installed at the moment, but I'd be willing to bet that whole block of function names is surrounded with @(), rendering the need for a comma irrelevant when there's a newline.

So I don't think it'd be that causing the issue, in general. Hmm. Thanks for checking, though!!

[Hermholtz]

@vexx32 you're right:

# Cmdlets to export from this module
CmdletsToExport = @(
    'Add-RoleMember',
....
    'Test-SqlSmartAdmin',
    'Write-SqlTableData' )

[SteveL-MSFT]

I took a look at the module and at import time, the script module dynamically imports the actual binary module depending on whether you're in Windows PowerShell or PowerShell Core. I've reached out the owners who published the module to see if this is expected in that some cmdlets may not be compatible with PSCore6.

[SteveL-MSFT]

The answer from the module owners is that this is expected as the porting work is not complete. Work will continue towards parity, but what you get today is what you get.

[Hermholtz]

Thanks for the information. Maybe a warning could be printed during installation on PowerShell Core then.

[SteveL-MSFT]

I would suggesting providing that feedback to the module owners using: https://www.powershellgallery.com/packages/SqlServer/21.0.17279/ContactOwners

[Matteo-T]

FWIW, I'm the module owner. And I can look into adding the warning (and possibly have the documentation updated). Sorry for all the trouble.

[MaximoTrinidad]

@Matteo-T

Thanks for the update!!

Question: Does it matter if I use the previous version? I use this module on Core for my demos.
:)

[Matteo-T]

No, it does matter. We have only recently started looking at the PS6 stuff and not much has changed across versions, really.

[MaximoTrinidad]

@Matteo-T

Thanks again!! Looking forward for more updates.
:)

[Matteo-T]

The "big" one we are tackling nos is to bring back Invoke-SqlCmd, at least in some usable form. There are non-trivial challenges though. Stay tuned.

[MaximoTrinidad]

I love the fact that I can create a SQLServer Drive on Linux and use the install SMO for building SQL SMO scripts. I love to play with it.

Thanks for working on getting it to Core.
:)

[Hermholtz]

@Matteo-T thank you! And while you'll be updating the manifest file, please fix that missing comma there (ugh, it's not semicolon as I wrote in the screenshot...) :-) It may not be required, but this time added to overall confusion :)))

[Matteo-T]

Yeah - the comma has been fixed already on my machine (as part of another change).

[johndowns]

How far away are we from a resolution to this? It appears that there are still lots of cmdlets that aren't imported when the SqlServer module is loaded into PowerShell Core.

[Matteo-T]

@johndowns : parity with "Full .NET Framework" is on-going and slow. In some case, it's just not feasible because the dependent libraries are just... not there. The roadmap is to try and add as much as we can as we go. I would like to have in the next semester the Always Encrypted cmdlets available for example. Are there any cmdlets that you are missing? Knowing what users out there are missing the most would be useful to prioritize the list...

[johndowns]

@Matteo-T Thanks for the quick response! I'm currently battling with the Invoke-ProcessASDatabase cmdlet and trying to get something working there. As far as I can tell it's a fairly thin wrapper around the REST API so it might be one of the low hanging fruit to pick off? :)

[MaximoTrinidad]

@Matteo-T

I'm a big fan of SMO.

Thanks for all the hard work!

[christianwade]

We are working on .NET Core versions of the AS client libs. Won't work initially with SSAS on prem. We have not planned the work for PowerShell integration. So currently we don't have a timeline for the end-to-end solution.

[jayrgee]

Are there any cmdlets that you are missing?

@Matteo-T : Is this thing still on? How about Test-SqlAvailabilityGroup?

[Matteo-T]

Are there any cmdlets that you are missing?

@Matteo-T : Is this thing still on? How about Test-SqlAvailabilityGroup?

Yes, very slow due to priorities and funding... I did not have time to migrate other cmdlets over to CoreCLR. That one should not be too hard to port over...

[ggolub]

@Matteo-T Sorry, "late to the party" on this. I, too, need to use "Invoke-ProcessASDatabase" using powershell running on Linux (under Docker). Is there any ETA to getting that implemented? Is there a base Microsoft Docker container that I could install powershell on where that CmdLet would be available?

[dmeadkagool]

Are there any cmdlets that you are missing? Knowing what users out there are missing the most would be useful to prioritize the list...

@Matteo-T is there any info on an ETA for implementing the Invoke-ASCmd cmdlet?

[Matteo-T]

@dmeadkagool - please refer to @christianwade's comment above. That's the most recent info I have as well.

[glennsarti]

@Matteo-T Just hit this as well with Add-SqlAzureAuthenticationContext

Disappointed to see even after all this time there's no docs on this at https://docs.microsoft.com/en-us/powershell/module/sqlserver/?view=sqlserver-ps or https://docs.microsoft.com/en-us/powershell/module/sqlserver/add-sqlazureauthenticationcontext?view=sqlserver-ps

SqlServer

Add-SqlAzureAuthenticationContext (sqlserver)

The Add-SqlAzureAuthenticationContext cmdlet authenticates the specified principal account to Azure Resource Manager. This cmdlet is used in conjunction with some other SQL Windows PowerShell cmdlets that interact with Azure resources, such as Key Vault. This cmdlet needs to be called to perform authentication, before any other cmdlet can interact with an Azure resource.

[yuanman2016]

@Matteo-T , I tried to import the module to Function App, and I want to see Invoke-ASCmd, in Kudu, I could see the command and can run queries with Azure cube. However, when I use it in Function App Code + Test mode in Azure Portal, the function is not imported. The PowerShell version I chose is 6.3 . Could you suggest if I miss anything here? Thanks for your help!

[MaximoTrinidad]

Seems to me, as Kudu runs in a Windows OS, when you use Windows PowerShell only have the Az modules installed. Unfortunately, the SqlServer module can’t be installed in Kudu manually (as you need admin privileges).

You only find the SqlServer module in Azure Shell and not in Kudu.

Maybe it’s a good idea to submit a request to have it included in Kudu.

😊

Maximo Trinidad
Technology Evangelist
[cid:[email protected]]

Microsoft MVP - Cloud and Datacenter Management (PowerShell)

[yuanman2016]

@MaximoTrinidad , thanks for following up and suggestions! I found a workaround to change runtime to 1.x and can import SqlServer modue with invoke-ASCmd which PowerShell version is 5.x that is what I needed :)

[asackmann]

@MaximoTrinidad , thanks for following up and suggestions! I found a workaround to change runtime to 1.x and can import SqlServer modue with invoke-ASCmd which PowerShell version is 5.x that is what I needed :)

@yuanman2016 can you explain how did you manage to change runtime to 1.x and then import SqlServer module?
I'm facing same problem! 😒

[yuanman2016]

@asackmann , I assume you already read this article: https://rasper87.wordpress.com/2019/01/25/import-powershell-module-package-into-azure-function/ which tells you how to add modules to a function

To configure powershell version for a function app, you can go to configuration of your function app-> function runtime settings->you can see runtime version there, and choose 1 which will change your powershell core version to 5.x

RaspeR87's Blog

Import PowerShell Module / Package into your Azure Function App

If you have a concern about how you could use external PowerShell Modules or Packages inside of you Azure Function App written in PowerShell language then you could read this blog post where I want…

[dmeadkagool]

The workaround to change runtime to 1.x and import SqlServer module using the method you linked to @yuanman2016 worked for using the Invoke-ASCmd cmdlet - thanks!

However, it doesn't seem to work for Invoke-SqlCmd (get a 'Could not load file or assembly 'Microsoft.SqlServer.BatchParser...' error)

Not sure if anyone else has come into this issue and found a solution?

[yuanman2016]

@dmeadkagool, I finally go latest version 3 and use ado client library for MDX query like this:

add-type -path .\Microsoft.AnalysisServices.AdomdClient.dll
add-type -path .\Microsoft.Identity.Client.dll

$conn = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection

$conn.ConnectionString = ""

$conn.open()

$cmd = $conn.CreateCommand()
$cmd.CommandText =
$adp =New-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter
$adp.SelectCommand = $cmd
$result = New-Object system.data.dataset
$adp.Fill($result)

Note: you will need to download latest ado client library and it is dependency dll, then upload to your function app folder and call like above : https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64

I don't check invoke-sqlcmd, but as it can be imported, it should work

Microsoft.AnalysisServices.AdomdClient.NetCore.retail.amd64 19.10.0-Preview

ADOMD.NET is a Microsoft .NET Core data provider to communicate with Azure Analysis Services and Microsoft SQL Server Analysis Services.