Wednesday, May 6, 2015

Troubleshooting SQL Server mirroring message

Symptom:

Error: 1443, Severity: 16, State: 2.
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://vmmdb01:5023'.

Database Mirroring login attempt by user 'NT Service\MSSQL$PROD.' failed with error: 'Connection handshake failed. The login 'NT Service\MSSQL$PROD' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: 2001:0:5ef5:79fb:2839:2416:f5ff:fdf0]

How to fix: 

Grant permission to all remote SQL instance connecting users to current instance DB Mirror End point

USE [master]
GO
CREATE LOGIN [NT SERVICE\MSSQL$PROD] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

GRANT CONNECT ON ENDPOINT::[Mirroring] TO [NT SERVICE\MSSQL$PROD2]; 

How to verify:

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'