Feb 3, 2017 7:30:18 PM by Pinal Dave
I was trying to set up database mirroring in my freshly created Azure Virtual Machines and encountered an error. The error was talking about the connectivity problem, but it was something else. Let us learn about how to fix the database mirroring connection error 4.
As soon as I configure database mirroring and click on “Start Mirroring”, I was getting below error.
Alter failed for Database ‘SQLAUTHORITY’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://SRV_W.SQLAUTHORITY.net:5022’.
The database mirroring configuration was not changed. Verify that the server is connected, and try again.
(Microsoft SQL Server, Error: 1456)
I looked around in ERRORLOG files to see if there is anything interesting.
2016-12-26 01:23:16.710 spid43s Database mirroring connection error 4 ‘An error occurred while receiving data: ’24
(The program issued a command but the command length is incorrect.)’.’ for ‘TCP://SRV_W.sqlauthority.net:5022’.
2016-12-26 01:23:36.490 spid75 Error: 1456, Severity: 16, State: 3.
2016-12-26 01:23:36.490 spid75 The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://SRV_W.sqlauthority.net:5022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
2016-12-26 01:23:36.750 spid41s Database mirroring is active with database ‘SQLAUTH’ as the principal copy.
This is an informational message only. No user action is required.
2016-01-01 22:28:14.370 Logon Database Mirroring login attempt by user ‘SQLAUTHORITYSRV_P$.’ failed with error:
‘Connection handshake failed. The login ‘SQLAUTHORITYSRV_P$’ does not have CONNECT permission on the endpoint. State 84.’.
I realized that this is a typical issue where service account is set to LocalSystem. Due to this mirroring, would use the machine name (having $ symbols at the end) to communicate. There are two workarounds for this.
CREATE LOGIN [SQLAUTHORITYSRV_M$] FROM WINDOWS
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [SQLAUTHORITYSRV_M$]
CREATE LOGIN [SQLAUTHORITYSRV_W$] FROM WINDOWS
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [SQLAUTHORITYSRV_W$]
Notice that we are giving permission to machine account of mirror and witness. Same set of command to be run on Mirror and Witness as well with other two machines account.
On Mirror = SQLAUTHORITYSRV_W$ and SQLAUTHORITYSRV_P$
On Witness = SQLAUTHORITYSRV_P$ and SQLAUTHORITYSRV_M$
Same issue can happen in AlwaysOn availability group as well if account is set to LocalSystem.
Have you encountered such errors? What was the solution?
Reference: Pinal Dave (https://blog.sqlauthority.com)
Tags: SQL Server
Written by Pinal Dave
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.