[SQL Server] Database Mirroring login attempt by user ‘DOMAIN\SERVER$.’ failed with error. ‘Connection handshake failed. The login ‘DOMAIN\SERVER$’ does not have CONNECT permission on the endpoint. State 84.’.

John Reyes

When setting up mirroring (and witnessing) among SQL Server instances when the servers are configured using Active Directory it will automatically try to auth using the ID of the server. You need to grant CONNECT permissions on the endpoint on each server for the other server that will be connecting to it (or two if using a witness). Example:

You have three servers.

1. DOMAIN\PRINCIPAL

2. DOMAIN\MIRROR

3. DOMAIN\WITNESS

On PRINCIPAL:

Connect to your database expand the objects Security and Logins. Right click on Logins and select New. Add the following logins:

DOMAIN\MIRROR$

DOMAIN\WITNESS$

The dollar sign at the end specifies that it is a server and not a user, and it is required.

Next select the master database and run the following queries:

GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\MIRROR$];
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\WITNESS$];

The square brackets are literal. They have to be in the query or you’ll get a syntax error.

On MIRROR:

Connect to your database expand the objects Security and Logins. Right click on Logins and select New. Add the following logins:

DOMAIN\PRINCIPAL$

DOMAIN\WITNESS$

Next select the master database and run the following queries:

GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\PRINCIPAL$];
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\WITNESS$];

On WITNESS:

Connect to your database expand the objects Security and Logins. Right click on Logins and select New. Add the following logins:

DOMAIN\PRINCIPAL$

DOMAIN\WITNESS$

Next select the master database and run the following queries:

GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\PRINCIPAL$];
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\WITNESS$];

That’s all there is to it. You may have to give these logins additional permissions but that will be expressed in a different error with the exact permissions they need if it arises.

There’s a bug (among many others) in SQL server where sometimes this just doesn’t work. I found it helps if you first delete a login then recreate with:

USE MASTER;
GO
CREATE LOGIN [DOMAIN\LOGIN$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\LOGIN$];
GO