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
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
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.
Hi, i have the same problem, different networks connected by VPN. Were you able to solve this?
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.
Hello, I can’t make telnet to my port (5022), Sugestion 3. ¿How i do this?
Disable the firewall services by going to Run > services.msc (hit ‘Enter’) and disabling/stopping windows Firewall.
Thank you so much.
you saved my day.
Thanks a lot. Impressed with the level of detail and logical debugging flow.
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?
I’ve done all the steps above…
Still not workring
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…
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!
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.
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.
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.
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.
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.
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.
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!
Hi,
How about if my servers dont join to the active directory?
Is it still possible to do the mirroring?
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?
I have ried all the above tricks but still i am getting the error. Can any one please help me.
Thanks
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