SQL SERVER – Database Mirroring Login Attempt Failed with Error ‘Connection Handshake Failed’

One of my friends was trying to configure database mirroring and was having a hard time in getting things working. The challenge was that he was using certificate for mirroring authentication because machines were workgroup not in domain. He followed one of the blog from Microsoft site but still having problems.

SQL SERVER - Database Mirroring Login Attempt Failed with Error 'Connection Handshake Failed' dbm-01

When he contacted me, he had multiple rounds of failure and gave up. I asked him to share SQL Server ERRORLOG from all 3 servers.

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

Here were the messages in ERRORLOG of 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.

Here is the error message in the SQL Server ERRORLOG on witness server.

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]

If we note the IP address, they are for principal and mirror. Above message on witness means that the login from the principal and mirror server: login_mirroring did not have CONNECT Permission on the witness endpoint.

SOLUTION / WORKAROUND

We need to grant permission to account which is failing to connect as per message in Errorlog.

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

As soon as above command was run on Witness server, there were not mirroring login failure and mirroring started working fine.

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

SQL Connection, SQL Mirroring, SQL Scripts, SQL Server
Previous Post
SQL SERVER – SSMS – Script Out Multiple Objects
Next Post
SQL SERVER – Unable to Start SQL Service – Server TCP provider failed to listen on [‘any’ 1433]. Tcp port is already in use.

Related Posts

4 Comments. Leave new

  • Where to run this command?
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring]
    GO
    This is runing for me nowhere… not in CMD, not in Powershell, not in SQL Management studio!!!

    Reply
  • Hi, resolve this error? i have the same error on Amazon AWS instances

    Reply
  • I run that statement and it says Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    Reply
  • the same error please help

    Reply

Leave a Reply