Mirroring not working in SQL Cluster

Mirroring not working in SQL Cluster

Some of you might see this error on a SQL Cluster "Database mirroring login attempt by user 'xxxx' failed with error: Connection handshake failed. The

Checking SQL Services with PowerShell
SQL Counters with PowerShell
Reading and Writing to SQL Databases with PowerShell

Some of you might see this error on a SQL Cluster “Database mirroring login attempt by user ‘xxxx’ failed with error: Connection handshake failed. The login ‘xxxx’ does not have CONNECT permissions on the endpoint.”

Every time that I have seen this error these are the queries that I have ran to fix the issue.

1. Delete the login account that you are wanting to use from SQL logins so that you know you are starting fresh. Then create the login with the query below.

USE [master]
GO
CREATE LOGIN [*accountname*] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

2. Grant connect rights to the account

GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [*accountname*]
GO

3. Stop the endpoint

ALTER ENDPOINT hadr_endpoint STATE=STOPPED

4. Start the endpoint

ALTER ENDPOINT hadr_endpoint STATE=STARTED

5. verify that the account has connect rights

SELECT e.name AS mirror_endpoint_name
    ,s.name AS login_name
    ,p.permission_name
    ,p.state_desc AS permission_state
    ,e.state_desc endpoint_state
FROM sys.server_permissions p
INNER JOIN sys.endpoints e ON p.major_id = e.endpoint_id
INNER JOIN sys.server_principals s ON p.grantee_principal_id = s.principal_id
WHERE p.class_desc = 'ENDPOINT'
AND e.type_desc = 'DATABASE_MIRRORING'

Do this on each target that is in the cluster!

COMMENTS

WORDPRESS: 0