none
Error 1418, fails to connect mirror server

    Question

  • II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below

     a)       On primary server:  

    Error: 1474, Severity: 16, State: 1

    Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'

    b)       On mirror server:

                      Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error:    'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'

     

    It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command

      GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];

    Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints

    Hope someone can help me resolve this issue... Thank you very much..

     

    Monday, February 20, 2006 10:39 AM

Answers

  • In BOL there is a section that deals explicitly with setup errors for database mirroring.

    Search for "troubleshooting database mirroring."

    Thanks,

    Mark

    Friday, March 17, 2006 10:42 PM

All replies

  • What is the account that SQL server is running as?
    Thursday, February 23, 2006 12:16 AM
  • The account that SQL runs as is the one that it tries to login onto the other server as.
    • Proposed as answer by Saubyj Tuesday, March 10, 2009 7:26 PM
    Thursday, February 23, 2006 12:18 AM
  • Same issue here in our environment. Two database servers, one domain, one user, full permissions... Yet the connection is forcably terminated under an anonymous user.

    Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.
    Monday, March 13, 2006 8:29 PM
  • In BOL there is a section that deals explicitly with setup errors for database mirroring.

    Search for "troubleshooting database mirroring."

    Thanks,

    Mark

    Friday, March 17, 2006 10:42 PM
  • I have exactly the same problem :

    Date  5/21/2006 1:56:49 PM
    Log  SQL Server (Current - 5/21/2006 1:56:00 PM)

    Source  Logon

    Message
    Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: 10.16.6.14]

    That's from the error log of the mirror.

    Both the principal and the mirror have sql running as the local service account for each machine respectively.

    I've tried the wizard, I've tried TSQL, I've done all the troubleshooting steps you recommend.  Can you please be very specific on what to do and what to put as logins when both principal and mirror run as each machines local service account.

    Thanks!

    Mark

    Sunday, May 21, 2006 7:04 PM
  • Sorry - nevermind - it helps to read the fine print.  I had not seen anywhere that if you use the local service account you must use Certificates.

    Once i did that it works just fine. 

    Hopefully, in future releases you'll make it easier to do that.

    Thanks!

    Mark

    Sunday, May 21, 2006 8:02 PM
  • Do you normally use Local System to run your SQL Server process?

    Thanks,

    Mark

    Monday, May 22, 2006 6:11 PM
  • Hello Mark,

    Could you send me the documentation on how to do this (create certificates), I have read a few but having problems on the mirror node.

    Thursday, November 16, 2006 2:52 PM
  • i too would like to create certificates i have the same problem
    Thursday, December 28, 2006 12:48 AM
  • I met the same problem.

    This issue is due to the wrong login authority.when we excute the TSQL "ALTER DATABASE WorkDataBase SET PARTNER ='TCP://URL:7022'",this operating lead the SQL Server send a request to the mirror point with NT AUTHORITY\ANONYMOUS enven if the SQL Server service is running as the same domain user as the mirror point does.

    And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.

    if someone got other answers,please write here.

    Tuesday, February 13, 2007 9:14 AM
  • Hello everybody.

     

    After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!

    Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.

     

        0)   In this example we are inside a DOMAIN. We have a domain user called MRRUSR 

    1)       Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.

    2)       MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.

    3)       MRRUSR is the MSSQLSERVER service account in both machines

    4)       Machine A is gonna be the PRINCIPAL, B the MIRROR.

    5)       Log on in my machine as MRRUSR

    6)       Open Sql server Studio. Connect to both machines using windows authenticacion.

    7)       Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data

    8)       Backup the new DB (Full backup) with .bak extension

    9)       Backup the new DB (Transaction log) with .trn extension

    10)   Copy the both files to a location in B machine.

    11)   Restore .bak into a new DB using NORECOVERY option

    12)   Restore .trn into the previous DB using NORECOVERY option

    13)   Go to Machine A, open a new query

    14)   Take a look to the endpoints

    SELECT type_desc, port FROM sys.tcp_endpoints;
    SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;

     

    15)   Delete existing ones (DROP ENDPOINT [NAME])

    16)   Create a new endopoint

    CREATE ENDPOINT [Mirroring]
    AS TCP (LISTENER_PORT = 5023)
    FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)

     

    17)   Start it

    ALTER ENDPOINT [Mirroring]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5023)
    FOR database_mirroring (ROLE = PARTNER);
    GO

     

    18)   Do steps 13 to 17 in machine B.

    19)   Return to machine A. Query.

    20)   Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.

    ALTER DATABASE NAME
    SET PARTNER ='TCP://MACHINENAME:5023'

    21)   Repeat step 20 from MACHINE B pointing to MACHINE A

    22)   Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.

    23)   Lets test them: Add some new data in Machine A database.

    24)   Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.

    25)   Refresh the 2 databases in the UI you should see the new status and roles changed.

    26)   Open Machine B databse, the new data should be there.

     

    Cheers!

    Kelo

    • Proposed as answer by Ajay.G Monday, February 07, 2011 11:17 AM
    Friday, April 27, 2007 10:09 AM
  • Another one that has shorter steps (same idea)

    http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

    Friday, April 27, 2007 5:21 PM
  •         IMPORTANT!  To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machinename.domain.com.  Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)

     

          FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN)  ensure your 3 mssqlservices are running under NETWORK SERVICE account

    • Proposed as answer by masosoma Monday, September 20, 2010 11:40 AM
    Wednesday, May 02, 2007 3:39 PM
  • Hi all,

    I'd like to add something to this however it's beyond the scope of a basic mirror. When adding a Witness to your mirror to allow for automatic failover you may experience the error on the Principle and Mirror stating

    Database Mirroring login attempt by user 'Domain\SQLExpressMachineName$.' failed with error: 'Connection handshake failed. The login 'Domain\SQLExpressMachineName$.'
    does not have CONNECT permission on the endpoint.


    This is deceiving as the Mirroring Wizard creates endpoints on all 3 hosts. Basically the error above is stating that the Witness server does now have access to the Master database endpoints on the Principle and Mirror, therefore you'll need to add the host name of the Witness Server running SQL Server Express. This is done using the  GRANT  tsql command

    master
    go
    create login [Domain/SQLExpressMachineName$]
    go
    grant connect on endpoint::mirroring to
    [Domain/SQLExpressMachineName$]

    I had to do this even though all SQL Services were running as a network service.

    Cheers,
    Damian
    Friday, May 04, 2007 11:48 AM
  • I can confirm that my very persistent problems with mirroring was resolved by creating a certificate on the Primary linking it with the end point, then transfer the physical file to the target server and linking it with the other end point.

     

    Panos.

    Friday, May 25, 2007 3:57 PM
  • Thanks for the tip, struggled with that all day.  Soon as I added domain\machinename$ in the 3 blanks in the wizard it started working right away!  Must be necessary when running under the Network Services account since I suppose the connection originates from the machine account.

     

    Here's the raw SQL that's needed for creating the SQL login and grant:

     

    CREATE LOGIN [domain\machinename$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

    grant connect on endpoint::mirroring to [domain\machinename$]

    (assuming your endpoint is named "mirroring")

     

    Thanks for the help!

     

    • Proposed as answer by NebSnow Monday, August 16, 2010 7:51 PM
    Thursday, May 31, 2007 4:50 AM
  • I have the same problem ,but my machines are not in a domain,and I also don't want to login with authenication of certificate.who can help me? my machines work in a workgroup
    Friday, June 01, 2007 6:33 AM
  • This was the key.... thank you!  Been working on this all week.  If you are running in Network Services account you need to add domain\machinename$ (don't forget the $).

     

    Microsoft ???? are you reading this?????

     

    V/R Jim Johnston

    MCDBA, MCSD.NET

    Friday, September 21, 2007 1:04 PM
  • Part of the problem that everyone seems to have with mirroring is the fact that using Local System and Network Services accounts for SQL Server services is not recommended if you want SQL 2005 to communicate with other clients and servers.  It is recommended that one or more domain user accounts be setup.  I typically create three separate accounts as the security requirements for each service are different.  Look under preparing to install SQL Server 2005 in BOL for detailed info.
    Friday, September 21, 2007 5:02 PM
  • Bob, if these three machine is in workgroup instead of domain,which kinds of account is recommanded to use? 

    Tuesday, October 02, 2007 7:53 AM
  • I have the above problems too….

    After try and error and some readings, i fixed the problem……

    Hope this help everyone:

    http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx

    • Proposed as answer by ddimm Wednesday, January 06, 2010 9:24 PM
    Saturday, November 10, 2007 4:24 AM
  •  

    OMG!!!   2 freeking weeks I have spent on this and Kelo's comment about the NETWORK SERVICE account is what fixed it for me!!   Microsoft....where the heck is THAT in your Knowledge Base and BOL???!!!
    Wednesday, October 29, 2008 6:44 PM
  • Thank you Kelo for your clear jotted points I was able to configure the database Mirroring along with witness server configured.

    I used same domain service for all the servers(Principal, Mirror & Witness)
    I also followed this http://technet.microsoft.com/en-us/library/cc917680.aspx to configure it with the encryption disabled

    Regards

    Muthahar
    Tuesday, July 21, 2009 9:44 PM
  • Here is a shocker; I forgot how long I’ve been working on this mirroring error 1418 – two, maybe three months... I was considering migrating corporate database from Oracle 11 to SQL 2008 but since mirroring is choppy to setup I can’t justify purchasing SQL 2008. I even have a DVD on SQL 2008 and the instructor points out error 1418 and he can’t fix it either. Microsoft and SQL 2008 needs more structure.

    Wednesday, August 19, 2009 7:00 AM

  • Any solution for this 1418 problem ? 

    ( There are a lot of posting on this but none of them are specific to the soultion. Please post if any one comes across)
    Friday, January 01, 2010 5:26 AM
  • 1. Make sure that you are using the same username and password to run the SQL Server service on both machines. - It is because SQL Server connect to each other with NT Auth. that using same username and password can prevent a lot of problems. - Using Configuration Manager to set the user for running the service. 2. Make sure that the username for running SQL Server service got all the rights. - Use "Surface area configuration" to assign the rights to the current user. 3. Make sure that you can connect to both machines with Windows Auth - To check it, just open SQLManagement Studio, connect to both server with Windows Auth - Update: I find out that SQL Server 2005 talk with each other with "Named Pipes" connection. You MUST enable "Named Pipes" on all servers! Check the following link for more details: http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx
    Sunday, January 03, 2010 10:49 AM
  • I had this problem as well.   After double and triple checking the end points were setup, and the firewall had the proper rules and the permissions were correct.  Compared everything to another instance of SQL that was mirroring.  

      I noticed that I hadn't restored the database on server $2 (the mirror) properly.   Make sure you use the "NO RECOVERY" option.   And the database should appear in the "restoring" state in the GUI on the mirror server. 

    For some reason because I did a full restore With Recovery the first time to server #2  when i tried to "Start Mirroring" it would give me the error:

    Error: 1418 - Microsoft SQL Server - The server network address "SERVER #1"  can not be reached or does not exist. Check the network address name and reissue the command. The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.

    Which was 100% mis-leading and incorrect (especially since Server 1 was the server reporting the errror that it couldn't contact itself.

    Once I did the correct type of restore on Server #2  I was able to easily configure mirroring following the wizard in Management Studio on Server #1.

    TLDR:  Be Sure you used the "Restore with NO RECOVERY"  option / command.  If the other troubleshooting steps didn't resolve the issue.
    • Proposed as answer by ddimm Wednesday, January 06, 2010 9:24 PM
    Wednesday, January 06, 2010 8:54 PM
  • Hi

    I think before you setup the database mirroring between the workgroup server, you need to read the following article first.
    http://msdn.microsoft.com/en-us/library/ms190941.aspx
    http://msdn.microsoft.com/en-us/library/ms189852.aspx

    Thanks, Leks
    Wednesday, January 06, 2010 9:10 PM
    Answerer
  • THANK YOU.  I searched for days and tried many things but this fixed the issue I was having. 
    Monday, August 16, 2010 7:53 PM
  • thanx a lot.

     

    I also had to make the Domain account a local admin on the SQL Instance for the mirroring to work.

    Monday, September 20, 2010 12:42 PM
  • Thanks Kelo,

    In sql server 2008 database mirroring the option : Encrypt data sent through this end point.

    When I have disable it then I am able to do the mirroing but Now I want to know the consequences of disabling this?


    "SQLSERVER DBA" "INDIA"
    Monday, February 07, 2011 11:17 AM
  • Dear All,

    Even i followed above step... all the steps working fine but got error while executing step 20 i.e

    ALTER DATABASE NAME
    SET PARTNER ='TCP://MACHINENAME:5023'
    every thing

    from above post.

     

    FYI,

    I am using 2 systems under same domain for DB mirroring.

    Principal sys. -  OS - Windows server 2008 R2 standard & SQL server 2008 R2.

    Mirror sys. - OS - Windows XP sp3 & SQL server 2008 R2.

    Following are the check list I have done :

    1.    mssqlservices are running under NETWORK SERVICE account

    2.    Filestream for TSQL is not enabled

    3.    firewalls OFF.

    4.    I am a local admin in both machines & also is a sysadmin user in both sqlservers.

    5.    I am using Windows authentication in both system.

    6.     DB backup with full recovery model & DB restore to a mirror database with WITH NORECOVERY option

    7.     Since endpoint is already present by Mirroring name so i have not deleted it instead  i have used alter command as follows.

    alter ENDPOINT [Mirroring]

    STATE=STARTED

    AS TCP (LISTENER_PORT = 7023, LISTENER_IP = ALL)

    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

    8.    Same in mirror too only Listener port is different. i.e 5022      

    9.    When I issue this command from mirror sys. its working fine

    ALTER DATABASE RajDB

    SET PARTNER ='TCP://PC32.MYDOMAIN.SOFT:7023'

    but from principal sys.  

    ALTER DATABASE RajDB

    SET PARTNER ='TCP://U15349479.MYDOMAIN.SOFT:5022'

    then its throwing an error -- L

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://U15349479.MYDOMAIN.SOFT: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.

    In error log - Database mirroring has been terminated for database 'RajDB'. This is an informational message only. No user action is required.

     

    Please help me to completing this task, working on this from past 1 month. Even I have gone though all the post but no solution.

    I am looking ahead of your valuable supports.

    Thanks,

    Vincent


    Thanks & regards, Vincent
    Wednesday, February 16, 2011 2:18 PM
  • The server network address can not be reached or does not exist. Check the network address name and reissue the command. The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.

    Saturday, February 19, 2011 11:54 AM
  • Hi Vincent. You should set in GUI setup Db Mirror (in section Service Account) one name of servis who use SQL Server Service (In my Server "DOMAIN\sql_server_service" - I set in Primary_Server, Secondary_Server, and Witness as service sql account) in 3 field, the same account... I had the same error!!! Michael...
    Saturday, March 05, 2011 12:43 PM
  • Hello,

    I've performed a test on "Database Mirroring" on both test machine on test database named "Mirror" .

    Server 1 : MSSQL.local

    Server 2 : Backup.local

    I've added IPs on both servers host file so that they can resolve the servers name to the corresponding IPs among each others.

    I've tested on the setup and found error as below when started "Mirroring"

     

    ===

    Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://backup.local:7024'.

    Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://backup.local:7024'.

    ===

    Both servers firewall are turned off and I'm able to telnet to port 7024 between both servers.

    Kindly advise.

     

    Rgds,

    -Teh-

    Wednesday, June 15, 2011 2:54 AM
  • Hi Teh

    I dont think i quite understand your request, ""I've performed a test on "Database Mirroring" on both test machine on test database named "Mirror"" .

    1. Are your machines on the same domain?

    2. Do you mean to say you tried to do the mirroring from both machines, first a, pointing to b, then b pointing to a?

    Can you make your scenario more clearer

    Wednesday, June 22, 2011 4:01 PM
  • Hi Everybody

    Just rake a look at URL below.

    Sure it works

    JUST REMEMBER TO create the mirror endpoint first

    http://www.sqlservercentral.com/articles/Database+Mirroring/72009/

    hope it was helpful

    Sunday, September 04, 2011 11:09 AM
  • Hi Just Try Giving IP Number  Instead Of  U15349479.MYDOMAIN.SOFT 

    For Example

     

    ALTER DATABASE RajDB
    
    SET PARTNER ='TCP://192.168.1.1:5022'
    


     


    Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Sunday, September 04, 2011 2:24 PM
  • Just remember that error could be also that your encryption is different between partners, otherwise flip the Recovery model before using the backups on the mirror partner. Please see the full details to rid yourself of this error (which trapped me twice too!! dammit):
    http://www.sqlservercentral.com/blogs/hugo/archive/2009/11/12/troubleshooting-sql-server-database-mirroring-problems-with-best-practices-requirements-and-the-64-bit-benefit.aspx
    Hugo Shebbeare
    Montreal, Canada 

    SQL Server MVP (2010) 

    Monday, October 31, 2011 7:33 PM
  • Read my answer on this page already, or go though these validation steps - sounds like you have miss-matched the security by accident: http://msdn.microsoft.com/en-us/library/ms189127.aspx
    Monday, October 31, 2011 7:36 PM
  • Yes, that works too, but sort of defeats the purpose of using DNS (a host name is easier to work with, no?).  
    If your server moves physically by IP, then you have a broken mirror - which may not be an issue if your servers barley ever change IPs, but just be aware that is a consequence of using IP address workaround. 
    Monday, October 31, 2011 7:39 PM
  • The correct answer, than you sir.
    Wednesday, February 06, 2013 1:16 PM