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
After going through your checklist, I now get the following error on the principal when trying to start mirroring:
Additional information:
—> After failed for Database ‘MyDB’. (Microsoft.SqlServer.Smo)
—> An exception occurred while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
—>Database ‘MyDB’ cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)
This is, of course, bogus. The database has no visual indication that is restoring (I can query and modify the database). Of course if you go over to the partnering machine and look at the ‘MyDB’, it is constantly restoring (as it should be).
It seems to me that the mirroring feature of SQL Server is a very fragile thing and what should have taken me 10 minutes to set up has cost me hours of frustration.
Hello sir,
we applied all these solution but not able to establish the mirroring between two server’s dbs.
we have run this command:
ALTER DATABASE RCMs SET PARTNER = ‘TCP://(redacted):5050’
Got error is:
Msg 1418, Level 16, State 1, Line 15
The server network address “TCP://(redacted):5050” 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.
Please help…
The issue that I find hardest to remember, is to set the login for the alternate machine. After doing all of this successfully a couple of months ago, I needed to repeat the process. I started getting the error listed here over and over no matter what I did. Unfortunately I cannot discern that the step that I forgot about is that both of my SQL Servers run under the NT Authority/Network Service account on each respective machine. As such, I needed to go to each SQL Server, Security, Logins and add a login for the other _machine_. i.e. domain\machine.domain.com$ and give that account appropriate permissions.
What I would have guessed in that this is the step that is most frustrating the folks who are having issues.
Steve
Well u can try setting the Sql Server Services run with the same admin user.
I have followed all above steps but still facing error-
Msg 1418, Level 16, State 1, Line 1
The server network address “TCP://SQLServer.com: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 have followed all above steps but still facing error-
Msg 1418, Level 16, State 1, Line 1
The server network address “TCP://SQLServer.com:5024” 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 have followed all above steps but still facing error-
Msg 1418, Level 16, State 1, Line 1
The server network address “TCP://SQLServer.com: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.
Hi Pinal,
Big fan of your work in SQL. You are SQL God.
I’ve been trying to set mirroring with all the steps you provided but m still getting the same error of the said topic.
its sql server 2019 with SSMS v18.9.2.
Could it be a bug of ssms ?
Please reply.
Hi Pinal,
Request you to please update this thread with the below solution. Most of the online searches related to this issue landed here :).
I have followed each and every step mentioned in your article, but issue persisted. Out of nowhere we thought of updating the host file on primary. We entered the Mirror server IP addresses, Server Name and server name FQDN in the host file of the Primary. After this I was able to setup mirroring and its working fine.