SqlAGReplica: Failed to connect to SQL instance 'hostname\MSSQLSERVER'

[emyatsuna]

Hi,

I was able to create an AG group and listener in the Primary node and tried to connect the details in secondary using SqlAGReplica. I got the following error.

Error: PowerShell DSC resource MSFT_SqlAGReplica failed to execute Set-TargetResource functionality with error message: System.InvalidOperationException: Failed to connect to SQL instance 'hostname\MSSQLSERVER'.

Here are my parameters passed in:

 sqlagreplica{'SQLAddReplica':
      ensure = 'Present'
      name = 'hostname\MSSQLSERVER'
      availabilitygroupname = 'AGGroup'
      servername = 'hostname'
      instancename = 'MSSQLSERVER'
      primaryreplicaservername = $primaryhost
      primaryreplicainstancename = 'MSSQLSERVER'
      endpointhostname = 'hostname'
      failovermode = 'Automatic'
      availabilitymode =  'SynchronousCommit'
      psdscrunascredential = $SqlAdministratorCredential
  }

Any idea what could be the best way to do? I keep on bumping Error 26 when manually connecting using 'hostname\MSSQLSERVER' but successfully connected using 'AGGroup\MSSQLSERVER'.

Thanks.

[johlju]

This one might have been answered before, or something similar to it, but not sure. 🤔 Have you check other issues around SqlAG and SqlAGReplica for similar problems?

[emyatsuna]

Hi @johlju, thanks for the reply.

Yes, I was able to Create AG using the SqlAG on the Primary node. However, I'm unable to add replica using the SqlAGReplica in the Secondary node. I used the same credentials on both Primary and Secondary nodes. I followed the steps from #910 and using the default port 5022 for SQLServerEndpoint. I can relate this to the #1220 issue. I am still getting failed to connect to instance error remotely. Appreciate if you can provide me a link to the answered issues? Thanks.

[emyatsuna]

Hi, just an update. I found the solution with this one. Turns out this is Firewall problem between my Primary and Secondary nodes. Thanks.

Resolved using the following link: https://www.sqlserverlogexplorer.com/fix-error-code-26/#

[johlju]

Thank you for the update @emyatsuna, this will help other users too.