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
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
Yesss SQL Server Service account must not be a local system account.Change it to Domain Account.
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
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
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
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…
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.
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).
Congrats, great article…save my life ;-)
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?
In addition to ports 5022 or 5023,UDP 137 is also needed to be open in firewall at both side of mirroring.This port is for network discovery and netbios talking in between.
Hope this helps you.
If the port 5022 is busy due to cluster fail-over, this is how I fixed it:
— the mirror error is related to TCP/IP port 5022 being busy
Server TCP provider failed to listen on [ IP address 5022].
Tcp port is already in use.
use master
GO
— get the name ogf the endpoints
SELECT state_desc, * FROM sys.database_mirroring_endpoints
— restart the endpoints
alter endpoint cluster_Mirroring state = stopped;
GO
alter endpoint cluster_Mirroring state = started;
GO
— verify the endpoints
SELECT type_desc, port FROM sys.tcp_endpoints
— chnage the port the mirror is listening to
ALTER ENDPOINT [cluster_Mirroring] AS TCP (listener_port = 5023)
— restart the endpoints
GO
alter endpoint cluster_Mirroring state = stopped;
GO
alter endpoint cluster_Mirroring state = started;
GO
— change back the port the mirror is listening to
ALTER ENDPOINT [cluster_Mirroring] AS TCP (listener_port = 5022)
— restart the endpoints
GO
alter endpoint cluster_Mirroring state = stopped;
GO
alter endpoint cluster_Mirroring state = started;
GO
— verify the endpoints again
SELECT type_desc, port FROM sys.tcp_endpoints
— VERIFY THE MIRRORING AGAIN
— the mirror error is related to TCP/IP port 5022 being busy
Server TCP provider failed to listen on [ IP address 5022].
Tcp port is already in use.
use master
GO
— get the name ogf the endpoints
SELECT state_desc, * FROM sys.database_mirroring_endpoints
— restart the endpoints
alter endpoint cluster_Mirroring state = stopped;
GO
alter endpoint cluster_Mirroring state = started;
GO
— verify the endpoints
SELECT type_desc, port FROM sys.tcp_endpoints
— chnage the port the mirror is listening to
ALTER ENDPOINT [cluster_Mirroring] AS TCP (listener_port = 5023)
— restart the endpoints
GO
alter endpoint cluster_Mirroring state = stopped;
GO
alter endpoint cluster_Mirroring state = started;
GO
— change back the port the mirror is listening to
ALTER ENDPOINT [cluster_Mirroring] AS TCP (listener_port = 5022)
— restart the endpoints
GO
alter endpoint cluster_Mirroring state = stopped;
GO
alter endpoint cluster_Mirroring state = started;
GO
— verify the endpoints again
SELECT type_desc, port FROM sys.tcp_endpoints
— VERIFY THE MIRRORING AGAIN
Thanks for sharing the T-SQL steps.
We had a similar problem to fix it we had to add the COMPUTER$ account to each of the Primary , Witness , and secondary server , so on primary we added the computer$ account for the mirror and the witness , On the secondary we added the computer$ account for the primary and witness and so on .
Also we granted GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\[COMPUTER]$] permissions .
So the question is why does it need the permissions on the COMPUTER$ account when we have specified the service account separately and has all these permissions granted to it
If SQL Service account is LocalSystem then machine account is used to communicate.
What could also happen is that on server A (principal) you are running SQL server enterprise Edition and on server B (Mirror) you are running Developer Edition.
Despite both edition can setup mirroring that can only work when DEV edition is mirrored with another DEV Edition or ENT edition is mirrored with ENT Edition
you cannot have ENT Edition mirrored with a DEV Edition
I had the issue that’s why i think sharing could help
The best way to fix this is download postgre. Unbelievable that a product of this pricing can not guide you through the process of setting up a built in feature and you need to bang your head for days to get started, if at all.
Hello Mr. Pinal,
First of all, thanks for your amazing work on your blog, I’m not an SQL expert at all, although I have used your blog for SCCM purposes :-)
My suggestion is to add point 3) to change service account on SQL Service Logon (SQL Server Configuration Manager) I was stuck for 6 hours! Until I did that (not listed on here but on a video on YT, where everyone thanked as they were also stuck there!)
That is a great suggestion Thank you.
Database mirroring worked when I left for vacation, but a week later DB mirror broke…Issue: principal was using RC4 encryption and mirror was using none…Not sure what caused it during my vacation off time…Resolution: drop and recreate all mirror endpoints from both principal and mirror and all DBs resumed mirroring again either synchronized or synchronizing…. Your tips are very helpful…I learn a lot from your website ….Many thanks and appreciation… David N Nguyen.
Thanks spwr-dba
My Issue Solved
spwr-dbaJune 27, 2013 3:18 am
thanks, once again. this is the one that did it for me:
GRANT CONNECT ON ENDPOINT::Mirroring TO PUBLIC
MSDN also offers some troubleshooting options not covered here, it’s worth a read:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/aa337361(v=sql.110)
This solution worked for me too. (after half an hour wasted checking firewalls!)