Misleading error 1418 message in SQL Server Database Mirroring

  • Article

While setting up a four-server database mirroring environment (one domain controller, two partner SQL Servers, one witness SQL Server), I ran across a misleading error message.  The whole process for setting up a database mirror is fairly easy, if you have the right components in place. However, I was missing an important detail and the error message there really did not help.

First of all, never starting looking into SQL Server 2005 Database Mirroring without first reading this page carefully: https://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx. It includes the perfect dose of technical information required to understand what's happening and how you should configure and monitor your systems. A fair amount of it relates to setting up the required three servers (only two servers if you don't need automatic failover).

First of all, make sure you have the latest SQL Server 2005 service pack (you need at least SP1, but I used SP2). Using Active Directory and the same service account across the systems will help simplify your security. The wizard (under database, properties, mirroring) sets up the required mirroring endpoint on each server, granting the right permissions to the service account. Once you get a backup of the database from the principal restored on the mirror, you should good to go.

I did all that but got stuck at this error: "The server network address TCP://<servername>.<domain>.com:<portnumber> 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)". This happened right when I click on the button to start mirroring, after the endpoints were all set.

I checked my networking configuration. I check my firewall settings. I pinged one server from the other and vice versa. I used telnet to connect to the endpoint port from one server to the other. All seemed fine, so I was a little frustrated. After reviewing all my steps and re-checking the documentation, I figured it out: I had not use the required "WITH NORECOVERY" option when restoring the database on the mirror. Once I did that, everything worked!

Amazing how an error message can point you the wrong way...