When working with SQL Server AlwaysOn Availability Groups, it is important to ensure that the configuration is set up correctly to avoid any issues. Recently, I encountered an interesting problem while preparing a demo for the AlwaysOn Availability Group. In this blog post, I will share my experience and provide a solution for fixing the Database Mirroring login error.

The Configuration

Let’s start by looking at the configuration:

• SERVER1 – Primary Replica
• SERVER2 – Secondary Replica

Both replicas are synchronous and configured for automatic failover. I shut down SERVER1, and as expected, SERVER2 became the primary replica. However, when SERVER1 came back online, it became the secondary replica, but the databases were not synchronizing. This was unexpected, as I was anticipating the data to be moved from SERVER2 to SERVER1.

Troubleshooting

As always, my troubleshooting checklist starts with checking the ERRORLOG. By investigating the ERRORLOG, I found the following messages on SERVER1:

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]

From these messages, I made a few interesting findings:

• Database Mirroring and AlwaysOn use the same endpoint, which is why we see “Database Mirroring” in the error message, even though we are using AlwaysOn Availability Groups.
• The IP address in the message belongs to SERVER2, which is the current primary replica. This indicates that SERVER2 is unable to communicate with SERVER1 via the Mirroring port.

I tried a few steps that I found on the internet, but they did not resolve the issue:

• I logged in to SERVER2 via remote desktop and successfully made a connection to SERVER1 using Management Studio.
• I restarted the endpoints on both replicas, but it did not solve the problem.
• I performed a PING from and to each replica, and the DNS was not an issue.
• I verified that SQL Server was running under a domain account and that the account was not locked.

None of these steps helped in resolving the issue. However, I then recalled that I had recently changed the service account domain password for SQL Server on SERVER1, but I had forgotten to update it on SERVER2. This realization led me to another clue in the error log:

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.

This message appeared when SQL Server was attempting to start the full-text service automatically. Since I had not changed the password for the full-text service, this error was occurring.

The Solution

The solution that worked for me was to correct the service account password for SQL Server-related services via SQL Server Configuration Manager. Once I updated the service account password, the databases started synchronizing, and I was relieved to see everything working as expected again.

It is crucial to ensure that all SQL Server-related services are using the correct service account password to avoid any login errors and synchronization issues in SQL Server AlwaysOn Availability Groups.

I hope this blog post helps you understand and resolve the Database Mirroring login error in SQL Server. If you have any questions or comments, please feel free to leave them below.