Database mirroring is a feature in SQL Server that allows you to create a redundant copy of your database for high availability and disaster recovery purposes. However, sometimes you may encounter errors when setting up database mirroring, such as the “Database mirroring connection error 4”. In this article, we will discuss how to fix this error.

Symptoms

When configuring database mirroring and clicking on “Start Mirroring”, you may receive the following error:

Alter failed for Database 'SQLAUTHORITY'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://SRV_W.SQLAUTHORITY.net:5022'.
The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)

Additionally, the error log files may contain the following entries:

Database mirroring connection error 4 'An error occurred while receiving data: '24(The program issued a command but the command length is incorrect.)'.' for 'TCP://SRV_W.sqlauthority.net:5022'.
Error: 1456, Severity: 16, State: 3.
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://SRV_W.sqlauthority.net:5022'.
The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Root Cause

The root cause of this error is typically related to the service account being set to LocalSystem. When mirroring is used, the machine name (with $ symbols at the end) is used for communication. This can cause connectivity issues and result in the error.

Workaround/Solution

There are two workarounds to fix this issue:

1. Change the service account to a domain account. This ensures that the machine name is not used for communication. For more information on best practices for SQL Server service account and password management, refer to the following article: SQL SERVER – Best Practices About SQL Server Service Account and Password Management.
2. Create a machine account as a login and provide CONNECT permission to the endpoint. This can be done using the following commands:

-- On principal
USE [master]
GO
CREATE LOGIN [SQLAUTHORITY\SRV_M$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [SQLAUTHORITY\SRV_M$]
GO

-- On mirror
USE [master]
GO
CREATE LOGIN [SQLAUTHORITY\SRV_W$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [SQLAUTHORITY\SRV_W$]
GO

-- On witness
USE [master]
GO
CREATE LOGIN [SQLAUTHORITY\SRV_P$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [SQLAUTHORITY\SRV_P$]
GO

Make sure to run the above commands on all the machines involved in the database mirroring setup.

Note that the same issue can occur in AlwaysOn availability groups if the account is set to LocalSystem.

Conclusion

Database mirroring is a powerful feature in SQL Server that provides high availability and disaster recovery capabilities. However, when encountering errors like the “Database mirroring connection error 4”, it is important to understand the root cause and apply the appropriate solution. By changing the service account to a domain account or creating machine accounts with the necessary permissions, you can resolve this error and ensure a successful database mirroring setup.

Have you encountered similar errors in your SQL Server environment? What solutions have you found? Share your experiences in the comments below!