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 (https://blog.sqlauthority.com), Many thanks to Solid Quality Mentors ) for their valuable suggestions.

, ,
Previous Post
SQLAuthority News – Download – Microsoft Sync Framework Power Pack for SQL Azure November CTP (32-bit)
Next Post
SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

Related Posts

165 Comments. Leave new

  • 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

    Reply
    • 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.

      Reply
      • 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.

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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Anthony Stephens
    April 27, 2013 3:11 am

    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.

    Reply
  • 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!

    Reply
  • 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:

    ——————————

    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:

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

    OK
    ——————————

    Optimistic response anticipated.

    Regards,
    Rigel Networks

    Reply
  • thanks, once again. this is the one that did it for me:
    GRANT CONNECT ON ENDPOINT::Mirroring TO PUBLIC

    Reply
  • A big thank to you Pinal…. All sorted now . but the step by wud doc be much approciated

    Reply
  • saran kumar reddy
    August 29, 2013 3:13 pm

    how to uninstall complete Sql server in my local system ?
    provide the steps.
    i am trying to un install it showing error Microsoft sql server 2008 r2 rsfx is already installed
    .

    Reply
  • 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]

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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”;

    Reply
  • 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.

    Reply
  • MUrali Krishna
    December 1, 2013 6:24 pm

    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.

    Reply
  • If you are mirroring multiple instances, make sure that you use a different port for each instance.

    Reply
  • 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.

    Reply

Leave a Reply