SQL SERVER – Database Mirroring login attempt failed – Connection handshake failed

The best way I have learnt about building SQL Server AlwaysOn Availability Group is by contacting my close friends who have written the only book on this topic. It is not common that I ping them for such technical queries, but that is what friendship is made off. Recently, while preparing a demo for the AlwaysOn Availability Group, I ran into interesting issue and was able to fix it as well. Let us learn about the Database Mirroring login error.

First, let us look at the configuration.

SERVER1              –              Primary Replica
SERVER2              –              Secondary Replica

They are synchronous and configured for automatic failover. I have shut down SERVER1 and as expected, SERVER2 became primary. As soon as SERVER1 came back, it became secondary, but the databases were NOT synchronizing. That was a surprise to me as I was expecting to see the data moved from SERVER2 to SERVER1 now.

As always, my troubleshooting checklist has ERRORLOG been my first point of investigation. If you have not seen earlier, you can use below blog to know how to find ERRORLOG

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

On SERVER1, I found below messages multiple times.

2015-08-24 01:24:18.480 Logon        Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.'.  [CLIENT: 10.0.0.2]

There are some interesting learning/finding from above messages.

1. Database Mirroring and AlwaysOn use same endpoint and that is why we are seeing Database Mirroring in error message even if I am using AlwaysOn Availability Group.
2. IP Address in the message belongs to SERVER2 (which is primary as of now).

This means that SERVER2 is not able to talk to SERVER1 via Mirroring port. I searched on hex code 8009030c is not helping much because it means – The logon attempt failed, which is already there is the same message.

Steps which I tried after searching on the internet (and they didn’t help)

1. I logged in to SERVER2 via remote desktop and tried making a connection to SERVER1 via Management Studio and it was working fine.
2. Restart endpoints on both replicas. No joy!

USE MASTER
GO
ALTER ENDPOINT hadr_endpoint STATE = STOPPED;
GO
ALTER ENDPOINT hadr_endpoint STATE = STARTED;
GO
[/sql

3. PING from and to each other. This also worked well, which means DNS was not an issue.
4. Verified that SQL was running under domain account and the account was not locked.

None of the above helped. Taking the last hint, Then I started backtracking to recall what I did with my SQL Service account. It did not take long to realize that I had changed the service account domain password in the recent past. I rarely use AlwaysOn Availability Group so I changed the service account password on SERVER1 but forgot on SERVER2.

When I was looking at the error log, I also found below

SQL Server failed to communicate with the filter daemon launch service  (Windows error: The service did not start due to a logon failure.). The full-text filter daemon process failed to start. The full-text search functionality will not be available.

Above message came when SQL was trying to start full-text service automatically. Since I didn’t change the password for full-text service, I was seeing above message.

Solution (which worked for me): Correct the Service account password for SQL Server related services via SQL Server Configuration Manager.

When I corrected the service account password, the databases were synchronized and I was happy again.

Reference: Pinal Dave (https://blog.sqlauthority.com)