SQL Server 2005 DB Mirroring Error

Question

I'm getting the following error when trying to setup SQL Server 2005 DB Mirroring on my servers. I am running two Win2K3 servers with SQL 2005 on each with one of those running alongside a SQL 2000 Version. After restoring the database with NORecovery and configuring the endpoints, I run into the following error when I try to start the mirroring session.

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'TestDB'.  (Microsoft.SqlServer.Smo)

------------------------------

An exception occurred while executing a Transact-SQL statement or 
batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://S-01.ssl.local: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)

I'll also note that I can telnet into the port using the full name from both servers.

Update

The secondary server is running SQL Server 2005 as a named instance on local 1434 and over the network as 1433 on the ip addresses that match the FQDN being used.

[EDIT]

I was able to Telnet to port 5022 on both servers.

[SOLUTION]

In the case that you can logging and both ports are active it may be that the underlying account that SQL Server is using is not a domain account. On our servers the local system account was who the service was logged in as. Once this was changed to be the administrator domain account instead the problem was resolved.

Answer 1

Since it looks like your login is successful and it's failing while actually enabling mirroring, have you tried using SQL Profiler to see exactly what statement is failing?

Not sure if you're familiar with Profiler, but here's a tutorial (It's for SQL 2000, but the tool is only slightly different in 2005).

Answer 2

Did you telnet to port 5022 on each server? That is the port being used by database mirroring, not the SQL ports 1433 / 1434.

I would check any firewall settings and that the SQL Server services are running under domain accounts.

You might also want to check the other points on this troubleshooting article about mirroring:

http://blogs.msdn.com/grahamk/archive/2008/12/08/database-mirroring-error-1418-troubleshooter.aspx

Answer 3

Check the SQL Log on both machines and ensure that the SQL Server is listening on the port 5022 that the database mirroring endpoint should be using.

On the server which has the named instance, is there another instance which is configured to use the same TCP port for mirroring or something else?

Answer 4

In the case that you can logging and both ports are active it may be that the underlying account that SQL Server is using is not a domain account. On our servers the local system account was who the service was logged in as. Once this was changed to be the administrator domain account instead the problem was resolved.