Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 mirroring run into 1418 error

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-01-30 : 10:18:14
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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-30 : 12:36:54
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

808 Posts

Posted - 2014-01-30 : 13:32:44
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

808 Posts

Posted - 2014-02-24 : 16:44:09
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
   

- Advertisement -