Database mirroring is a popular feature in SQL Server that provides high availability and disaster recovery solutions. However, there are instances where clients still prefer to use database mirroring over Always On Availability Group. In this blog post, we will discuss one possible cause of database mirroring Error 1456 – The ALTER DATABASE command could not be sent to the remote server instance.

A client recently contacted me during a disaster situation, facing this issue after trying to re-configure the database mirroring witness server. Their old witness server had crashed, and they had built a new server to replace it. However, they were having a hard time adding the new server to the database mirroring configuration.

The error message they encountered was: “The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://srv_w.sqlauthority.com:5022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)”

Upon checking the ERRORLOG, we found the following information on the Principal Server:

2018-03-15 07:16:12.040 spid49s      Database mirroring is inactive for database 'test'. This is an informational message only. No user action is required.
2018-03-15 07:16:12.110 Logon        Database Mirroring login attempt by user 'SQLAUTHORITY\srv_w$' failed with error: 'Connection handshake failed. The login 'SQLAUTHORITY\srv_w$' does not have CONNECT permission on the endpoint. State 84.'  [CLIENT: 10.17.144.60]
2018-03-15 07:16:12.110 spid109s     Error: 1474, Severity: 16, State: 1.
2018-03-15 07:16:12.110 spid109s     Database mirroring connection error 5 'Connection handshake failed. The login 'SQLAUTHORITY\srv_w$' does not have CONNECT permission on the endpoint. State 84.' for 'TCP://srv_w.sqlauthority.com:5022'.
2018-03-15 07:16:14.600 Logon        Database Mirroring login attempt by user 'SQLAUTHORITY\srv_w$' failed with error: 'Connection handshake failed. The login 'SQLAUTHORITY\srv_w$' does not have CONNECT permission on the endpoint. State 84.'  [CLIENT: 10.17.144.60]

At first, it seemed like the CONNECT permissions were not provided on the endpoint. However, it turned out to be more complex than that. The account in question, ‘SQLAUTHORITY\srv_w$’, was a Machine Account on a new server. Since this was a new server, the SID (Security Identifier) of this account would be different from the account that was present on the old server.

According to the documentation, “For two server instances to connect to each other’s database mirroring endpoint, the login account of each instance requires access to the other instance. Also, each login account requires ‘connect’ permission to the Database Mirroring endpoint of the other instance.”

To resolve this issue, we needed to ensure that the SID for this login on other servers matched the new SID. We accomplished this by deleting the old login and re-adding it, which created a new SID. After performing this step, we were able to add the witness successfully.

If you encounter a similar error, it is worth checking the CONNECT permissions on the endpoint. However, keep in mind that if you are dealing with a new server and a machine account, the SID may need to be updated to match the new server.

For more information on similar errors and solutions, you may refer to the following blog posts:

• SQL SERVER – Database Mirroring Login Attempt Failed with Error ‘Connection Handshake Failed’
• SQL SERVER – Database Mirroring Connection Error 4 – An Error Occurred While Receiving Data: 10054

Database mirroring can be a powerful tool for ensuring high availability and disaster recovery in SQL Server. However, it is important to understand the potential issues that can arise, such as Error 1456. By troubleshooting and resolving these issues, you can ensure the smooth operation of your database mirroring configuration.