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
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
Are you able to telnet endpoints?
Have you done full and log restore?
Do you see any error message in ERRORLOG about communication issues.
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.
rather than services.msc, I would suggest SQL Server Configuration Manager
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.
Sure. I think that would be similar to Suggestion 6 with service account rather than ALL.
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.
HI guys I follow this note: https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/example-setting-up-database-mirroring-using-certificates-transact-sql?view=sql-server-2017#ConfigureMirroringPartners
and it works for me.
Thanks
That’s great. Thanks for sharing.
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?
For workgroup, you need to use Certificate based authentication..
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.
1st step was enough to fix the issue for me, leaving the database in restoring state
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.
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
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
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?
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?
The issue may be with firewall.
If the issue is with firewall , telnet shouldnt work right?
hi
i am facing 1448 error while configuring sql database mirroring,please help me to resolve the error
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)
And what do you have in Errorlog? None of 7 suggestion worked? It must be a very secure system.
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.
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
above solutions doesn’t fix my problem please help me out.
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
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;
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.
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];
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.