SqlAlwaysOnService/SqlAGReplica: Failed to connect to SQL instance

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

I am getting this error when I enable the AlwaysOn using this DSC 'SqlAlwaysOnService'. I can manually enable it from the configuration without any issue.

Updated: I noticed that I got the same issue with AddReplica. (but SqlAG is working. )

Verbose logs showing the problem

"Message":  "PowerShell DSC resource MSFT_SqlAlwaysOnService  failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance \u0027MY-SQL-01\u0027. ",
 "SerializedRemoteException":  "System.Exception: System.InvalidOperationException: Failed to connect to SQL instance \u0027MY-SQL-01\u0027.",
 "SerializedRemoteInvocationInfo":  "System.Management.Automation.InvocationInfo",
 "ErrorRecord":  "System.InvalidOperationException: Failed to connect to SQL instance \u0027MY-SQL-01\u0027.",
 "WasThrownFromThrowStatement":  false,
 "Message":  "System.InvalidOperationException: Failed to connect to SQL instance \u0027MY-SQL-01\u0027.",
 "Data":  "System.Collections.ListDictionaryInternal",
 "InnerException":  null,
 "TargetSite":  "Void EndInvoke()",
 "StackTrace":  "   at System.Management.Automation.Runspaces.AsyncResult.EndInvoke()\r\n   at System.Management.Automation.PowerShell.CoreInvokeRemoteHelper[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)\r\n   at System.Management.Automation.PowerShell.CoreInvoke[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)\r\n   at System.Management.Automation.PowerShell.Invoke(IEnumerable input, PSInvocationSettings settings)\r\n   at Microsoft.PowerShell.DesiredStateConfiguration.Internal.ResourceProviderAdapter.ExecuteCommand(PowerShell powerShell, ResourceModuleInfo resInfo, String operationCmd, List`1 acceptedProperties, CimInstance nonResourcePropeties, CimInstance resourceConfiguration, LCMDebugMode debugMode, PSInvocationSettings pSInvocationSettings, UInt32\u0026 resultStatusHandle, Collection`1\u0026 result, ErrorRecord\u0026 errorRecord, PSModuleInfo localRunSpaceModuleInfo)",

Suggested solution to the issue

No idea. but I tested enabling the 'AlwaysOn' manually and it works so something wrong with my code or this DSC.

SQL Server edition and version the target node is running

Microsoft SQL Server 2016 (SP1-CU8) (KB4077064) - 13.0.4474.0 (X64) Feb 24 2018 13:53:17 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name Version Path

---

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

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

SqlAlwaysOnService EnableHADR
        {
            Ensure               = 'Present'
            InstanceName         = $Node.InstanceName
            ServerName           = $Node.NodeName
            DependsOn            = '[SqlServerEndpoint]SQLConfigureEndpoint-Instance'
        }

SqlAGReplica AddReplica
            {
                Ensure                     = 'Present'
                Name                       = $Node.NodeName
                AvailabilityGroupName      = $Node.AvailabilityGroupName
                ServerName                 = $Node.NodeName
                InstanceName               = "$($Node.InstanceName):1433"
                PrimaryReplicaServerName   = ( $AllNodes | Where-Object { $_.Role -eq 'MS SQL Primay Replica' } ).NodeName
                PrimaryReplicaInstanceName = ( $AllNodes | Where-Object { $_.Role -eq 'MS SQL Primay Replica' } ).InstanceName
                DependsOn                  = '[SqlAlwaysOnService]EnableHADR'
                ProcessOnlyOnActiveNode    = $Node.ProcessOnlyOnActiveNode
            }

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture : 64-bit
WindowsBuildLabEx : 14393.2248.amd64fre.rs1_release.180427-1804
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

Name Value

---

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

Version of the DSC module that was used ('dev' if using current dev branch)

Start by reproducing the configuration with the SqlAlwaysOnService and SqlAGReplica resources, using the supplied SQL Server, PowerShell, operating system, and module versions. Compare the resource behavior with manually enabling AlwaysOn and adding the replica; done means both DSC resources connect to the SQL instance and complete successfully without the reported error.