SQL SERVER – The server network address “TCP://SQLServer:5023″ 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)

While doing SQL Mirroring, we receive the following as the most common error:

The server network address “TCP://SQLServer:5023″ cannot 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)

The solution to the above problem is very simple and as follows.

Fix/WorkAround/Solution: Try all the suggestions one by one.

Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).

Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).

Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023″.

Suggestion 4: Make sure your firewall is turned off.

Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.

Suggestion 6: Try the following command as one of the last options.

GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

Suggestion 7: Delete the end points and recreate them.

If any of above solutions does not fix your problem, do leave comment here. Based on the comment, I will update this article with additional suggestions.

Please note that some of the above suggestions can be security threat to your system. Please use them responsibly and review your system with security expert in your company.

Reference: Pinal Dave (http://blog.SQLAuthority.com), Many thanks to Solid Quality Mentors (http://www.solidq.com) for their valuable suggestions.

About these ads

106 thoughts on “SQL SERVER – The server network address “TCP://SQLServer:5023″ 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)

  1. these are really helpful and pretty good solutions, I remember one of the case right now, if your both SQL Servers are not in one domain rather than both are in same workgroup and this error comes up and doesn’t resolve with any of the above suggestions, you should go for mirroring with certificate.

    • HI Panal

      After following al th above mentioned steps also i am getting same error as above that was unable to find mirror network address and i am doing this by using certificates

  2. Sir

    i want to create mirroring to My DR site. i have a doubt mirroring will support with different subnet. why means my primay site having the network of 10.147.160.0 /8 and the DR sit having the network of 10.147.161.0 /24. both the locations are connected with MPLS VPN.

    i tried but i get the error of error 1418

    Note : the same i tried with same subnet, working fine.

    please help us.

  3. Hi Nandha,

    Pinal explained very clearly to resolved the error 1418 comes by Mirroring configuration. Some times might be caused to different subnet, but most of time missing any of Pinal’s steps. I had already facing the same problem but finally i resolved that error.

    But finally we have need to some discussion on the same error.

  4. Pingback: SQL SERVER – Mirroring Configured Without Domain – The server network address TCP://SQLServerName:5023 can not be reached or does not exist Journey to SQL Authority with Pinal Dave

    • Disable the firewall services by going to Run > services.msc (hit ‘Enter’) and disabling/stopping windows Firewall.

  5. I’ve done all the steps above…

    I have specific service accounts on all 3 machines (principal, mirror, witness) and the services are running under these accounts with admin rights.

    I’m unable to mirror, I keep getting that it cannot connect to the mirror stating that. Does not exsist or cannot be reached.

    I can telnet to the machine on port 5022 and windows firewalls are disabled…

    They not on a domain and are just part of a workgroup.

    I deleted and recreated the mirrors…. Any other ideas?

  6. I tried sql mirroing couple of times but did not suceed. I am using SQL 2008 R2 under virtual environment. I created three VMS – PrincipalSQL, MirroredSQL and WitnessSQL. The services are using NT Authority. I backuped up database from principal and restored it on mirroredSQL with norecovervy. (Now database on mirrored is showing Restoing….). Then I tried to mirroing using SSMS. But everytime I am getting error about FQDN so I used following T-SQL –

    ALTER DATABASE ptQueueDB SET PARTNER = ‘tcp://MIRROREDSQL:5022′

    I got following error –

    Msg 1418, Level 16, State 1, Line 1
    The server network address “tcp://MIRROREDSQL: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.

    I checked ports are open on all machines and Telnet was also successful.

    Please help me…

  7. Two other tips
    1) Attempt to use the same SQL service accounts and make sure that account is listed as a SysAdmin on the database server instance.

    I would put this before attempting to grant all step #6.

    2) For #3, drop the endpoints and attempt to telnet to the ports first; if you connect, choose another port; reference assignments from the IANA list.

    • Tip no 1 saved my day! I tried desperately almost everything until I found this tip about service account being sysadmin on the sql instance. Thanks!

    • This site (this post in particular) saved my day about 4 times in two different projects on several diferent enviroments – and now this tip is added to my ‘Top 10 most important things to always check’ :) good one!

  8. This is a great guide.

    In my case, the comment from Ken ( make sure that SQL service account is listed as a Sysadmin) solved my problem.

    Thanks to Ken and Pinaldave.

  9. I have a principal and mirror, in 2005. I set up the mirror site in recovery mode. I keep seeing references about restoring the log file to the mirror, after the database is restored in recovery mode. I cannot do that in MS, and it would probably fail in t-sql. Also, I have endpoints on both databases. When I do a set partner, on the mirror it says it is already set up for mirroring. It says the endpoint is started.

    SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints says partner and started for both.

    SELECT type_desc, port FROM sys.tcp_endpoints says
    DATABASE_MIRRORING and 5022 form both

    There is no firewall involved, the telnet works both ways. I am at a lose. Can you suggest something else.
    The sql service service is set to local. Should I change it, and if so, to what. Also, both accounts are local, not domain accounts. Any light you could shed on this would
    be appreciated. I enjoy your knowledge sharing.

  10. I resolved my problem with the undocumented feature, (requirement), of having to use a domain user as the owner of the sql service…service. Thank you for your patience

    • This fixed it for me too!!

      Well it’s still not working but I have a new error message related to incorrect backup restore. But it’s progress for sure.

      Thanks so much!!

    • After trying all above solutions and tired of pulling my hair…. this one worked for me at last.
      Thanks for sharing.

  11. The first tip of Ken’s solved my problem. I’ve tried for several days and this annoying error message kept coming out.

    After checking the service account on both partners and realizing that the principal used Administrator and the mirror used NT AUTHORITY\NetworkService to access, I changed the SQL Server service account of the mirror to Administrator and tried the following step:

    ———
    ALTER DATABASE DB_NAME
    SET PARTNER =
    ‘TCP://MY_DOMAIN:PORT_NUM’
    GO
    ———

    And it worked like a charm!

    Thank you so much, Ken.

  12. hi,

    i’ve tried the following:
    > restored the principal backup on mirror server with using NORECOVERY
    > restored the transaction log on mirror server
    > created endpoints on each server to use port 7024 with no encryption
    > opened windows firewall ports to 7024 on each server; set it to allow all incoming and outgoing connections
    > checked telnet to port on both servers. for some reason, it connects and then disconnects by itself
    > deleted and recreated the endpoints on both
    > on both servers i’ve created an account ‘mirror’ with permissions to the required DB.
    > both servers are on the same domain.
    > able to ping each server from the other
    > windows firewall is not logging any entries for blocked connections

    still getting error 1418. any pointers? thank you.

  13. same as above post –
    hi,

    i’ve tried the following:
    > restored the principal backup on mirror server with using NORECOVERY
    > restored the transaction log on mirror server
    > created endpoints on each server to use port 7024 with no encryption
    > opened windows firewall ports to 7024 on each server; set it to allow all incoming and outgoing connections
    > checked telnet to port on both servers. for some reason, it connects and then disconnects by itself
    > deleted and recreated the endpoints on both
    > on both servers i’ve created an account ‘mirror’ with permissions to the required DB.
    > both servers are on the same domain.
    > able to ping each server from the other
    > windows firewall is not logging any entries for blocked connections

    still getting error 1418. any pointers? thank you.

  14. Hi,
    If you don’t succeed with above steps, try this:

    1. Make sure your sql service is running under the same domain account on all of your sql servers in the mirroring.
    2. Make your sql service account sysadmin on your sql servers.
    3. Make your sql service account local admin on your servers.
    4. Login on our server using your sql service account.
    5. Make your mirroring according to the step byt step guides availible.

    This worked for me after I tried all other suggestions to error 1418.

    After you have set up your mirroring you can remove your sql service account as sysadmin and local admin. You can’t remove it from sql server though because it is the owner of the endpoint, but I guess you can change this as well.

    Hope this helps!

  15. Hi,
    I have been working on installing Mirroring on SQL server 2008, and get the same 1418 error.
    I did all teh checks mentioned above and also created the SQL login which is a local user and runs the SQL server services.
    The mirror is set up on different servers with awitness instance.

    Any ideas? what else shd i check?

  16. My principle database is in domain controller server and mirror database server is in same domain. i am trying to do mirroring but it gives me error “The server network address “TCP://maxserver-2:5023″ can not be reached or does not exist. Check the network address name and reissue the command.” i have tried everything but not success

    Please help me to configure mirroring

  17. Hi,

    I tried with all above steps. But i got the same issue..Can any help me please..!!

    Error: “The server network address “TCP://servername: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)”

    Thanks,
    Laxman

  18. This is a great guide.

    In my case, the comment (Make sure that SQL service account is listed as a “Administrator” – Local account) solved my problem.

    Thanks to everyone.

  19. In my case, I got the same error after pressing the ‘murror’ button. Tried al tips but nothing helped. Found the following error in the event viewer:

    Database Mirroring login attempt failed with error: ‘Connection handshake failed. There is no compatible encryption algorithm. State 22.’. [CLIENT: xxx]

    When I executed the following query on both, the principal and the mirror:

    select * from sys.database_mirroring_endpoints

    I found out that in the column “encryption_algorithm_desc”, the value for the principal was “RC4″ and the value for the mirror was “NONE”.

    Guess these values need to be the same! Good luck all.

  20. hi guys
    From my experience add all services particularly mirror and principal sql instances under
    domain account and with sysadmin rights in sql server. Add domain user account as part of local administrator group in each instance machine

  21. I had this exact problem. Tried everything you list here and read many guides on the internet and it still did not work. Nothing helped. I did all copying, backing up, etc with the “Microsoft SQL Server Management Studio” and I thought maybe I had to do everything with SQL Transact statements. However, as a last thing to try in “Microsoft SQL Server Management Studio” I tried to connect to the server instances with Authentication set to “SQL Server Authentication” and used the sa account instead of using the default “Windows Authentication”. This solved my problem. Suddenly it just worked.

  22. I did all the recommendations mentioned before, and I was getting the same error, I made a test creating the mirror using the same server with different instances and works. The principal sql server version, that I was using, was 10.50.1600.1 and the mirror was 10.50.2500.0, I’m not sure if that was a constraint that I don’t take in consideration

  23. I have the same network connection problem. I fix it doing the following:

    1. In Management studio, I connect the Database engine Name : IP ADDRESS
    and Authentication: SQL Server Authentication, User name: sa with sa password.

    2. Then I started mirroring. I left blank the Service account for the Principal and Mirror.

    3. After completing, when the system asked to Start mirroring, I choose No.

    4. Then I changed Server Network Adress Places as: Principal as it is,
    but Mirror: ‘tcp://[ip address]:5022′

    Amazingly, the system started to synchronize.

    By the way I choose ‘Hight Safety’ Operating mode. i.e. no witness.

    If it helpful, just share it.

  24. In witness server:

    1. Search file ‘hosts’ , it is normally found in %system32%/drivers/etc
    2. Open it and Go at bottom of the file
    3. Add the following:
    [Principal server IP address] [Your Principal servername]
    [Mirror server IP address] [Your Mirror servername]
    4. and Start mirroring again.

    You can do the above in Principal and Mirror servers.

  25. I am about to go crazy here guys. I have tried EVERY suggestion on this page, but I still receive the 1418 error. Any other suggestions would be greatly appreciated. I set this up a couple weeks ago just to test and everything worked fine. I rebuilt the mirror server due to adding additional hard drives and now I can’t seem to get past this error.

    Thanks in advance.

  26. I tried the following code but am getting same error can any body help me

    USE master
    GO
    ALTER DATABASE MesHun_DBArchival_Original SET PARTNER = ‘TCP://wfist-eorch:9999′;
    GO

    Error:-
    The server network address “TCP://wfist-eorch:9999″ 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.

  27. After dealing with this problem for a long, long time, I solved this problem by using the same approach as Tony mentioned in his post. (Changing the ComputerName.Domain:PortNumber format to IpNumber:PortNumber)

    Remember to do this on BOTH Principal and Mirror fields (and Witness if you’re using one).

  28. Hi guys please any body can help me out i am facing the same error 1418.

    The steps which I have followed taken full backup along tran log and restored with no recover option and I have added administrator accounts in both primary and mirror.when I am starting I am getting same error..Please help me out

  29. Hi guys please any body can help me out i am facing the same error 1418.

    The steps which I have followed taken full backup along tran log and restored with no recover option and I have added administrator accounts in both primary and mirror.when I am starting I am getting same error..Please help me out

    Alter failed for database ‘RDCCI’

    The server network address ”TCP://RDCCI-SQLAPPS2.kdcci.org.5022” can not be reached or does not exist. check the network address name that ports for local and remote endpoints are operational (micorosoft sql server error :1418

  30. You can also, from the command line, run “netstat -a” and determine if SQL Server is listening on your configured EndPoint port as another way of looking at it. Sometimes that’s helpful.

  31. Steps 6 does not work. All is not a valid parameter. I even tried my login and it says i cannot grant permissions to myself. how ridiculous, especially since my login is an SQL Admin…

    Not sure how to do step 7. I tried re running the security wizard several times but no change.

    One other big question is, what should be put into the “service Accounts” part fo the wizard? I’m using my domain account for access, but I want to have it run off local accounts. I’ve tried leaving these all blank and tried putting my domain account in there, but no difference! I’ve been using windows auth (ie my domain account) for all the connect parts of the wizard.

    Desperately yours!

  32. what would be really helpful here is an idiots guide on how to check each option. Iv’ve tried everything on this page several times but still have a 1418 error :-)

    • One thing not mentioned in step 3 is, you need to check your Telnet connection BOTH WAYS. From your Principle to Mirror server and from your Mirror back to your Principle. This was my issue. Wish they could put more smarts into the security wizard to check this!

  33. 1.Make sure that all the service account is same on all the servers that involve in mirroring session.

    >>The service account means sql service account?
    >>Administrative tools->services->sql server ->Log on tab?
    >>if so, both principal and mirror server, I have set up as below

    >>Log on as
    >>This account =Network Service

    >>In both SQL server, I have “NT Authority\Network Service” as sysadmin from server >>role.

    >>Is this OK for setup??

    2 .Make sure that the mirror database is ready for mirroring.
    (First restore full backup with no recovery and after that transaction log backup with no recovery on the going mirror DB)

    >>Yes, I did this. The mirror server, the mirror database is xxxx ( In Recovery)

    >> is this OK?

    3. Make sure that the name and port of the mirror server instance are correct.

    >>ALTER DATABASE Mirror1
    >>SET PARTNER =’TCP://domain_name:1524′
    >>GO

    >.returned with the following error

    >>Msg 1418, Level 16, State 1, Line 3
    >>The server network address “TCP://xx.com:1524″ 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.

    >>SELECT SERVERPROPERTY(‘ProcessID’)

    >>don’t work

    4. Make sure that the destination mirror server instance is not behind a firewall.

    >>I turned off the firewall for both servers
    >>Control Panel-> Windows Firewall->Turn Windows Firewall on or off->Turn off windows >>firewall

    >>Correct??

    5. Make sure that the principal server instance is not behind a firewall.

    >>I did the same as 4 above

    6. Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. If either endpoint is not started, execute an ALTER ENDPOINT statement to start it.

    >> both should start. I use the following query in principal and mirror

    >>SELECT state_desc
    >>FROM sys.database_mirroring_endpoints

    >> both return ” STARTED”

    Am I Correct??

    7. Make sure that the principal server instance is listening on the port assigned to its database mirroring endpoint and that and the mirror server instance is listening on its port. For more information, see “Verifying Port Availability,” later in this topic. If a partner is not listening on its assigned port, modify the database mirroring endpoint to listen on a different port.

    >> first, I tried automatic pickup pornt by ms sql
    >> principal: domain_name: 5023
    >> mirroe: domain_name: 5022
    >>don’t work,
    >>then I use the following commands in DOS
    >>C:\>netstat -a | find “LISTENING”
    >>C:\>netstat -ano
    >> to get available port and process
    >>then, I dump into

    >>ALTER DATABASE Mirror1
    >>SET PARTNER =’TCP://ip:1524′
    >>GO
    >>don’t work
    >>Msg 1418, Level 16, State 1, Line 3
    >>The server network address “TCP://xx.com:1524″ 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.

    >>Finally, I use
    >>SELECT SERVERPROPERTY(‘ProcessID’)
    >>to get port then use
    >>ALTER DATABASE Mirror1
    >>SET PARTNER =’TCP://ip:1524′
    >>GO
    >>don’t work with the following error
    >>Msg 1418, Level 16, State 1, Line 3
    >>The server network address “TCP://xx.com:1524″ 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.

    >>Did I do the right?

    >>What other tool you can tell me to fix this issue?

  34. Hello, I was reading this post because I had trouble like this, implementing my mirroring. I had verified all of the points listed by our excellent Pinal.
    But all was fine except the error message.

    My context :
    1 Domain : dom.ste.org
    0 Witness
    1 Cluster Windows 2008 R2 : Winclu01
    2 Nodes : NDwin01, NDwin02
    2 Clusters SQL Server 2005 SP4 : Sqlclu01, Sqlclu02
    2 SQL Clusters Domaine\Account
    2 Endpoints Mirroring01 on port 5022, Mirroring02 on port 5023
    2 Instances SQL Server : Inst01, Inst02
    1 Database DB99
    Mirroring Safety Full

    I then read all the nice comments and I have seen Tony who speak about host file.
    I didn’t want to do this because I was thinking about AD and DNS which seemed to work well.
    But after another battle, short this time! I decide to register my 2 SQL clusters in the two host files :
    10.122.28.53 Sqlclu01
    10.122.28.55 Sqlclu02
    One minute later it was done.
    I was running the last statement, connected to principal Sqlclu01 :
    ALTER DATABASE DB99 SET PARTNER=’TCP://Sqlclu02.dom.ste.org:5023′
    Now my mirror is up and running !!!

    A big Thank You to Pinal, which always raises questions of great interest.
    And a big Thank You to Tony for his full advice narrowly.

  35. Hello Pinal,

    I need help here I am trying to mirror two databses here on different servers.

    For mirroring I have used certificates as prescribed in http://msdn.microsoft.com/en-us/library/ms191140.aspx

    When I run alter statement on principal database it throws 1418 error.

    I have tried your suggestions partly.

    The things which should be noted are

    1) My principal server is enterprise edition but my mirror server is standard edition as a result wizard didnot allow me to configure but I am trying this with t-sql which some of the guys told it will work.
    2) I didnot turnoff firewalls as one is my production server, so I created inbound and outbound rules on both servers on firewall for port 7022.
    3) Telnet on my principal server which is production is not working i.e I tried Telnet in command prompt but it says invalid command.

    Please help me out of this situation.

  36. There’s another fundamental requirement which, if you do not do, results in this message. The SQL Server (instancename) service MUST be set to Log On As an administrative user. If the servers are on a domain, then it’s a good idea for this to be an administrative domain user.

  37. I’m setting up mirroring on my home network for practice & MS Exam preparation. I am mirroring from a Server2008 VM on another computer to an Evaluation edition instance on Windows 7. There is no Active Directory domain. What seemed to work for me (after following clues in the SQL Error Log) was to add the SQL service accounts to Logins on the Servers themselves. I didn’t think you needed to do this.

  38. Suggestion 7 can help if endpoints on both servers are using different ecryptions. If not specified, manually created endpoints use ENCRYPTION = DISABLED. Management studio creates endpoints with REQUIRE RC4 by default.

    So script both endpoints in SSMS (drop+create) and compare ENCRYPTION option. Set both to DISABLED and apply.

  39. dear sir i am having one quiery
    I am having two SQL servers 2008 at 2 diff location and connected with Lease line , my quiery is that if i make mirror using both server and suppose my lease line got dissconnected i want to use bot servers from their own location’s means server A will be used only by users who seat at loction A and server B database will used BY only location B users
    is it possible means in mirrion one detabase is inaccessable it wnat to use both database in lease line failure condition

    please help me to solve this issue

  40. 1. Check Firwall of System should not block SQL Server port.

    2. SQL Server Configuration Manager –> Service and Application –> SQL Server 2005 Configuration Manager –> Network Configuration–>Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.

    3.SQL Server Configuration Manager –> Service and Application –> SQL Server 2005 Configuration Manager –> Client Configuration–Enable TCP/IP protocol.

  41. I am stuck with error 1456 and I finally realize and found out after looking the post by Tony. The DNS-entries are not updated the changes of host name or IP address.
    By running gpupdate on DNS server and the others SQL Servers (Principle, Mirror and Witness) could solve the problem without editing the hosts file.

  42. Hi,
    I tried all the options. First of all my firewall is off in Primary, mirroring and witness SQL server machines. Still I am getting the error that TCP://servicename:port is not acceible.

    • VV,

      If you could telnet to 5022 port : telnet ipaddress 5022 is to confirm the firewall is not blocking. Tony is posted and it is the solution for error 1456 – if the DNS is not translated the host name properly.
      Please edit the host file from witness server, and put ip address + host name for Principle, Mirror. That’s it !
      Try from command line : ipconfig /flushdns

  43. At last I made it. The error 1418 disappeared. My configuration: two SQL servers without domain. This is my solution:
    1. Create the same user account (e.g. SQL_mirr) with the same password and with standard priviledges on both Windows servers
    2. In the system firewall allow communication via ports 1433 and 5022 (default SQL mirroring) on both servers
    3. Set the SQL_mirr account as “Log on” at service MSSQLSERVER properties
    4. Add SQL_mirr account with sysadmin priviledges on both MS SQL servers
    The rest as standard: backup database with transactional log from principal server, restore backups to mirror server with RESTORE WITH NORECOVERY option and set mirroring via wizard or TransactSQL. It works with IP, FQDN, without FQDN in TCP://partner_server_address:5022 syntax.
    Good luck! :)

  44. For all of you poor saps who got all the way down here and it’s STILL not working…I was in your boat. The fact of the matter was, Sql never started listening on my configured endpoint on the mirror partner.
    Facts:
    1. I tried multiple ports.
    2. I turned OFF all firewalls.
    3. I could NOT Telnet to my EndPoint port from any other machine – but COULD telnet to the Primary and Witness EndPoints from the problem machine.
    4. Other machines COULD telnet to the default SQL 1433 port on the problem machine.

    The Primary DB simply could NOT see it’s mirror partner’s EndPoint port – so I could *never* complete the setup.

    The sad fact was, that after 7 hours of exhausting every possibility I could think of from hardware, to software – I gave up and UNINSTALLED and REINSTALLED Sql Server on the problem machine. The EndPoint started listening on the first try. Problem solved.

    The takeway? Sql Server got itself munged up pretty well and wouldn’t start listening on any configured endpoint ports. I may never know why. And FYI – a SQl Repair didn’t fix it. It needed the nuclear option.

    …Hope no one else gets to this point – but, it happens. And this fixed it.

  45. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

  46. My principal and mirror SQL server were running in AWS EC2 cloud (virtual environment similar to VMWARE) – we have a domain setup in there. I was getting same FQDN error. I resolved it by trying to running SQL Server services on both principal and mirror servers using a domain account (for test I used my own domain account) and then specifying the same in Mirroring Configuration step where it asks for accounts on both servers if domain account account is setup. Try it and let me know.

  47. Hello All,
    I have got to the end of this post and still do not have a resolution. Here is my setup.3 Servers on AWS EC2 without a domain (one of them is a witness)
    I have tried every thing that people have suggested here. Always end up with the 1418 error. The services are running on the same local account on both servers. The account has sysadmin access to the SQL server.
    I have been at it not for about 4 days now. I ‘accidentally’ go it to work once ad had to expand the root drive on the servers. Now, it has stopped working.

    I would appreciate any additional things I can try short of re-installing the SQL server. :-)

    Thanks in advance.
    KV

    • KV,
      Suggest you try steps 1&2 again given above by Mr Pinal creating a dummy ‘Test’ SQL database on principal server instance. Backup full & backup transaction log of this dummy db (without data like tables, stored procedures etc so size will be very small of both backups) to rule out full backup & trans log backup related issues with setp. Certainly, most common problem in mirroring setup between 2 servers!

      If setup still fails then this is network related issue so focus on suggestion 3 onwards given by Mr Pinal.

      • Thanks, Crougar, for your response
        I tried it and it still fails. I think it might be a SecurityGroup issue.
        Trying that.

        • Right, if with dummy dbs on both principal & mirror with no data in them fails to get mirror setup – it has be to do with network.
          You may want to open up security groups in AWS EC2 instances for 2way comms between all 3 instances – allow all tcp,all udp,all icmp comms between principal-mirror,principal-witness,mirror-witness if not then least 5022,5023 if using those alone.
          Use both internal IP and elastic IP in SG configuration and test.

          • We did try the security groups with the help of ops but that did not end up fixing it. I have detailed below how I finally fixed it. Hopefully it will add t othe knowledge base.

            Thanks Pinal and everyone else. This is a valuable resource.

  48. In my case, SQL Service pack were different in both the servers. When that was taken care of, Mirroring worked like a charm.

  49. Hello ,

    I am getting same error message , I m trying to configure in workgroup environment , I tried with certificate but still getting same error

    Thanks
    Moin

  50. Hello,
    After struggling with the issue for several days, I was able to fix it as follows:
    The key piece seemed to be that when you create an AWS instance, it automatically creates a DNS for the instance that looks like this IP-xxyyzzaa where xx,yy,zz,aa are the hex values for the ip v4 address when you do an ipconfig on the machine. So, on the primary, mirror and witness I changed the computer name to match the scheme. To verify that it worked, I did an nslookup IP-xxyyzzaa. It should return back the ip address of the machine and also the name IP-xxyyzzaa.ec2.internal. In the Configure Security Wizard, I used the name IP-xxyyzzaa.ec2.internal to connect to each machine. At the end of it Start Mittoring worked like a charm. To make sure that this was not a fluke, I repeated the procedure on 3 new machines and it worked without a hitch.

    One thing to keep in mind is that it is good to have an elastic ip tied to these machines so the ip address does not change. Right now I do not have Elastic ip tied to these, so I will have to reconfigure mirroring when that happens.

    Hope this helps. Please post questions, comments as you see fit. I will be checking back periodically.

    Thanks
    KV

  51. My solution was even more straight forward (kicking myself as I type) you must use the service account that the sql database engine us running under for database mirroring “Service Account” configuration page. I was using a service account that was created and dedicated for database mirroring with local admin permission on both servers and a member of the sysadmin group on both servers. I’m not sure why that wouldn’t work because this dedicated account was setup with permission to access the endpoint. Interesting.

    Anyway changed the credentials to the service account that the database engine was using and all was well.

  52. My solution was… to remove duplicate endpoints. My starting situation was:
    – instances server1\ABC and server2\ABC both with endpoints on port N, mirroring not enabled, but endpoints enabled
    – new instances server1\XYZ and server2\XYZ with new endpoints, also on port N, also enabled – I was able to point server2\XYZ (mirror) to server1\XYZ (principal), but when trying reverse, it failed.
    I checked about 20 things, including telnet, but then found that endpoints on ABC instances were already enabled – I just had to drop them to make mirror for XYZ work. (ABC will be decommissioned anyway – if that’s not the case for you, just use a different port)
    Good luck!

  53. Hi Pinal,

    I have performed all your 7 step, you mentioned in this post however i am still getting below error message.

    – I am performing mirroring without Witness Server.
    – without Domain
    – 5022 Port used on both Sides
    – With no service account

    TITLE: Database Properties
    ——————————

    An error occurred while starting mirroring.

    ——————————
    ADDITIONAL INFORMATION:

    Alter failed for Database ‘TESTDB’. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

    ——————————

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    The server network address “TCP://WIN-KJTSN3JJT52: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)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

    Optimistic response anticipated.

    Regards,
    Rigel Networks

  54. Hi, I get an error in the mirror server event logs.. I am using Windows authentication. I have a domain user that has access to both servers and all DBs. I looked at it and it’s something like this:

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

  55. Hi,I have restore the database on mirror server with Restore with Non recovery.It gives proper successful message.But still showing Restoring progress bar.

  56. Hi,

    We are getting below error while trying to connect with Primary server to Mirror Server:
    The server network address “TCP://<>:7022″ 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.

    The query used is :

    DECLARE @partner AS VARCHAR(60)
    DECLARE @query AS VARCHAR(MAX)
    DECLARE @dbName VARCHAR(30)

    ———————————————-
    SET @partner = ‘TCP://server:7022′;
    SET @dbName = ‘AutoDB_DBmirroring';
    ———————————————-

    SET @query = ‘ALTER DATABASE ‘ + @dbName +
    ‘ SET PARTNER = ”’+ @partner +”';’ ;
    EXEC (@query);

    Although, we have tried all above steps already, DB mirroring is getting configured in mirror instance, where we are getting the above error when running in primary server.

    Any help is appreciated.

  57. Same problem , error 1418. Did everything suggested above.

    In hindsight it was obvious. When completing the MIRRORING > CONFIGURE SECURITY WIZARD you get a page explaining the results. Looking at SQL1 and SQL2 nodes this line was the key:

    On the principal server instance, SQL1
    Listener Port: 5022
    Encryption: **Yes**

    On the mirror server instance, SQL2
    Listener Port: 5022
    Encryption: **No**
    Solution. After finishing the wizard on all SQL nodes execute:

    —————————————————–
    drop endpoint Mirroring
    go

    CREATE ENDPOINT Mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022 )
    FOR DATABASE_MIRRORING
    (ENCRYPTION = DISABLED,ROLE=ALL)
    GO
    —————————————————–

    Hit start mirroring, problem solved.

    Hope that helps someone.
    Scott

  58. Hello Everyone, this is an old post but just wanted to share the below info.. In case where you have 2 SQL instances on same Host and if you are attempting to configure mirroring on second instance to a different host it will try to communicate on same default 5022 port so in order for the second instance to work and get rid of 1418 error.Please modify the port for the end point

    alter ENDPOINT Mirroring — Enter the endpoint name
    –STATE = STARTED
    AS TCP ( LISTENER_PORT = 5023 ) — pick any available port
    –FOR DATABASE_MIRRORING (ROLE=PARTNER);
    GO

  59. nothing but below works –
    Suggestion 7: Delete the end points and recreate them

    IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = ‘End_Mirroring’)
    DROP ENDPOINT End_Mirroring;
    IF EXISTS ( SELECT * FROM sys.certificates WHERE name = ‘PRIM_cert’ )
    DROP CERTIFICATE PRIM_cert;
    IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE [name] LIKE ‘%DatabaseMasterKey%’)
    DROP MASTER KEY”;

  60. The SQL Server service needs to be running under a domain account, not the local system account. And I presume both servers need to be under the same account.

  61. I am trying to configure the SQL Mirroring in VPN, i am getting Database is not configured for mirroring in principal server even i could able to telnet each other from the mirror and principal servers. Only this is the IP series between both are different and in workgroup. Can you please help me? Thanks.

  62. It was so simple fix, related to adding the service account of either servers and providing sysadmin rights.

    The error is misleading while the solution is pretty simple and straight..
    Server A –> principal –> domain\Servic_Account1
    Server B –> Mirror –> domain\Servic_Account2
    Make sure domain\Servic_Account1 has sysadmin rights on Server B
    and the same way domain\Servic_Account2 has sysadmin rights on Server A.
    Set up the mirroring again and you should be good with out any errors.

  63. To all who have experienced this problem. Check the Windows log or SQL Log, I had two important entries:

    1 incompatible encryption
    2 You have no right to connect

    Solution. In the case of encryption, it was not important and I just turned off. In the second case, where the problem is the user, it turned out that the user specified in the log was not the same user on the mirror which it ran. Check the Windows Log or SQL Log and you will find out to which user you had to grant permission to connect to the mirror.

    GRANT CONNECT ON ENDPOINT :: TO

  64. Hello,

    I am having the same issue and I have tried all the options above successfully. However, I still face the same issue. When I go to the Mirrored instance and issue the command “Alter database DBN set partner =’TCP://abc.abc.com:5022′ its executed successfully, but I get the error on the principal server. Please suggest

  65. I’m still having the same 1418 connection issue after trying all above solutions. My situation is:

    Two identical servers on the same domain running SQL Server 2008 R2 Enterprise SP2.
    I had 27 databases mirrored without a witness and one of the servers rebooted and I lost mirroring.
    I then ran ALTER DATABASE [Database] SET PARTNER OFF on both server for all databases.
    I have DROPPED and CREATED the mirroring endpoints on both servers.
    I have backed up all databases and transaction logs for all databasses and restored to mirror in no recovery state as usual.
    I have used the same domain account for both SQL Server Engines and Agents.
    Account is sysadmin in both SQL Servers.
    Account is local administrator on both boxes.
    Account was GRANTED CONNECT on ENDPOINT::mirroring_endpoint TO [account].
    All databases have database master keys.

    I can ping both servers from each other.
    I can telnet to both server ip and ports for mirroring without issue from both servers.
    Firewalls are off.

    I have no idea what to do next. Although I do have a call into Microsoft for support.

    Thanks for any suggestions,
    Robb

  66. Note: Server is a production server needing to be up 24/7, but we don’t have the resources to cluster, so are mirroring for now. Mirroring WAS WORKING just fine before we lost power and I lost the mirroring. Don’t understand what might have happened.

    Robb

  67. Figured it out! In doing documentation for a Microsoft call I wanted to be complete so I dropped and recreated the database keys and everything worked after that! What do you know…

  68. Problem: You face the error as given below:
    The server network address “TCP://SQLServer:5023? cannot 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)

    Solution:
    Make sure that SQL Server service for both Principal and Mirror server runs under local (in case Principal and mirror instances on the same machine) or domain account which is part of local administrators group on both the machines.

  69. I experienced this issue. I found failed logins from the event viewer. Make sure the System service has permissions to both instances (has a login, and granted connect to the endpoint).

  70. Checked all the points upthere same error 1418. In this particular case I’m using a single server with two SQL instances, cos at the moment I don’t have two server to test the procedure. Could you suggest me something to test more?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s