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

  • Hi,

    I have a physical server windows server 2012 with sql server 2012 and I have too VMWare with Windows server 2012 with sql server 2012 installed.

    Ping each other its OK, I dont create endpoint between both….
    I have router 192.168.0.1
    The machine number one to connect wired direct in swicth router and other notebook via wireless using same lane IP…

    I try configure both but SQL SERVER – The server network address “TCP://SQLServer:5023” can not be reached or does not exist….

    So where I’m failed ..configuration, POrts, ENdpoints..I dont know

    Reply
    • Are you able to telnet endpoints?
      Have you done full and log restore?
      Do you see any error message in ERRORLOG about communication issues.

      Reply
  • Hello Pinal, I am trying to mirror our sql server for the first time. I tried to restore the database with no recovery. The database is always saying restoring and in accessible. When i googled for the problem, i got answer from one of your other article. https://blog.sqlauthority.com/2014/10/24/sql-server-database-stuck-in-restoring-state/ Does you solution for this article link, overlaps the solution for this article? I am stuck with the same problem
    The server network address “TCP://SQLServer:5023” can not be reached or does not exist….
    Any help much appreciated.
    Thanks,
    Riyaz

    Reply
    • Mirrored database is always inaccessible so that’s expected. you need to troubleshoot second issue. “The server network address … ” error.

      Reply
      • I too an finding that doing a Full restore puts the restored database into “Recovering..” . So then I follow that with a restore of transaction logs. The database remains in “Recovering…” status. So I blindly plow forward and attempt to create the mirror and here I get the error on my Primary about reaching the Mirror server.

        1) Telnet results are good, I can telnet to port 5022 from the Primary to the Mirror, and vice versa.

        2) All SQL tests all look good showing proper information using these queries:

        SELECT type_desc, port FROM sys.tcp_endpoints;

        SELECT state_desc FROM sys.database_mirroring_endpoints;

        SELECT role FROM sys.database_mirroring_endpoints;

        SELECT EP.name, SP.STATE,
        CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
        AS GRANTOR,
        SP.TYPE AS PERMISSION,
        CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
        AS GRANTEE
        FROM sys.server_permissions SP , sys.endpoints EP
        WHERE SP.major_id = EP.endpoint_id
        ORDER BY Permission,grantor, grantee;

        3) Firewall on Primary and Mirror are set to permit anything in the Domain (Private and Public have restrictions)

        Additionally, I’m using SQL Server 2008 R2 on both the Primary and Mirror.

        Any thoughts on this one? Thanks for any more tips.

  • Hope this can help someone.

    Check out whether the SQL server & SQL server agent has the user login with full privilege/permissions.To change it:
    Run-services.msc-SQL Server-Properties-Logon-Choose a correct user.

    Reply
  • You can add connect permission for the mirroring login on the endpoint opposite to the initial principal. That was the issue I had. Then again, mirroring will be replaced by Always On Availability Groups anyway.

    Reply
    • Sure. I think that would be similar to Suggestion 6 with service account rather than ALL.

      Reply
  • leonardo rojas
    March 15, 2017 9:01 am

    Hi I have the following scenario:

    SQLSERVER Versions: SQLSERVER 2008 R2 SP3

    1) I have a cluster server names: CLUSTERPROD (this obviously behalvs to a domain)
    2) I have a new virtual machine out of the domain, and it has the name: CLUSTERPROD

    I tryed to establish mirror,but i receive the error:
    Msg 1418, Level 16, State 1, Line 1
    The server network address “tcp://XX.XX.XX.XX: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.

    I think that I’m trying something not possible.

    I try to use fullycualified names as: tcp://ipv4:port

    We are trying to migrate the productive machine to virtualserver without change server machine, ad reduce the time of the maintenance window.

    Reply
  • Hi Pinal, I followed all the steps that you gave but I still get an error. I have 2 servers on a workgroup. did I miss something?

    Reply
    • For workgroup, you need to use Certificate based authentication..

      Reply
      • philippinesentinelJerome
        April 12, 2017 11:03 pm

        Hi Pinal, thank you for this. I decided to use a domain controller in configuring the sql mirroring. Unfortunately, I still go the same error. Any thoughts?

      • can you check ERRORLOG from both sides and see if you have any interesting error.

  • jaspreet singh
    April 14, 2017 7:47 am

    1st step was enough to fix the issue for me, leaving the database in restoring state

    Reply
  • 1) If using cert-based mirroring, make sure to have both principal and mirror entries in the C:WindowsSystem32driversetchosts file
    2) Also make sure to have aliases (name, port, IP, protocol) defined in SQL Server Configuration Manager in both the mirror and principal servers.

    Reply
  • Dear Pinal Dave,

    I’m configure the mirroring but also database go the in recovery mode in secondary database. but I’m cheek the issues networking. it changes the local path. because same issues will coming

    error 1418
    Thanks,
    anil

    Reply
  • Hi Pinal,

    One gotcha to look out for: I’d changed the user the server and agent were running under since installation and also have mixed mode authentication. I had to add the service user as a windows login and grant connect to the endpoint before I could get it to work (no other special privileges needed).

    Thanks
    John

    Reply
  • I have server1→node1 and Server2→node2 in cluster environment and mirrored in unclustered environment.

    When SQL services running on Node2 then mirroring works fine. i.e. restoring state and Node2(Principal) becomes synchronised.

    But one issue is there.
    When we do cluster failover i.e. moving services to node1 then mirroring becomes disconnected. And all node shows disconnected.

    Few points checked :
    a. Same logins used for configuration
    b. Connect permission given to mirroring endpoint.

    One issue found here.
    On execution of below command .
    Xp_readerrorlog 0,1,’server is listening on’ at node1…..

    A. When services running on node1 then 5022 not showing in query result.
    B. When services on node2 then it shows in query result.

    Why this is happening?

    Reply
  • Madhu Polisetti
    October 26, 2017 5:44 pm

    Hi Pinal,

    We are configuring the mirroring for the first time. We have Principal, Mirror, Witness in 3 different machines.

    We took a full backup from principal server and restored in Mirror Server with “No Recovery” option. Later took a transaction log backup from Principal server and restored the same in Mirroring Server with “No Recovery” option set.

    Now when I start mirroring in Principal server, it is saying that it can not access “TCP://{MirrorServerName}:5022”

    We cross checked end point statuses in all machines, the state is “STARTED”.

    We are able to connect to port number 5022 of MirrorServer from Principal Server. (Cross checked with telnet).

    Where are we going wrong?

    Reply
  • vardhineni srihari
    October 29, 2017 11:42 am

    hi
    i am facing 1448 error while configuring sql database mirroring,please help me to resolve the error

    Reply
  • TITLE: Database Properties
    ——————————

    An error occurred while starting mirroring.

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

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

    The server network address “TCP://SERVER_1.Anbu.in: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)

    Reply
    • And what do you have in Errorlog? None of 7 suggestion worked? It must be a very secure system.

      Reply
  • In my case, Suggestion 7: Delete the end points and recreate them – That worked. Why? It turned out my certificates had expired because they were created with a default 1 year expiration. So, in order to recreate the endpoints, I had to recreate the certs – now with a much longer expiry date. Thanks Pinal Dave for all your great articles that have helped me immensely over the years.

    Reply
  • Abhishek Agarwal
    June 27, 2018 8:23 pm

    I am planning to configure mirroring in AWS environment and I have 3 servers, Primary, Secondary & Witness servers in different subnets. Is it possible to configure mirroring between three or there will be some challenge?

    like
    Private IPs are

    10.204.3.5
    10.204.9.5
    10.204.79.101

    Reply
  • above solutions doesn’t fix my problem please help me out.

    Reply
  • Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem)- ******This is fine

    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).***** This is fine

    Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023”. Its Server Name or SQL Service Name????

    Suggestion 4: Make sure your firewall is turned off.***** This is Fine.

    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.***** This is Fine

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

    GRANT CONNECT ON ENDPOINT::Mirroring TO ALL- What is this ALL , any user we have to Specify????

    Suggestion 7: Delete the end points and recreate them.***** This is fine.

    But still i am Having same Error..
    Point 3 and 6 i want some clarification

    Reply
    • Additional Suggestion:
      Run this script and check the encryption_algorithm_desc column in both primary and secondary. If you using different algorithm in both server then Drop and recreate the ENDPOINT with same algorithm( eg : ENCRYPTION = REQUIRED ALGORITHM AES).

      SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
      t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
      e.connection_auth_desc
      FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
      ON e.endpoint_id = t.endpoint_id;

      Reply
  • Above comments not working.pls suggest.

    network address can’t found or doesn’t exist.check network address n ports are for local n remote endpoint are operational.

    Reply
  • In my case domain sql service user account exists in sql instance, but does not have permissions to the endpoint. (found this message in sql server logs)
    command “GRANT CONNECT ON ENDPOINT::Mirroring TO ALL” failed, there is no ALL option available in my case.
    command below solved my issue (replace domain and svcaccount to yours, and check if endpoint name is correct, its default name but can be changed)
    GRANT CONNECT ON ENDPOINT::Mirroring to [yourdomain\svcuseraccount];

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version