SqlServiceAccount: When module SqlServer is present, error "SQL Server WMI provider is not available on SQL" is thrown

[Yvand]

Problem description

I deploy the latest SQL Server 2022 image available on Azure:
MicrosoftSQLServer:sql2022-ws2022:sqldev-gen2:latest

If the PowerShell module SqlServer is present, DSC resource SqlServiceAccount config always fails during Test with this error:

Get the current service account for the service 'DatabaseEngine'.
Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
Connecting to WMI on 'SQL'.
The following exception occurred while trying to enumerate the collection: "SQL Server WMI provider is not available on SQL.".

I do not repro this error if PowerShell module SqlServer is not installed.

Same symptom was reported here: https://dba.stackexchange.com/questions/321296/cant-connect-to-wmi-provider-on-new-sql-2022-install
But no solution found.

Edit: I looked at the code: It fails here and error can be reproduced simply by executing this in PowerShell:

Import-Module -Name SqlServer
$localhostname = Get-Content env:computername
$managedComputer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer -ArgumentList $localhostname
$managedComputer.Services

Verbose logs

VERBOSE: [SQL]: LCM:  [ Start  Resource ]  [[SqlServiceAccount]SetSqlInstanceServiceAccount]
VERBOSE: [SQL]: LCM:  [ Start  Test     ]  [[SqlServiceAccount]SetSqlInstanceServiceAccount]
VERBOSE: [SQL]:                            [[SqlServiceAccount]SetSqlInstanceServiceAccount] Get the current service account for the service 'DatabaseEngine'.
VERBOSE: [SQL]:                            [[SqlServiceAccount]SetSqlInstanceServiceAccount] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [SQL]:                            [[SqlServiceAccount]SetSqlInstanceServiceAccount] Connecting to WMI on 'SQL'.
The following exception occurred while trying to enumerate the collection: "SQL Server WMI provider is not available on SQL.".
    + CategoryInfo          : NotSpecified: (:) [], CimException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator
    + PSComputerName        : localhost
 
VERBOSE: [SQL]: LCM:  [ End    Test     ]  [[SqlServiceAccount]SetSqlInstanceServiceAccount]  in 0.0780 seconds.
PowerShell DSC resource DSC_SqlServiceAccount  failed to execute Test-TargetResource functionality with error message: 
System.Exception: The DatabaseEngine service on SQL\MSSQLSERVER could not be found. 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost

DSC configuration

configuration ConfigureSQLVM
{
    param
    (
        [Parameter(Mandatory)] [System.Management.Automation.PSCredential]$SqlSvcCreds
    )

    Import-DscResource -ModuleName SqlServerDsc -ModuleVersion 16.0.0
    Import-DscResource -ModuleName SqlServer -ModuleVersion 21.1.18256

    [String] $ComputerName = Get-Content env:computername

    Node localhost
    {
        LocalConfigurationManager
        {
            ConfigurationMode = 'ApplyOnly'
            RebootNodeIfNeeded = $true
        }

        SqlServiceAccount SetSqlInstanceServiceAccount
        {
            ServerName     = $ComputerName
            InstanceName   = "MSSQLSERVER"
            ServiceType    = "DatabaseEngine"
            ServiceAccount = $SqlSvcCreds
            RestartService = $true
        }
    }
}

Suggested solution

(NOT WORKING)
I tried to execute mofcomp "C:\Program Files (x86)\Microsoft SQL Server\160\Shared\sqlmgmprovider.mof" but it does not change anything

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  Developer Edition (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)

SQL Server PowerShell modules

Name      Version    Path
----      -------    ----
SqlServer 21.1.18256 C:\Program Files\WindowsPowerShell\Modules\SqlServer\SqlServer.psd1
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.0.0  C:\Users\yvand\Documents\WindowsPowerShell\Modules\SqlServerDsc\16.0.0\SqlServerDsc.psd1

[johlju]

To fully work with SQL Server 2022 you need the latest SqlServer v22.0.49-preview, and the latest preview of SqlServerDsc.

[johlju]

This was tracked in this issue #1800.