Sync-DbaAvailabilityGroup - doesn't connect to Replicas using custom port

[andrew-bickerton]

Environmental information

PowerShell version:

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

dbatools Module version:

Name    : dbatools
Path    : C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.23\dbatools.psd1
Version : 1.0.23

SQL Server:

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64)   May 15 2019 19:14:30   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor) 

/* REPLACE WITH output of @@LANGUAGE */

Report

When using Sync-DbaAvailabilityGroup as:

Sync-DbaAvailabilityGroup -Primary "PrimaryInstance,CustomPort" -AvailabilityGroup "AGName"

Response received is:
WARNING: [12:12:44][Sync-DbaAvailabilityGroup] Error occurred while establishing connection to | Access is denied

Through monitoring I've been able to determine that Sync-DbaAvailabilityGroup is retrieving the list of Replica names, but not their ports. And is only trying to connect to the Replicas against port 1433.

There is a workaround:

Sync-DbaAvailabilityGroup -Primary "PrimaryInstance,CustomPort" -Secondary @("ReplicaOne,CustomPort","ReplicaTwo,CustomPort")

An ideal fix would be to have the powershell function determine the port number for the Replicas.
If cannot determine port number perhaps a better sequence would be:

1. Try connecting using the same port number as used on the primary node?
2. If no response try default port (1433)
3. Failing that the error message should include what it was trying to connect to and the port number.

Host used

• powershell.exe
• [ x] ISE
• [ x] VS Code
• Other (please specify)

If anything other than powershell.exe was used, please confirm that you can duplicate the issue with powershell.exe

• [x ] Still buggy in powershell.exe

Errors Received

writeErrorStream      : True
PSMessageDetails      : 
Exception             : System.Exception: Access is denied ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was 
                        not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not 
                        open a connection to SQL Server) ---> System.ComponentModel.Win32Exception: Access is denied
                           --- End of inner exception stack trace ---
                           at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String 
                        newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String 
                        accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
                           at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection 
                        owningConnection, DbConnectionOptions userOptions)
                           at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, 
                        DbConnectionOptions userOptions)
                           at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
                           at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
                           at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, 
                        DbConnectionOptions userOptions, DbConnectionInternal& connection)
                           at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
                           at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal 
                        oldConnection, DbConnectionInternal& connection)
                           at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, 
                        DbConnectionOptions userOptions)
                           at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
                           at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
                           at System.Data.SqlClient.SqlConnection.Open()
                           at CallSite.Target(Closure , CallSite , Object )
                           --- End of inner exception stack trace ---
TargetObject          :  (PrimaryNode,CustomPort)
CategoryInfo          : ConnectionError: (PrimaryNode,CustomPort:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Sync-DbaAvailabilityGroup,Stop-Function
ErrorDetails          : Access is denied
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.23\allcommands.ps1: line 80171
                        at Sync-DbaAvailabilityGroup<Process>, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.23\allcommands.ps1: line 65169
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {0, 1}

Steps to Reproduce

1. Create 2 SQL Instances with custom TCP Port
2. Create an AG across both instances
3. Create a new login on Primary instance
4. Run: Sync-DbaAvailabilityGroup -Primary "PrimaryInstance,CustomPort" -AvailabilityGroup "AGName"

Expected Behavior

New Login to appear on Secondary with identical SID/HashedPassword

Actual Behavior

Sync error'd and login did not appear on Secondary

[wsmelton]

This is a bug in SMO that we cannot fix because the port number for secondary instances is not known or available for the data stored for a given replica in SQL Server. We have no way of accurately getting the port number so if you are using custom ports the workaround you provided is the only way it can work.

We may want to add that as an example in the documentation if it is not already to help clarify this problem.

[wsmelton]

Just a small note the SQL Server DSC module has this same issue and actually prevents some cases of building AGs utilizing DSC because of it. (I'm not aware if they have come up with any workaround for it either so far.)

[andrew-bickerton]

Thanks for the clarification @wsmelton I thought as much (when working with the AG through SSMS, it exhibits similar behaviour - have to connect to each replica individually by going into Custom connections...)

That said, I would say the response of:

WARNING: [12:12:44][Sync-DbaAvailabilityGroup] Error occurred while establishing connection to | Access is denied

is a minor bug as it doesn't provide details as to what it was trying to connect to and where the failure occurred.