Connect-SQL : Failed to connect to SQL instance

[SalutAToi]

Problem description

When running a SQL configuration (in my case, the SQLLogin DSC resource), I'm met the below error message saying it failed to connect.

Note I was initially using Ansible with ansible.windows.win_dsc, but the below error is returned from recreating the configuration with DSC locally on the computer where I was running the ansible configuration. It still fails for the same reason. The server name is a FQDN with a record in Microsoft AD.

While that error happened using SQLLogin, I've traced it to the Connect-SQL function https://github.com/dsccommunity/SqlServerDsc/blob/23d76847a1c99324eae6fe4a0faf8a7ac24e41fb/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1#L569 so I imagine the issue could be due to the upstream lib Microsoft.SqlServer.Management.Smo.Server, but I'm not sure. It does not even attempt to open a connection

I've tried specifying dbatools as SMODefaultModuleName, no success (with or without). Here' s the configuration :

$password = 'redacted' | ConvertTo-SecureString -asPlainText -Force
$username = "ad\admin"
[PSCredential] $credential = New-Object System.Management.Automation.PSCredential($username,$password)

Configuration test
{

    Import-DscResource -ModuleName 'SqlServerDsc'
    node localhost
    {
        SqlLogin 'testlogin'
        {
            Ensure               = 'Present'
            Name                 = 'ad\SQLLOGIN'
            LoginType            = 'WindowsGroup'
            ServerName           = 'server'
            InstanceName         = 'instance'
                PsDscRunAsCredential = $SqlAdministratorCredential

        }
    }
}
$cd = @{
    AllNodes = @(
        @{
            NodeName = 'localhost'
            PSDscAllowPlainTextPassword = $true
                PsDscAllowDomainUser = $true
SMODefaultModuleName = 'dbatools'
        }
    )
}
test -ConfigurationData $cd

Verbose logs

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer BL-WEB-1 with user sid S-1-5-21-1483537166-2846010545-766114601-1115.
VERBOSE: [BL-WEB-1]: LCM:  [ Start  Set      ]
VERBOSE: [BL-WEB-1]: LCM:  [ Start  Resource ]  [[SqlLogin]testlogin]
VERBOSE: [BL-WEB-1]: LCM:  [ Start  Test     ]  [[SqlLogin]testlogin]
VERBOSE: [BL-WEB-1]:                            [[SqlLogin]testlogin] Determines if the login 'ad\user' at the instance 'server\instance' has the correct state.
VERBOSE: [BL-WEB-1]:                            [[SqlLogin]testlogin] Getting the login 'ad\user' from the instance 'server\instance'.
VERBOSE: [BL-WEB-1]:                            [[SqlLogin]testlogin] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [BL-WEB-1]: LCM:  [ End    Test     ]  [[SqlLogin]testlogin]  in 1.9140 seconds.
PowerShell DSC resource DSC_SqlLogin  failed to execute Test-TargetResource functionality with error message: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: Failed to connect to SQL instance
'server\instance'. (SQLCOMMON0019)
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost

VERBOSE: [BL-WEB-1]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : localhost

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 3.447 seconds

How to reproduce

Context is a windows VM running in GCP Compute Engine (google provided image) joined to a managed Microsoft AD, connecting to a Cloud SQL (MSSQL) instance on the same VPC (all network requirements verified, SSMS works fine on the same computer)

Expected behavior

Successful connection to the database instance

Current behavior

Fails to connect.

Does not even attempt a DNS request to resolve the server FQDN, even though it is properly returned by the verbose output. Does not initiate a TCP connection with the server (verified with wireshark during the request)

SSMS works fine from the same computer, same creds

Suggested solution

Nope

Operating system the target node is running

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 and build the target node is running

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

Module version used

Name                  Version
----                  -------
ActiveDirectoryDsc    6.5.0
ComputerManagementDsc 9.1.0
dbatools              2.1.22
dbatools.library      2024.4.12
NetworkingDsc         9.0.0
PackageManagement     1.4.8.1
PowerShellGet         2.2.5
SqlServer             22.3.0
SqlServerDsc          16.6.0