Standard

Posted by

Posted on

July 3, 2020

Posted under

AlwaysON

Comments

Leave a comment

Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (8009030c) 0x8009030c.

Environment

-> Server JBAG1 is the primary replica and Server JBAG2 is the secondary replica. Server JBAG1 and JBAG2 are part of a failover cluster without shared storage. Server JBAG1 and JBAG2 participate in Always on availability group using synchronous commit mode with automatic failover.

-> Database JBDB is part of a Availability group JBAG. Database in Primary replica seems synchronized, but Replica JBAG2\IN01 is down.

-> Database in secondary replica is not-synchronizing.

-> lets look at the Always on dashboard,

-> Checking the SQL Server errorlog,

JBAG1\IN01

2020-07-03 07:34:13.345 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.'.  

2020-07-03 07:35:11.500 spid42s A connection timeout has occurred on a previously established connection to availability replica 'JBAG2\IN01' with id [FDBE71F5-C8F7-4A98-AE01-3B4E23451908]. Either a networking or a firewall issue exists or the availability replica has transitioned 

JBAG2\IN01

2020-07-03 07:35:12.380 spid77s A connection timeout has occurred while attempting to establish a connection to availability replica 'JBAG1\IN01' with id [FA3748D6-57FE-4247-8F92-245D2C40418A]. Either a networking or firewall issue exists, or the endpoint address provided for the replica

-> The error message in primary replica JBAG1\IN01 ” 2020-07-03 07:34:13.345 Logon Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.’.  [CLIENT: 192.168.0.33]” refers that the primary and secondary replica is not able to talk to each other.

-> Logged into JBAG1 and pinged JBAG2,

-> It was pinging fine. Logged into JBAG2 and tried IPCONFIG,

-> IPAddress for JBAG2 as per ipconfig is 192.168.0.33. There seems to be some issue in JBAG1 where it is resolving to a wrong IPaddress for JBAG2.

-> I checked the hosts file in location C:\windows\System32\drivers\etc on both servers JBAG1 and JBAG2. I was able to see a wrong entry for JBAG2 in the hostfile.

-> I removed the wrong entry and saved the hosts file. It all started working fine,

-> There are scenarios where the same error happens, but pinging to server resolves the correct IPAddress. In those cases verify if SQL Service account password was changed recently and not updated in SQL Server configuration manager. In scenarios like this you will have to try restarting SQL Services and check if SQL comes online. If it fails with an credential issue, then update the password in SQL Server configuration manager and that solves the issue.

-> The same error can happen if SQL Service account doesn’t have proper access to the other instance. Verify if access is provisioned properly on all servers in the availability group and provide access if required.

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.

Leave a Reply