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'
No comments:
Post a Comment