SqlAGListener: Test-DscConfiguration fails with the SqlAGListener module when the Availability Group is not present

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

When the SqlAGListener SqlServerDsc DSC module is used in the DSC configuration and that the Availability Group is not yet created on the node, the "Test-DscConfiguration" command fails.

Verbose logs showing the problem

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = TestConfiguration,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'. VERBOSE: An LCM method call arrived from computer AUTHORING_STATION with user sid SID. VERBOSE: [COMPUTER_NAME]: LCM: [ Start Compare ] VERBOSE: [COMPUTER_NAME]: LCM: [ Start Resource ] [[SqlLogin]AddNTServiceClusSvc] VERBOSE: [COMPUTER_NAME]: LCM: [ Start Test ] [[SqlLogin]AddNTServiceClusSvc] VERBOSE: [COMPUTER_NAME]: [[SqlLogin]AddNTServiceClusSvc] Determines if the login 'NT SERVICE\ClusSvc' at the instance 'COMPUTER_NAME\INSTANCE_NAME' has the correct state. VERBOSE: [COMPUTER_NAME]: [[SqlLogin]AddNTServiceClusSvc] Getting the login 'NT SERVICE\ClusSvc' from the instance 'COMPUTER_NAME\INSTANCE_NAME'. VERBOSE: [COMPUTER_NAME]: [[SqlLogin]AddNTServiceClusSvc] Preferred module SqlServer found. (SQLCOMMON0023) VERBOSE: [COMPUTER_NAME]: [[SqlLogin]AddNTServiceClusSvc] Importing PowerShell module 'SqlServer' with version '21.0.17262' from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.0.17262\SqlServer.psm1'. (SQLCOMMON0025) VERBOSE: [COMPUTER_NAME]: [[SqlLogin]AddNTServiceClusSvc] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054) VERBOSE: [COMPUTER_NAME]: [[SqlLogin]AddNTServiceClusSvc] Connected to SQL instance 'COMPUTER_NAME\INSTANCE_NAME'. (SQLCOMMON0018) VERBOSE: [COMPUTER_NAME]: [[SqlLogin]AddNTServiceClusSvc] The login 'NT SERVICE\ClusSvc' is Present at the instance 'COMPUTER_NAME\INSTANCE_NAME'. VERBOSE: [COMPUTER_NAME]: LCM: [ End Test ] [[SqlLogin]AddNTServiceClusSvc] True in 1.8430 seconds. VERBOSE: [COMPUTER_NAME]: LCM: [ End Resource ] [[SqlLogin]AddNTServiceClusSvc] VERBOSE: [COMPUTER_NAME]: LCM: [ Start Resource ] [[SqlPermission]AddNTServiceClusSvcPermissions] VERBOSE: [COMPUTER_NAME]: LCM: [ Start Test ] [[SqlPermission]AddNTServiceClusSvcPermissions] VERBOSE: [COMPUTER_NAME]: [[SqlPermission]AddNTServiceClusSvcPermissions] Determines the permissions for the principal 'NT SERVICE\ClusSvc'.
VERBOSE: [COMPUTER_NAME]: [[SqlPermission]AddNTServiceClusSvcPermissions] Enumerating permissions for 'NT SERVICE\ClusSvc'.
VERBOSE: [COMPUTER_NAME]: [[SqlPermission]AddNTServiceClusSvcPermissions] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [COMPUTER_NAME]: [[SqlPermission]AddNTServiceClusSvcPermissions] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [COMPUTER_NAME]: [[SqlPermission]AddNTServiceClusSvcPermissions] Connected to SQL instance 'COMPUTER_NAME\INSTANCE_NAME'. (SQLCOMMON0018)
VERBOSE: [COMPUTER_NAME]: LCM: [ End Test ] [[SqlPermission]AddNTServiceClusSvcPermissions] True in 0.2350 seconds.
VERBOSE: [COMPUTER_NAME]: LCM: [ End Resource ] [[SqlPermission]AddNTServiceClusSvcPermissions]
VERBOSE: [COMPUTER_NAME]: LCM: [ Start Resource ] [[SqlEndpoint]CreateHAEndpoint]
VERBOSE: [COMPUTER_NAME]: LCM: [ Start Test ] [[SqlEndpoint]CreateHAEndpoint]
VERBOSE: [COMPUTER_NAME]: [[SqlEndpoint]CreateHAEndpoint] Determines if the endpoint with the name 'HADR' for the instance 'INSTANCE_NAME' is in desired state.
VERBOSE: [COMPUTER_NAME]: [[SqlEndpoint]CreateHAEndpoint] Getting the current values of the endpoint with the name 'HADR' for the instance 'INSTANCE_NAME'.
VERBOSE: [COMPUTER_NAME]: [[SqlEndpoint]CreateHAEndpoint] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [COMPUTER_NAME]: [[SqlEndpoint]CreateHAEndpoint] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [COMPUTER_NAME]: [[SqlEndpoint]CreateHAEndpoint] Connected to SQL instance 'COMPUTER_NAME\INSTANCE_NAME'. (SQLCOMMON0018)
VERBOSE: [COMPUTER_NAME]: [[SqlEndpoint]CreateHAEndpoint] Connect to the instance 'COMPUTER_NAME\INSTANCE_NAME'.
VERBOSE: [COMPUTER_NAME]: [[SqlEndpoint]CreateHAEndpoint] The endpoint 'HADR' is the desired state.
VERBOSE: [COMPUTER_NAME]: LCM: [ End Test ] [[SqlEndpoint]CreateHAEndpoint] True in 0.2810 seconds.
VERBOSE: [COMPUTER_NAME]: LCM: [ End Resource ] [[SqlEndpoint]CreateHAEndpoint]
VERBOSE: [COMPUTER_NAME]: LCM: [ Start Resource ] [[SqlAlwaysOnService]EnableHA]
VERBOSE: [COMPUTER_NAME]: LCM: [ Start Test ] [[SqlAlwaysOnService]EnableHA]
VERBOSE: [COMPUTER_NAME]: [[SqlAlwaysOnService]EnableHA] Determines if the current state of Always On Availability Groups on the instance 'COMPUTER_NAME\INSTANCE_NAME'.
VERBOSE: [COMPUTER_NAME]: [[SqlAlwaysOnService]EnableHA] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [COMPUTER_NAME]: [[SqlAlwaysOnService]EnableHA] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [COMPUTER_NAME]: [[SqlAlwaysOnService]EnableHA] Connected to SQL instance 'COMPUTER_NAME\INSTANCE_NAME'. (SQLCOMMON0018)
VERBOSE: [COMPUTER_NAME]: [[SqlAlwaysOnService]EnableHA] Always On Availability Groups is enabled on the instance 'COMPUTER_NAME\INSTANCE_NAME'.
VERBOSE: [COMPUTER_NAME]: [[SqlAlwaysOnService]EnableHA] Always On Availability Groups is enabled.
VERBOSE: [COMPUTER_NAME]: LCM: [ End Test ] [[SqlAlwaysOnService]EnableHA] True in 0.0470 seconds.
VERBOSE: [COMPUTER_NAME]: LCM: [ End Resource ] [[SqlAlwaysOnService]EnableHA]
VERBOSE: [COMPUTER_NAME]: LCM: [ Start Resource ] [[SqlAG]CreateAG]
VERBOSE: [COMPUTER_NAME]: LCM: [ Start Test ] [[SqlAG]CreateAG]
VERBOSE: [COMPUTER_NAME]: [[SqlAG]CreateAG] Get the current configuration for the availability group 'AG_NAME' on the instance 'INSTANCE_NAME'.
VERBOSE: [COMPUTER_NAME]: [[SqlAG]CreateAG] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [COMPUTER_NAME]: [[SqlAG]CreateAG] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [COMPUTER_NAME]: [[SqlAG]CreateAG] Connected to SQL instance 'COMPUTER_NAME\INSTANCE_NAME'. (SQLCOMMON0018)
VERBOSE: [COMPUTER_NAME]: [[SqlAG]CreateAG] Determines if the configuration for the availability group 'AG_NAME' on the instance 'INSTANCE_NAME' is in desired state.
VERBOSE: [COMPUTER_NAME]: LCM: [ End Test ] [[SqlAG]CreateAG] False in 0.1720 seconds.
VERBOSE: [COMPUTER_NAME]: LCM: [ End Resource ] [[SqlAG]CreateAG]
VERBOSE: [COMPUTER_NAME]: LCM: [ Start Resource ] [[SqlAGListener]CreateAGL]
VERBOSE: [COMPUTER_NAME]: LCM: [ Start Test ] [[SqlAGListener]CreateAGL]
VERBOSE: [COMPUTER_NAME]: [[SqlAGListener]CreateAGL] Determines the current state for the Availability Group listener 'AGL_NAME' for the Availability Group 'AG_NAME' on the instance 'INSTANCE_NAME'..
VERBOSE: [COMPUTER_NAME]: [[SqlAGListener]CreateAGL] Get the current state of the Availability Group listener 'AGL_NAME' for the Availability Group 'AG_NAME' on the instance 'INSTANCE_NAME'.
VERBOSE: [COMPUTER_NAME]: [[SqlAGListener]CreateAGL] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [COMPUTER_NAME]: [[SqlAGListener]CreateAGL] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [COMPUTER_NAME]: [[SqlAGListener]CreateAGL] Connected to SQL instance 'COMPUTER_NAME\INSTANCE_NAME'. (SQLCOMMON0018)
VERBOSE: [COMPUTER_NAME]: LCM: [ End Test ] [[SqlAGListener]CreateAGL] False in 0.1720 seconds.
VERBOSE: [COMPUTER_NAME]: LCM: [ FAILEDCompare ] Completed processing compare operation. The operation returned False.
PowerShell DSC resource DSC_SqlAGListener failed to execute Test-TargetResource functionality with error message: System.Exception: Trying to make a change to a listener that does not exist. ---> System.Exception: System.Exception: Unable to locate the Availability
Group 'AG_NAME' on the instance 'INSTANCE_NAME'.
--- End of inner exception stack trace ---
+ CategoryInfo : InvalidOperation: (root/Microsoft/...gurationManager:String) [], CimException
+ FullyQualifiedErrorId : ProviderOperationExecutionFailure
+ PSComputerName : COMPUTER_NAME

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

Suggested solution to the issue

N/A

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

# This configuration configures the SQL Server and create required SQL objects for ...

Configuration AIOServer
{
    Import-DscResource -Module 'PSDesiredStateConfiguration'
    Import-DscResource -ModuleName 'SqlServerDsc' -ModuleVersion 15.1.1
    Import-DscResource -ModuleName 'NetworkingDsc' -ModuleVersion 8.2.0
    
    Node $AllNodes.Where{$_.Role -eq 'AIOServer'}.NodeName
    {
        # Adding the required service account to allow the cluster to log into SQL
        SqlLogin 'AddNTServiceClusSvc'
        {
            Ensure               = 'Present'
            Name                 = 'NT SERVICE\ClusSvc'
            LoginType            = 'WindowsUser'
            ServerName           = $Node.NodeName
            InstanceName         = $ConfigurationData.SQLConfiguration.InstanceName
        }

        # Add the required permissions to the cluster service login
        SqlPermission 'AddNTServiceClusSvcPermissions'
        {
            Ensure               = 'Present'
            ServerName           = $Node.NodeName
            InstanceName         = $ConfigurationData.SQLConfiguration.InstanceName
            Principal            = 'NT SERVICE\ClusSvc'
            Permission           = 'AlterAnyAvailabilityGroup', 'ViewServerState'

            DependsOn            = '[SqlLogin]AddNTServiceClusSvc'
        }

        # Create a DatabaseMirroring endpoint
        SqlEndpoint 'CreateHAEndpoint'
        {
            Ensure               = 'Present'
            ServerName           = $Node.NodeName
            InstanceName         = $ConfigurationData.SQLConfiguration.InstanceName
            EndPointName         = $ConfigurationData.SQLConfiguration.EndPointName
            EndpointType         = $ConfigurationData.SQLConfiguration.EndPointType
            Port                 = $ConfigurationData.SQLConfiguration.EndPointPort
        }

        # Enable Always On Availability Group
        SqlAlwaysOnService 'EnableHA'
        {
            Ensure              = 'Present'
            ServerName          = $Node.NodeName
            InstanceName        = $ConfigurationData.SQLConfiguration.InstanceName
        }

        # Create the Availability Group
        SqlAG 'CreateAG'
        {
            Ensure                          = 'Present'
            ServerName                      = $Node.NodeName
            InstanceName                    = $ConfigurationData.SQLConfiguration.InstanceName
            Name                            = $Node.AGName
            DatabaseHealthTrigger           = $ConfigurationData.SQLConfiguration.AGDatabaseHealthTrigger
            FailoverMode                    = $ConfigurationData.SQLConfiguration.AGFailoverMode
            AvailabilityMode                = $ConfigurationData.SQLConfiguration.AGAvailabilityMode
            ConnectionModeInSecondaryRole   = $ConfigurationData.SQLConfiguration.AGConnectionModeInSecondaryRole
            AutomatedBackupPreference       = $ConfigurationData.SQLConfiguration.AGAutomatedBackupPreference

            DependsOn                       = '[SqlAlwaysOnService]EnableHA', '[SqlPermission]AddNTServiceClusSvcPermissions'
        }

        # Create the Availability Group Listener
        SqlAGListener 'CreateAGL'
        {
            Ensure              = 'Present'
            ServerName          = $Node.NodeName
            InstanceName        = $ConfigurationData.SQLConfiguration.InstanceName
            AvailabilityGroup   = $Node.AGName
            Name                = $Node.AGLName
            Port                = $ConfigurationData.SQLConfiguration.AGLPort
            IpAddress           = $Node.AGLIPAddress

            DependsOn           = '[SqlAG]CreateAG', '[SqlAlwaysOnService]EnableHA', '[SqlPermission]AddNTServiceClusSvcPermissions'
        }

        # Create inbound firewall rule for the AGL
        Firewall 'AddAGLInboundFirewallRule'
        {
            Name                = $ConfigurationData.SQLConfiguration.AGLInboundFirewallRuleName + " - " + $Node.AGLName
            Protocol            = 'TCP'
            Action              = 'Allow'
            LocalPort           = $ConfigurationData.SQLConfiguration.AGLPort
            Profile             = ('Domain', 'Private','Public')
        }
    }
}

SQL Server edition and version the target node is running

Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

SqlServerDsc

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture : 64-bit
WindowsBuildLabEx : 14393.1794.amd64fre.rs1_release.171008-1615
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

Name Value

PSVersion 5.1.14393.1884
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.1884
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 15.1.1 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.1.1\SqlServerDsc.psd1

Start with the SqlAGListener DSC resource and its Test-TargetResource behavior, using the verbose Test-DscConfiguration output as the reproduction. Verify the behavior when the referenced Availability Group is absent; done means Test-DscConfiguration completes without the reported listener exception and returns the expected resource state.