Regular readers of my blog will be aware of my friend who called me few days ago with very a funny SQL Problem SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results. This time, it did not take long before he called me up with another interesting problem, although the issue he was facing this time was not that interesting and also very specific to him, however, he insisted me to share with all of you. Let us understand his situation at first.
My friend is preparing for DBA exam Exam 70-450: PRO: Designing, Optimizing and Maintaining a Database Server Infrastructure using Microsoft SQL Server 2008 and for the same, he was trying to set up replication on his local laptop. He had installed two different instances of SQL Server on his computer and every time when he started the mirroring, it failed with common error message.
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)
Well, before he contacted me, he searched online and checked my article written on the error in mirroring. However, he tried all the four suggestions, but it did not solve his problem. He called me at a reasonable time of late evening (unlike last time, which was midnight!). I even tried all the seven different suggestions myself, as previously proposed in my article; however, none of them worked. While looking at closely at services, I noticed something very simple. He was running all the instances on ‘Network Services’. In fact, his computer was a stand-alone computer. There was no network at all. Also, there was no domain or any other advance network concepts implemented.
I just changed services from ‘Network Services’ to ‘Local System’ as his SQL Server was running on his local system and there were no network services. This prompted to restart the services. As this was not the production server and his development machine, we restarted the services on the laptop (do not restart services on production server without proper planning).
After changing the ‘services log on’ account to localsystem, when he attempted to reconfigure the mirroring it worked right away. As usually in production server, proper domains are configured and advance network concepts are implemented I had never faced this type of problem earlier. My friend insisted to post this solution to his situation, wherein there was no domain configured and setting up mirroring was throwing an error. According to him, this is bound to help people, like him, who are preparing for certification using single system.
Reference: Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
Thanks, I will make a note of this, this situation can crop up when one is installing the sql server ,the server piece on a local pc/laptop.
Thankyou, very helpful and solved my problem.
Thank you!, I’ve been searching to a solution to a problem I am having which seems like a a slight variation on the above. I have three seperate machines each running SQL Server 05 under local system. The machines are in a workgroup not domain and all have identical DNS suffix ‘local’.
When attempting to start mirroring I get the following on the mirror server log – (sometimes, not always?!):
Message
Database Mirroring login attempt by user ‘NT AUTHORITY\ANONYMOUS LOGON.’ failed with error: ‘Connection handshake failed. The login ‘NT AUTHORITY\ANONYMOUS LOGON’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: 192.168.1.4]
It doesnt seem to matter however what credentials I supply when setting up the mirror via the wizard it either has some (error regarding anonymous login appears in log) or no effect (wizard fails with complaint one or more servers lacks FQDN – As far as I can tell this shouldnt be the case, they are all in format machinename.local and all pingable using the machinename.local form of address)
I can telnet to the mirror port to and from all machines so the route seems good to me – clearly I lack authentication but I cant determine where or what I need to grant
Really would appreciate a steer on this
Thanks
Philip
Thanks a ton, facing same problem and got resolved.
This error also can occur in following situations:
1) When the SQL Servers not in same Domain
2) When one of the SQL Servers in Domain and other not
3) When the SQL Servers are workgroups
Please read the following post that describes the solution:
Thanks….
I am A new DBA and also provide Basic training for SQL dba, one time at my student’s satnd alone PC is got the same error at the time of my Mirering lecture, But I aneble to solve it before I read this post. thnks
P.Dev.
Hi Pinal Dave
I have whole-heartedly embraced all of your suggestions thus far and still get the old 1418 error. I have 2 instances of SQL 2008R2 installed and one version of SQL Express (for witness) all locally on a PC that is on a domain. No matter what steps I take (including that above and your step by step guide) I still cannot reach the network address nor can I perform mirroring. Your thoughts would be appreciated.
Thank you, it really worked for me as well
ya it worked for me as well but i would like to know about mirroring in different machine without using a domain.. i know we need to issue a certificate. When i quered to create an endpoint it says already an endpoint exists and how to create an endpoint while one is already exist?
Goto System properties->Computer name->click Change button->click More button-Type the text box in “something.com” click ok->ok->Apply->ok
After restart the computer it will change
mirrioring also worked
I opened 3 instances of sql 2012 in my laptop. I am not able to mirror with above problem.I manually created 3 end points in different instances with 5022,23,24 port numbers. same problem agai. I don’t know about telnet or pinging between instances. Can you help me. I done remaining steps as above +7 steps of you article.please help me
Tell your friend I said thanks.
I am configuring mirroring between 3 instances of same sql server in a same workgroup.
I am getting Network server address “TCP://sss:5022” not reached or doesn’t exist
Thank you, thank you very much.,
You did well to post this, send regards to your friend for suggesting that.
Hi Pinal,
I have SQL Mirrored environment. Databases state changes to ‘in recovery’ mode after a week or so & application doesn’t work in this case. To resolve the issue, I always restart the SQL Services on both the SQL Servers & it works again for a week or so & suddenly change the databases state to ‘in recovery’.
Also we are using m3.medium (1CPU & 3.7GB RAM) & m3.large (2CPU & 7.5GB RAM) respectively. SQL servers are not in domain.
I checked the event logs & found below error:-
The mirroring connection TCP://Witness Server:5022 has timed out for database “database name” after 10 seconds without a response. Check the service & network connection
Can you please check & suggest, what could be the reason
Regards,
Sunny Chopra
have you tried restarting endpoints rather than SQL restart? Looks like some networking issue.
Yes, I’ve tried by restarting endpoints as well but issue is still there. For a week or so it works fine without any error or delay.
Regards,
Sunny Chopra
You should contact Microsoft support for expert advise and advance troubleshooting.
Thanks. This worked find for the SINGLE SYSTEM, can it work for the below situation as well?
What about setting up Mirroring in a “WORKGROUP” environment, using Principal & Mirror Servers on two different machines in the network, without any DOMAIN, without any Witness server.
Both Servers are running SQL Server 2008 Ent version.
Could you please help do achieve this task? Pls suggest the solution through SS Management Studio (GUI Mode), if possible.
Thanks in advance.