SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 mirroring run into 1418 error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

784 Posts

Posted - 01/30/2014 :  10:18:14  Show Profile  Reply with Quote
Hi,
This one belongs to drive-me-crazy category. I will break down my post into these parts:
--summary—
--more info—
--things had tried—
--discussion\question—

--summary—
While doing SQL Mirroring, got 1418 error:
The server network address “TCP://db01: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)

--more info—
Principal: db02. Sql 2012 (11.0.3128) enterprise, windows server 2012 v6.2
Mirror: db01 same sql and windows built as db02. On the same domain.
No witness server.

--things had tried—
1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (every retry start from here. check).

2: Make sure that from Principal the latest LOG backup is restored to mirror server. (check).

3: Make sure firewall is turned off(Check).

4: 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. (check, select [state], state_desc from sys.database_mirroring_endpoints returned 0, started on both servers)

5: Try the following command.
GRANT CONNECT ON ENDPOINT::Mirroring TO Public

6: Delete the end points and recreate them.

7. modified hosts file on both servers to add
[IPPrincipal] [ServerNamePrincipal]
[IPMirror] [ServerNameMirror]
8. override mirroring database properties Principal and Mirror entries after completed the Configure Security wizard to following pairs:
TCP:// [IPPrincipal]:5022
TCP:// [IPMirror]:5022
TCP://DB02.FullDomainName:5022
TCP://DB01. FullDomainName:5022
TCP://DB02:5022
TCP://DB01:5022
And repeated above for another port 8001

9. make sure SQL Service accounts is listed as a sysadmin on the sql instance, and listed as a member of local machine’s Administrator. (check. NT Service\MSSQLSERVER and NT Service\SQLSERVERAGENT on both servers)

10. on mirror’s SQL Configuration Manager
tcp/ip properties show IP2 Active:Yes; Enabled:No; IP Address: ...240 <--change enabled to yes

--discussion\question—
These servers are designated for SharePoint 2013, and our SP consultants had "hardened" the instances to use a custom port(for example 8001) for TCP\IP.

Some has suggested Check if telnet to ports using command TELNET ServerName Ports like “telnet SQLServerName 5023 from both direction. I run into telnet is not a recognized command, and haven’t spend time to figure out how to turn it on.
Then I asked our network guy, and he run nmap the ip 8001 is open.

Anyone see anything that I should try?

Thanks!
hommer

Edited by - Hommer on 01/30/2014 10:50:43

tkizer
Almighty SQL Goddess

USA
36661 Posts

Posted - 01/30/2014 :  12:36:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
You definitely need to test via telnet. It doesn't come installed automatically on newer operating systems, so you have to add it in add/remove features (or similarly named) in control panel. It does not require a reboot, though it does take a few minutes.

We have to use the FQDN in our mirroring setup: servername.domain.company.com.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 01/30/2014 :  13:32:44  Show Profile  Reply with Quote
In the absent of telnet, I downloaded the nmap then run it from db02 against db01.

It returned all the info, including FQDN, which I have used as one pair of the value in my first post.


Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 02/24/2014 :  16:44:09  Show Profile  Reply with Quote
Finally, got through this ugly error.

Having the windows team created a domain account, then based on next,

http://support.microsoft.com/kb/811889

have them configured the SPN to let two servers communicate, then the whole mirroring fell into place.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000