Database mirroring is a high-availability feature in SQL Server that allows you to create a redundant copy of your database on another server. This ensures that your data is always available, even in the event of a server failure.

However, configuring database mirroring can sometimes be a challenging task, especially when dealing with authentication in a workgroup environment. In this article, we will discuss a common issue faced by users when configuring database mirroring with certificate authentication.

One of my friends recently encountered difficulties while setting up database mirroring in a workgroup environment. He followed a blog post from the Microsoft site but was still facing problems. After multiple failed attempts, he reached out to me for assistance.

I asked him to share the SQL Server ERRORLOG from all three servers involved in the mirroring configuration. The ERRORLOG provides valuable information about any errors or issues encountered during the mirroring setup.

Upon analyzing the ERRORLOG, we found the following messages on the principal server:

2016-01-22 04:48:17.13 spid21s Error: 1474, Severity: 16, State: 1.
2016-01-22 04:48:17.13 spid21s Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://Witness:5022'.
2016-01-22 04:48:36.53 spid118 Error: 1456, Severity: 16, State: 3.
2016-01-22 04:48:36.53 spid118 The ALTER DATABASE command could not be sent to the remote server instance 'TCP://Witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Additionally, the witness server’s ERRORLOG contained the following error message:

Database Mirroring login attempt failed with error: 'Connection handshake failed. The login 'login_mirroring' does not have CONNECT permission on the endpoint. State 84.' [CLIENT: xx.xx.xx.xx]

Upon further investigation, we discovered that the login ‘login_mirroring’ did not have the necessary CONNECT permission on the witness endpoint. This was the cause of the mirroring login failure.

To resolve this issue, we needed to grant the CONNECT permission to the ‘login_mirroring’ account on the witness server’s endpoint. We achieved this by running the following command on the witness server:

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring]
GO

After executing the above command, the mirroring login failure was resolved, and the database mirroring started working seamlessly.

Configuring database mirroring can be a complex task, especially when dealing with authentication in a workgroup environment. However, by carefully analyzing the SQL Server ERRORLOG and addressing any permission issues, you can overcome these challenges and ensure a successful database mirroring setup.

Stay tuned for more SQL Server tips and tricks!