Environment

-> I was tasked to setup Always on Availability group on a newly provisioned database server.

-> Everything went fine from the “New Availability Group Wizard”, except the last step which was to “Join the Database to Availability Group”. This last step was executing indefinitely.

-> Checked for blockings and could not see anything either on JBSAG1 and JBSAG2.

->Checked SQL Server errorlog,

JBSAG1
_{^{2022-03-02 16:10:40.840 Logon Database Mirroring login attempt by user ‘JBSWIKI\JBSAG2$.’ failed with error: ‘Connection handshake failed. The login ‘JBSWIKI\JBSAG2$’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: 192.168.1.4]}}

JBSAG2

_{^{2022-03-02 16:13:26.090 spid73 State information for database ‘JBDB’ – Hardened Lsn: ‘(38:114176:1)’ Commit LSN: ‘(0:0:0)’ Commit Time: ‘Mar 2 2022 3:57PM’
2022-03-02 16:13:26.100 spid73 State information for database ‘JBDB’ – Hardened Lsn: ‘(38:114176:1)’ Commit LSN: ‘(0:0:0)’ Commit Time: ‘Mar 2 2022 3:57PM’
2022-03-02 16:13:26.100 spid73 Starting up database ‘JBDB’.
2022-03-02 16:13:26.110 spid73 The database ‘JBDB’ is marked RESTORING and is in a state that does not allow recovery to be run.}}

-> Executed below query on both JBSAG1 and JBSAG2,

select r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.last_connect_error_description,
rs.last_connect_error_number, rs.last_connect_error_timestamp
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r
on rs.replica_id=r.replica_id

JBSAG1

JBSAG2

-> Below is the error message,

An error occurred while receiving data: ’24(The program issued a command but the command length is incorrect.)’.

-> It is clear that that there is a connection issue where JBSAG2 is not able to connect to JBSAG1 when using the SeverName (having $ symbols at the end) to communicate. The connection uses SeverName (having $ symbols at the end) when the SQL Service account is Local System and that is when I realized that I have not used the appropriate Service account on JBSAG2.

-> I changed the SQL Service account on JBSAG2 to JBSWIKI\jbswiki and the issue resolved right away.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.