The client contacted me during a disaster and they were facing this issue after the disaster situation. They were trying to re-configure database mirroring witness server and were having a hard time in doing that. Their old witness server had crashed, and they had built a new server and were trying to add it to the database mirroring configuration.
Here is the error when we try to add witness server to the current mirrored database.
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://srv_w.sqlauthority.com:5022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)
As usual, I asked them to check ERRORLOG and found below on Principal Server
2018-03-15 07:16:12.040 spid49s Database mirroring is inactive for database ‘test’. This is an informational message only. No user action is required.
2018-03-15 07:16:12.110 Logon Database Mirroring login attempt by user ‘SQLAUTHORITY\srv_w$’ failed with error: ‘Connection handshake failed. The login ‘SQLAUTHORITY\srv_w$’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: 10.17.144.60]
2018-03-15 07:16:12.110 spid109s Error: 1474, Severity: 16, State: 1.
2018-03-15 07:16:12.110 spid109s Database mirroring connection error 5 ‘Connection handshake failed. The login ‘SQLAUTHORITY\srv_w$’ does not have CONNECT permission on the endpoint. State 84.’ for ‘TCP://srv_w.sqlauthority.com:5022’.
2018-03-15 07:16:14.600 Logon Database Mirroring login attempt by user ‘SQLAUTHORITY\srv_w$’ failed with error: ‘Connection handshake failed. The login ‘SQLAUTHORITY\srv_w$’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: 10.17.144.60]
I have already blogged about similar errors earlier. You may want to refer them if the solution in this blog is not helping.
- SQL SERVER – Database Mirroring Login Attempt Failed with Error ‘Connection Handshake Failed’
- SQL SERVER – Database Mirroring Connection Error 4 – An Error Occurred While Receiving Data: 10054
This issue looked like the CONNECT permissions are not provided on the endpoint, but it was not that easy. I used the below query to check of the CONNECT permission has been granted for this account to the endpoint.
SELECT EP.name, SP.STATE, CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) AS GRANTEE FROM sys.server_permissions SP , sys.endpoints EP WHERE SP.major_id = EP.endpoint_id ORDER BY Permission,grantor, grantee; GO
If CONNECT permission is not provided we need to provide the same. In our case, everything looked good, but we still encountered failures. Then a thought provoked me that this is a new server and the account we are talking about is a Machine Account. i.e., SQLAUTHORITY\srv_w$. As this is a new server the SID of this account will be different of that of the account which was present on the old server.
As per documentation “For two server instances to connect to each other’s database mirroring endpoint point, the login account of each instance requires access to the other instance. Also, each login account requires “connect” permission to the Database Mirroring endpoint of the other instance.”
So, the SID for this login present in other servers will have an Old SID as they have not re-added the account. I ran below command to make a note of the SID.
select * from sys.syslogins where name = 'sqlauthority\srv_w$'
We deleted the old login and re-added the login which will create the new SID. After that, we were able to add witness successfully.
Reference: Pinal Dave (https://blog.sqlauthority.com)