Troubleshooting SQL Mirror errors

I was setting up a new database server and I had some difficulty in setting up the mirroring, so I thought I would post here just on the off chance it helps someone. We are still using SQL 2005, but I think the same ideas apply to SQL 2008.

When I tried to start mirroring I would get an error like this:

The server network address "tcp://[server name]: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.

First and foremost the single most important thing I learned was this: If you get an error while trying to start mirroring check the application event logs on both servers. The error messages you get back from the UI are not helpful at all. The event log messages will lead you very quickly to the error.

In my case, the first error I got was:

Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'. [CLIENT: ]

In my case, this error indicated that the two database endpoints did not have the same encryption algorithm selected. I was working with an existing server that has mirroring already running and a new server that did not yet have mirroring running. I didn't pay attention in the mirror setup wizard and did not check the box to indicate that the connection should be encrypted. This left the original server requiring encryption and the new server without encryption. You can check this by running the following query on both servers:

SELECT * FROM sys.database_mirroring_endpoints

Compare the values of the encryption_algorithm_desc column from both servers. In my case I had one server with RC4 and one server with None. They have to match. The solution was to drop the endpoint on the new server ('DROP ENDPOINT Mirroring') and re-run the mirror setup wizard and checking that the mirror server required encryption.

Once I figured out that error, the second error I got was:

Database Mirroring login attempt by user '' failed with error: 'Connection handshake failed. The login '' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: ]

Again, it was a configuration problem. In this situation it was because I had forgotten to set the user that the SQL service was running as. I believe SQL has to be running as the same domain user on both servers or, I believe there is some way to use certificates. In my case, my existing server was using the domain ID, but on the new server the service was running as 'Local System'. You can check this by examining the credentials used by the the 'SQL Server' service in the server administrator tools. Once I set the new server to use the same domain ID, mirroring started up successfully.

I hope this saves you some time.