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.
165 Comments. Leave new
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 AuthorityNetwork 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?
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.
This is an outstanding article and one of the best I found on troubleshooting this error.
However, the steps in the article did not work for my workgroup mirror.
I had to configure certificate based authentication to get this to work. There is a walk through at https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/example-setting-up-database-mirroring-using-certificates-transact-sql?view=sql-server-2017#ConfiguringOutboundConnections
I hope this helps someone avoid the hours of frustration I had yesterday
This is an outstanding article and one of the best I found on troubleshooting this error.
However, the steps in the article did not work for my workgroup mirror.
I had to configure certificate based authentication to get this to work. There is a walk through at https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/example-setting-up-database-mirroring-using-certificates-transact-sql?view=sql-server-2017#ConfiguringOutboundConnections
I hope this helps someone avoid the hours of frustration I had yesterday
Hello to everybody, my problem is the user wich log the SQL Server service. I grant it as SYSadmin and I fix the problem.
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 https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/example-setting-up-database-mirroring-using-certificates-transact-sql?view=sql-server-2017
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.
Hello All,
I have GRANTED PERMISSION FOR ENDPOINT to PUBLIC and it worked.
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.
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.
thanks much for your post!! i solved my issue with your suggestion number 1!
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.
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
Sir,
Since 48 hrs I was struggling with 1418 Error. Tried all possibilities; but no luck. Please help me out.
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.
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.
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
Awesome! the sysadmin comment for the service accoutn ssolved my problem! thankyou!
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! :)
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.
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.