SqlScriptQuery: Get-Module for SQLPS throws error

[RDiegoli01]

Problem description

When calling the attached DSC configuration, we see an error related to our SqlScriptQuery call.

It looks like the target VM is trying to import the SQLPS module (from SQL Server 2022), but the Get-Module is passing bad parameters (see verbose logs).

We caught this last week we were using SqlServerDsc 16.2 which was working fine. Then our script updated it to 16.3 (released last week) and we started seeing this error.

Verbose logs

Running the Get-Module cmdlet without ListAvailable parameter is not supported for module names that include a path. 
Name parameter has this element 'C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS' which 
resolves to a path. Update the Name parameter to not have path elements, and then try again. The PowerShell DSC 
resource '[SqlScriptQuery]SetName::[SetSqlServerName]SetECMSqlServerName' with SourceInfo 
'F:\Agents\Agent2\_work\11\s\ConfigureInfrastructure\SetSqlServerName.ps1::17::5::SqlScriptQuery' threw one or more 
non-terminating errors while running the Test-TargetResource functionality. These errors are logged to the ETW channel 
called Microsoft-Windows-DSC/Operational. Refer to this channel for more details. Running the Get-Module cmdlet 
without ListAvailable parameter is not supported for module names that include a path. Name parameter has this element 
'C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS' which resolves to a path. Update the 
Name parameter to not have path elements, and then try again. The PowerShell DSC resource 
'[SqlScriptQuery]SetName::[SetSqlServerName]SetECMSqlServerName' with SourceInfo 
'F:\Agents\Agent2\_work\11\s\ConfigureInfrastructure\SetSqlServerName.ps1::17::5::SqlScriptQuery' threw one or more 
non-terminating errors while running the Test-TargetResource functionality. These errors are logged to the ETW channel 
called Microsoft-Windows-DSC/Operational. Refer to this channel for more details. Unable to find type 
[Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException]. The PowerShell DSC resource 
'[SqlScriptQuery]SetName::[SetSqlServerName]SetECMSqlServerName' with SourceInfo 
'F:\Agents\Agent2\_work\11\s\ConfigureInfrastructure\SetSqlServerName.ps1::17::5::SqlScriptQuery' threw one or more 
non-terminating errors while running the Test-TargetResource functionality. These errors are logged to the ETW channel 
called Microsoft-Windows-DSC/Operational. Refer to this channel for more details. The SendConfigurationApply function 
did not succeed. The SendConfigurationApply function did not succeed. The SendConfigurationApply function did not 
succeed.

DSC configuration

Configuration SetSqlServerName {
    Import-DscResource -ModuleName SqlServerDsc
    Import-DscResource -ModuleName PSDscResources

    $correctServerName = Join-Path -Path $Node.NodeName -ChildPath $Node.SqlInstance

    $GetQuery   = "select null"
    $TestQuery  = "select null"
    $SetQuery   = '
EXEC sp_dropserver @@SERVERNAME
GO
EXEC sp_addserver ''' + $CorrectServerName + ''', ''local''
GO
'
    Write-Output 'SetQuery: ' $SetQuery
    SqlScriptQuery SetName
    {
        ServerName           = $Node.NodeName
        InstanceName         = $Node.SqlInstance
        GetQuery             = $GetQuery
        TestQuery            = $TestQuery
        SetQuery             = $SetQuery
        QueryTimeout         = 30
    }


    # Restart SQL for name changes to take effect
    Script RestartSql {
        SetScript =
        {
            $using:Node
            Get-Service -Name "MSSQL`$$($Node.SqlInstance)" | Restart-Service -Force
        }
        TestScript = { $false }
        GetScript = { @{ Result = $null } }
    }

}

Suggested solution

Tested following exactly what the error message suggested:

• Added -ListAvailable parameter to Get-Module and it worked
• Also tested without the path in -Name (i.e. Get-Module -Name SQLPS)

SQL Server edition and version

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)   Oct  8 2022 05:58:25   Copyright (C) 2022 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)

SQL Server PowerShell modules

Name                 Version Path
----                 ------- ----
Az.MySql             1.1.0   C:\Program Files\WindowsPowerShell\Modules\Az.MySql\1.1.0\Az.MySql.psd1
Az.PostgreSql        1.1.0   C:\Program Files\WindowsPowerShell\Modules\Az.PostgreSql\1.1.0\Az.PostgreSql.psd1
Az.Sql               4.6.0   C:\Program Files\WindowsPowerShell\Modules\Az.Sql\4.6.0\Az.Sql.psd1
Az.SqlVirtualMachine 1.1.1   C:\Program Files\WindowsPowerShell\Modules\Az.SqlVirtualMachine\1.1.1\Az.SqlVirtualMach...
SQLPS                16.0    C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

OsName               : Microsoft Windows Server 2022 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 2009
WindowsBuildLabEx    : 20348.1.amd64fre.fe_release.210507-1500
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value
----                           -----
PSVersion                      5.1.20348.1366
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.20348.1366
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version Path
----         ------- ----
SqlServerDsc 16.3.0  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.3.0\SqlServerDsc.psd1

[johlju]

Thanks for reporting this. Guess we need to add integration tests that runs with SQLPS as well. 🙂

Added -ListAvailable parameter to Get-Module and it worked

On what line did you add that to?

[RDiegoli01]

Hey @johlju what I meant in my suggestion is that I added in a very simple test simply trying to call

• Get-Module -Name "C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS" which gives the same error in the logs.
• While Get-Module -ListAvailable -Name "C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS" yields me the desired module.

I couldn't get the time to discover exactly where in SqlScriptQuery is the failing Get-Module call.

[johlju]

Ah, cool. I will figure it out. It was most likely one of the changed lines to support dbatools as a preferred module. I did not know Get-Module worked that way. I will get this fixed as soon as possible. This weekend if not quicker.

[johlju]

It this line that fails.

SqlServerDsc/source/Public/Import-SqlDscPreferredModule.ps1

Line 100 in a86039c

$loadedModuleName = (Get-Module -Name $availableModuleName | Select-Object -First 1).Name

It should check for Get-Module -Name 'SQLPS', so when it get the path it should just use the leaf part of the path.

So the line should be

$loadedModuleName = (Get-Module -Name (Split-Path -Path $availableModuleName -Leaf) | Select-Object -First 1).Name 

@RDiegoli01 could you possible confirm this by changing that line in the file SqlServerDsc.psm1 on a node that fails? The line is part of the command Import-SqlDscPreferredModule.

[RDiegoli01]

@johlju tested your suggestion on a stack I had. It worked perfectly fine!

[johlju]

Great. I sent in a PR with the change. Will merge and release as soon as the tests pass.