Database mirroring connection error 4

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.

Environment

• SRV_P -Principal- Microsoft SQL Server 2014 – 12.0.4422.0 (X64)
• SRV_M-Mirror- Microsoft SQL Server 2014 – 12.0.4422.0 (X64)
• SRV_W-witness- SQL SERVER 2008 R2

SYMPTOMS

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.

PRINCIPAL SERVER

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.

MIRROR

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.’. 
[CLIENT: 10.0.40.34]
 

WORKAROUND/SOLUTION

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.

1. Change the Service account to domain account

   Read more here: SQL SERVER – Best Practices About SQL Server Service Account and Password Management

1. If you don’t want to use domain account, then we need to create machine account as login and provide CONNECT permission to endpoint (as mentioned in error message)

On principal

use [master]
GO
CREATE LOGIN [SQLAUTHORITYSRV_M$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Mirroring] TO [SQLAUTHORITYSRV_M$]
GO
use [master]
GO
CREATE LOGIN [SQLAUTHORITYSRV_W$] FROM WINDOWS
GO
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.

• Hi,

  I’m trying to set up database mirroring using the database mirroring wizard in SQL Server 2008 R2 RTM x64 developer edition on Windows Server 2008 R2 EE x64 SP.

  Primary Server: SRV1

  Instance:SRV1/ins1

  Mirror Server:SRV2

  Instance:SRV2ins1

  I have selected all default configuration in the wizard to set up mirroring for AdventureWorks database .

  But getting the below error:

  I’m able to telnet the port 5022 from both the servers SRV1 & SRV2.

  I did turn OFF the Windows firewall too.

  Do we need to do anything with MSDTC configuration?

  What else do I need to verify to resolve the issue?

  Thanks

• Perry Whittle

  SSC Guru

  Points: 233806

  Is ABC.com your actual domain name?

  Database mirroring error

  ---

  The server network address «TCP://SRV1.ABC.COM:5022» can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

  ————————————————————————————————————

  «Ya can’t make an omelette without breaking just a few eggs» 😉

• pshaship

  SSCertifiable

  Points: 6125

  No. ABC.COM is NOT the actual domain. I have replaced my actual domain name with ABC while posting in the forum.

• seth delconte

  SSCertifiable

  Points: 6388

  Here are some good related fixes… are you getting that error for your Principal server (I assume so, since I believe 5022 is the default port…).

  http://blog.sqlauthority.com/2010/01/11/the-server-network-address-tcpsqlserver5023-can-not-be-reached-or-does-not-exist-check-the-network-address-name-and-that-the-ports-for-the-local-and-remote-endpoints-are-operational-microso/[/url]

  http://blog.sqlauthority.com/2007/04/22/sql-server-fix-error-1418-microsoft-sql-server-the-server-network-address-can-not-be-reached-or-does-not-exist-check-the-network-address-name-and-reissue-the-command/[/url]

• pshaship

  SSCertifiable

  Points: 6125

  Thanks for the links.

  Below are the error messages from the errorlog

  On Principal server’s error log:

  Database mirroring connection error 4 ‘An error occurred while receiving data: ‘10054(An existing connection was forcibly closed by the remote host.)’.’ for ‘TCP://SRV2.ABC.COM:5022’.

  On Mirror Server’s errorlog

  Database Mirroring login attempt by user ‘ABCmssqlsrv.’ failed with error: ‘Connection handshake failed. The login ‘ABCmssqlsrv’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: 10.10.10.13]

  I tried to grant the connect permission to ABCmssqlsrv but it’s complaining that the login DOES NOT exist.

  GRANT CONNECT ON ENDPOINT::Mirroring TO [ABCmssqlsrv]

  Msg 15151, Level 16, State 1, Line 1

  Cannot find the login ‘ABCmssqlsrv’, because it does not exist or you do not have permission.

  Please advice..

• seth delconte

  SSCertifiable

  Points: 6388

  Have you restored the last transaction log backup on the mirror instance with NO RECOVERY?

  http://www.sqlservercentral.com/Forums/Topic683162-146-1.aspx#bm684411

  Also, check and see what login accounts your SQL services are using — apparently you need certificates if you are using Local Service Accounts:

  http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/73fb15c0-9270-4cbf-a74e-544639e792da/[/url]

• Perry Whittle

  SSC Guru

  Points: 233806

  pshaship (7/12/2011)

  ---

  Thanks for the links.

  Below are the error messages from the errorlog

  On Principal server’s error log:

  Database mirroring connection error 4 ‘An error occurred while receiving data: ‘10054(An existing connection was forcibly closed by the remote host.)’.’ for ‘TCP://SRV2.ABC.COM:5022’.

  On Mirror Server’s errorlog

  Database Mirroring login attempt by user ‘ABCmssqlsrv.’ failed with error: ‘Connection handshake failed. The login ‘ABCmssqlsrv’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: 10.10.10.13]

  I tried to grant the connect permission to ABCmssqlsrv but it’s complaining that the login DOES NOT exist.

  GRANT CONNECT ON ENDPOINT::Mirroring TO [ABCmssqlsrv]

  Msg 15151, Level 16, State 1, Line 1

  Cannot find the login ‘ABCmssqlsrv’, because it does not exist or you do not have permission.

  Please advice..

  add the login to the mirror instance and grant connect permissions to the endpoint and re try

  ————————————————————————————————————

  «Ya can’t make an omelette without breaking just a few eggs» 😉

• pshaship

  SSCertifiable

  Points: 6125

  Issue resolved:-)

  Reason for the issue:

  The SQL Server service account abcmssqlsrv did not exit in logins on both Principal & Mirror

  Solution:

  Add the service account in logins on both the servers and grant the connect permission as below;

  GRANT CONNECT ON ENDPOINT::Mirroring TO [abcmssqlsrv]

  Hope this helps someone!!

• Perry Whittle

  SSC Guru

  Points: 233806

  geenrally you dont want to use the sql server service account as the security principal for your endpoints. Create a base domain user, grant login to sql server and grant connect permissions to the end point

  ————————————————————————————————————

  «Ya can’t make an omelette without breaking just a few eggs» 😉

• Ignacio A. Salom Rangel

  SSC-Insane

  Points: 20443

  pshaship (7/12/2011)

  ---

  Issue resolved:-)

  Reason for the issue:

  The SQL Server service account abcmssqlsrv did not exit in logins on both Principal & Mirror

  Solution:

  Add the service account in logins on both the servers and grant the connect permission as below;

  GRANT CONNECT ON ENDPOINT::Mirroring TO [abcmssqlsrv]

  Hope this helps someone!!

  Thanks for sharing!