SqlProtocolTcpIp: SQL2019 not supported via 'SqlServer' module (and old 'Microsoft.SqlServer.SqlWmiManagement' libary/version?)

[SphenicPaul]

Details of the scenario you tried and the problem that is occurring

Background

I've been attempting to install SQL Server 2019 (not using any Cumulative Updates, yet) using the SqlServerDsc module's resources. The installation has been performed correctly (making use of the SQLSetup resource), up until I attempt to set the TCP port number for the SQL Instance using the combination of related, resources within the SqlServerDsc PSModule - These are SqlProtocol and SqlProtocolTcpIp (example usage below).

I have kept hitting the scenario where the ports are not set to what I was/am expecting. The SQL instances installed seem to be using dynamic ports (I'm intending to set them as fixed for IP1, IP2, IP3 and IPAll).

The short answer to all of this might be to add an additional check/guard clause in Get-ServerProtocolObject to ensure there is a ServerInstance obtained (rather than proceeding silently without one), but I was putting this together as investigating in case anyone else hit this problem/issue.

Investigations

I believe I've traced this problem back to the Get-ServerProtocolObject function within the included/embedded SqlServerDsc.Common PSModule (within the SqlServerDsc PSModule).

As part of this function, it performs...

$serverInstance = $managedComputerObject.ServerInstances[$InstanceName]

... but the ServerInstances array property on the $managedComputerObject object appears to be empty, even after a successful installation of SQL2019. where:

• I can also connect to (if I find the correct port) and;
• I can see the SQL Server services listed via use of Get-Service

I'd also noticed that the other properties on the $managedComputerObject object were empty arrays (i.e. Services and ClientProtocols). This got me looking in the direction of the versions of binaries used.

Findings

So, seeming, the Get-ServerProtocolObject function in the SqlServerDsc.Common PSModule (embedded in SqlServerDsc module) is making use of the [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer](https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.wmi.managedcomputer?view=sqlserver-2016&viewFallbackFrom=sqlserver-2019) type/class. This appears to be being obtained from the SqlServer` PSModule.

Having reviewed various sources of Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer, I've determined:

• SqlServer PSModule v21.0.17279 ...(I was using this)... is using Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer v14.0.17279.0
• SqlServer PSModule v21.1.18229 ...(latest version)... is using Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer v15.0.18229.0
• Microsoft.SqlServer.SqlManagementObjects NuGet package v161.44091.28 is using Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer v16.200.44091.28

Note that the version of the SqlServer PSModule I was using also didn't seem to work with SQL2017 (as I'd perhaps expect given that it was using a v14.0 version of Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer)? ... not looked too much further that that though.

... so, as a result of all that, my conclusion is that:

• the SqlServer module I was using doesn't yet support SQL2019 through use of Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer (or there is a problem/issue with it, and.or how it's used, and/or one or more referenced libraries it uses) .... sorry, not too narrowly scoped.
• the Get-ServerProtocolObject function within the included/embedded SqlServerDsc.Common PSModule (within the SqlServerDsc PSModule) is continuing to execute, without raising an error/exception, even if it cannot obtain a ServerInstance and cannot assign it to the $serverInstance within that function .... which then effectively makes the rest of the function perform nothing (and leaves the user of the DSC (i.e. myself in this case :) ) resource puzzled as to why the ports haven't been updated.

Verbose logs showing the problem

N/A

Suggested solution to the issue

Seemingly, updating the installed Sqlserver module to v21.1.18229 does seem to resolve this (in terms of getting my test script working) although doesn't handle the "silent error" within Get-ServerProtocolObject.

I'm going to try a complete rebuild and reinstall of SQL 2019 using the newer module over the next day or two (depending on priorities). I'll reply to this post/issue with any results and whether that resolved the installation.

The DSC configuration that is used to reproduce the issue (as detailed as possible)

    # ....SNIP.... 

    SqlProtocol 'SqlProtocol_SharedMemory' {
        InstanceName           = $SQLInstanceName
        ProtocolName           = 'SharedMemory'

        Enabled                = $false # Disabled
        SuppressRestart        = $false
        RestartTimeout         = $SqlProtocolRestartTimeout

        DependsOn              = "[SQLSetup]DefaultSQLInstance"
    }

    SqlProtocol 'SqlProtocol_NamedPipes' {
        InstanceName           = $SQLInstanceName
        ProtocolName           = 'NamedPipes'

        Enabled                = $false # Disabled
        PipeName               = 'NotUsed'
        SuppressRestart        = $false
        RestartTimeout         = $SqlProtocolRestartTimeout

        DependsOn              = "[SQLSetup]DefaultSQLInstance"
    }

    SqlProtocol 'SqlProtocol_TcpIp' {
        InstanceName           = $SQLInstanceName
        ProtocolName           = 'TcpIp'

        Enabled                = $true
        ListenOnAllIpAddresses = $true
        KeepAlive              = 20000
        SuppressRestart        = $false
        RestartTimeout         = $SqlProtocolRestartTimeout

        DependsOn              = "[SQLSetup]DefaultSQLInstance"
    }

    SqlProtocolTcpIp 'SqlProtocolTcpIp_IP1' {
        InstanceName           = $SQLInstanceName

        Enabled                = $true
        IpAddressGroup         = 'IP1'
        TcpPort                = $SQLTCPPort

        DependsOn              = "[SqlProtocol]SqlProtocol_TcpIp"
    }

    SqlProtocolTcpIp 'SqlProtocolTcpIp_IP2' {
        InstanceName           = $SQLInstanceName

        Enabled                = $true
        IpAddressGroup         = 'IP2'
        TcpPort                = $SQLTCPPort

        DependsOn              = "[SqlProtocolTcpIp]SqlProtocolTcpIp_IP1"
    }

    SqlProtocolTcpIp 'SqlProtocolTcpIp_IP3' {
        InstanceName           = $SQLInstanceName

        Enabled                = $true
        IpAddressGroup         = 'IP3'
        TcpPort                = $SQLTCPPort

        DependsOn              = "[SqlProtocolTcpIp]SqlProtocolTcpIp_IP2"
    }

    SqlProtocolTcpIp 'SqlProtocolTcpIp_IP4' {
        InstanceName           = $SQLInstanceName

        Enabled                = $true
        IpAddressGroup         = 'IP3'
        TcpPort                = $SQLTCPPort

        DependsOn              = "[SqlProtocolTcpIp]SqlProtocolTcpIp_IP3"
    }

    SqlProtocolTcpIp 'SqlProtocolTcpIp_IPAll' {
        InstanceName           = $SQLInstanceName

        Enabled                = $false
        IpAddressGroup         = 'IPAll'
        UseTcpDynamicPort      = $false
        TcpPort                = $SQLTCPPort

        DependsOn              = "[SqlProtocolTcpIp]SqlProtocolTcpIp_IP4"
    }


    # ....SNIP.... 

...and in addition to the above, I'd made a cut-down script (including tiny edits to functions to remove need for exception functions etc.) to test the scenario...

cls

$VerbosePreference = 'Continue'



function New-InvalidOperationException
{
    param($Message)

    throw $Message
}

<#
    .SYNOPSIS
        Get static name properties of he specified protocol.
    .PARAMETER ProtocolName
        Specifies the name of network protocol to return name properties for.
        Possible values are 'TcpIp', 'NamedPipes', or 'ShareMemory'.
    .NOTES
        The static values returned matches the values returned by the class
        ServerProtocol. The property DisplayName could potentially be localized
        while the property Name must be exactly like it is returned by the
        class ServerProtocol, with the correct casing.
#>
function Get-ProtocolNameProperties
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateSet('TcpIp', 'NamedPipes', 'SharedMemory')]
        [System.String]
        $ProtocolName
    )

    $protocolNameProperties = @{ }

    switch ($ProtocolName)
    {
        'TcpIp'
        {
            $protocolNameProperties.DisplayName = 'TCP/IP'
            $protocolNameProperties.Name = 'Tcp'
        }

        'NamedPipes'
        {
            $protocolNameProperties.DisplayName = 'Named Pipes'
            $protocolNameProperties.Name = 'Np'
        }

        'SharedMemory'
        {
            $protocolNameProperties.DisplayName = 'Shared Memory'
            $protocolNameProperties.Name = 'Sm'
        }
    }

    return $protocolNameProperties
}

<#
    .SYNOPSIS
        Returns the ServerProtocol object for the specified SQL Server instance
        and protocol name.
    .PARAMETER InstanceName
        Specifies the name of the SQL Server instance to connect to.
    .PARAMETER ProtocolName
        Specifies the name of network protocol to be configured. Possible values
        are 'TcpIp', 'NamedPipes', or 'ShareMemory'.
    .PARAMETER ServerName
        Specifies the host name of the SQL Server to connect to.
    .NOTES
        The class Microsoft.SqlServer.Management.Smo.Wmi.ServerProtocol is
        returned by this function.
#>
function Get-ServerProtocolObject
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $true)]
        [System.String]
        $InstanceName,

        [Parameter(Mandatory = $true)]
        [ValidateSet('TcpIp', 'NamedPipes', 'SharedMemory')]
        [System.String]
        $ProtocolName,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $ServerName
    )

    $serverProtocolProperties = $null

    # =======================================================
    # ORIGINAL
    # =======================================================
    $newObjectParameters = @{
        TypeName     = 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer'
        ArgumentList = @($ServerName)
    }
    
    $managedComputerObject = New-Object @newObjectParameters
    
    $serverInstance = $managedComputerObject.ServerInstances[$InstanceName]

    if ($serverInstance)
    {
        $protocolNameProperties = Get-ProtocolNameProperties -ProtocolName $ProtocolName

        $serverProtocolProperties = $serverInstance.ServerProtocols[$protocolNameProperties.Name]
    }

    return $serverProtocolProperties
}

<#
    .SYNOPSIS
        Imports the module SQLPS in a standardized way.
    .PARAMETER Force
        Forces the removal of the previous SQL module, to load the same or newer
        version fresh. This is meant to make sure the newest version is used, with
        the latest assemblies.
#>
function Import-SQLPSModule
{
    [CmdletBinding()]
    param
    (
        [Parameter()]
        [System.Management.Automation.SwitchParameter]
        $Force
    )

    if ($Force.IsPresent)
    {
        Write-Verbose -Message $script:localizedData.ModuleForceRemoval -Verbose
        Remove-Module -Name @('SqlServer', 'SQLPS', 'SQLASCmdlets') -Force -ErrorAction SilentlyContinue
    }

    <#
        Check if either of the modules are already loaded into the session.
        Prefer to use the first one (in order found).
        NOTE: There should actually only be either SqlServer or SQLPS loaded,
        otherwise there can be problems with wrong assemblies being loaded.
    #>
    $loadedModuleName = (Get-Module -Name @('SqlServer', 'SQLPS') | Select-Object -First 1).Name
    if ($loadedModuleName)
    {
        Write-Verbose -Message $('PowerShellModuleAlreadyImported : ' + $loadedModuleName) -Verbose
        return
    }

    $availableModuleName = $null

    # Get the newest SqlServer module if more than one exist
    $availableModule = Get-Module -FullyQualifiedName 'SqlServer' -ListAvailable |
        Sort-Object -Property 'Version' -Descending |
        Select-Object -First 1 -Property Name, Path, Version

    if ($availableModule)
    {
        $availableModuleName = $availableModule.Name
        Write-Verbose -Message $('PreferredModuleFound') -Verbose
    }
    else
    {
        Write-Verbose -Message $('PreferredModuleNotFound') -Verbose

        <#
            After installing SQL Server the current PowerShell session doesn't know about the new path
            that was added for the SQLPS module.
            This reloads PowerShell session environment variable PSModulePath to make sure it contains
            all paths.
        #>
        Set-PSModulePath -Path ([System.Environment]::GetEnvironmentVariable('PSModulePath', 'Machine'))

        <#
            Get the newest SQLPS module if more than one exist.
        #>
        $availableModule = Get-Module -FullyQualifiedName 'SQLPS' -ListAvailable |
            Select-Object -Property Name, Path, @{
                Name       = 'Version'
                Expression = {
                    # Parse the build version number '120', '130' from the Path.
                    (Select-String -InputObject $_.Path -Pattern '\\([0-9]{3})\\' -List).Matches.Groups[1].Value
                }
            } |
            Sort-Object -Property 'Version' -Descending |
            Select-Object -First 1

        if ($availableModule)
        {
            # This sets $availableModuleName to the Path of the module to be loaded.
            $availableModuleName = Split-Path -Path $availableModule.Path -Parent
        }
    }

    if ($availableModuleName)
    {
        try
        {
            Write-Debug -Message $('DebugMessagePushingLocation')
            Push-Location

            <#
                SQLPS has unapproved verbs, disable checking to ignore Warnings.
                Suppressing verbose so all cmdlet is not listed.
            #>
            $importedModule = Import-Module -Name $availableModuleName -DisableNameChecking -Verbose:$false -Force:$Force -PassThru -ErrorAction Stop

            <#
                SQLPS returns two entries, one with module type 'Script' and another with module type 'Manifest'.
                Only return the object with module type 'Manifest'.
                SqlServer only returns one object (of module type 'Script'), so no need to do anything for SqlServer module.
            #>
            if ($availableModuleName -ne 'SqlServer')
            {
                $importedModule = $importedModule | Where-Object -Property 'ModuleType' -EQ -Value 'Manifest'
            }

            Write-Verbose -Message $('ImportedPowerShellModule :' + $importedModule.Name + '; ' + $importedModule.Version + '; ' +  $importedModule.Path) -Verbose
        }
        catch
        {
            $errorMessage = 'FailedToImportPowerShellSqlModule : ' + $availableModuleName
            New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
        }
        finally
        {
            Write-Debug -Message $('DebugMessagePoppingLocation')
            Pop-Location
        }
    }
    else
    {
        $errorMessage = $script:localizedData.PowerShellSqlModuleNotFound
        New-InvalidOperationException -Message $errorMessage
    }
}

cls

Import-SQLPSModule

<#
    Must connect to the local machine name because $ServerName can point
    to a cluster instance or availability group listener.
#>
$getServerProtocolObjectParameters = @{
    ServerName   = $env:COMPUTERNAME
    Instance     = 'TESTSQL'
    ProtocolName = 'TcpIp'
}

'----------------'
Get-Module | Select Name, Version | FT -AutoSize
'----------------'

$serverProtocolProperties = Get-ServerProtocolObject @getServerProtocolObjectParameters

# Returns value, if works ... no value returned if NULL
$serverProtocolProperties

SQL Server edition and version the target node is running

SQL Server 2019 Enterprise

SQL Server PowerShell modules present on the target node

Further module version information above but....

SqlServer 21.1.18229 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18229\SqlServer.psd1
SqlServer 21.0.17279 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17279\SqlServer.psd1
SQLPS 15.0 C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture : 64-bit
WindowsBuildLabEx : 14393.3986.amd64fre.rs1_release.201002-1707
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

PSVersion 5.1.14393.3866
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.3866
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Version of the DSC module that was used

SqlServerDsc 14.2.1 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\14.2.1\SqlServerDsc.

[SphenicPaul]

Shout in the meantime if there are any questions.

[johlju]

Thank you for the detailed investigation into this! Appreciate it! If I understand the conclusion it sounds that the fix is fairly simple by adding the mentioned error handling, so it throws an error if the object returned is not an instance (e.g. $null). 🤔

[SphenicPaul]

Yes. It took a bit of tracking down but I didn’t want to waste the investigation without some documentation of it.

Incidentally, I’ve not had chance to retry with a later version of the ‘SQLServer’ module yet, but I believe the fix (suggested above) will at least save somebody else falling down this rabbit hole whilst investigating this issue.

[SphenicPaul]

I was finally able to get around to retrying the above functionality this week.

It does seem that upgrading the SQLServer module to v21.1.18229 (which is using Microsoft.SqlServer.Management.Smo.Wmi(.ManagedComputer) v15.0.18229.0) does then return the correct/expected result/output for the subsequent functionality (to install a SQL Server 2019 instance) to execute correctly.

I've submitted PR #1651 to resolve the outstanding issue (i.e. catch the scenario where there are no SQL instances found within Get-ServerProtocolObject, helper function (within SqlServer.Common, helper module).

Shout if you've any questions.