Search

Monday, February 13, 2012

Database Mirroring login attempt by User ' ' failed with error: 'Connection handshake failed',


Sometime you may receive an error in SQL Server's log and Database Mirroring stops:

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

This message actually informs that your security configuration for the mirroring protocol is broken. You can easily rectify this error by fixing the permission on endpoint using below SQL:

GRANT Connect on EndPoint::[EndPoint Name] To [Account]

Below command will fix this issue assuming end point name is “mirroring” and account name used is “ArunL\test”

GRANT Connect on EndPoint::[EndPoint Name] To [ArunL\test]

Note: If your mirroring is suspended then you have to resume mirrirong after this command.

3 comments:

  1. I am getting this error while trying to set up Mirroring. the login is set up as sysadmin. Still the same error..

    ReplyDelete
  2. I am also recieving this error when attempting create an availability group using SQL 2012 SP1 enterprise on Server 2012.
    The SQL server gives the above error in the SQL log and it refers to the secondary domain\machine account not being allowed access.
    The grant command given above will not work with machine accounts and gives an error:
    Cannot find the login 'domain\machineaccount' because it does not exist or you do not have permission.
    There was mention in others posts that this account needs to be in the Security logins section of SQL, but that portion will not allow you to create a domain\machine account.
    Any Ideas?

    ReplyDelete
  3. I discovered that the SQL service account on both windows machines needs to be the same.
    I applied a domain account on these services and this solved the problem.
    However it caused SQL to no longer have access to the data or logs drives.

    ReplyDelete