Trouble setting up witness in SQL Server mirroring scheme w/ error

Question

I've got a trio of Windows servers (data1, data2 and datawitness) that aren't part of any domain and don't use AD. I'm trying to set up mirroring based on the instructions at http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx. I've had success right up until the final set of instructions where I tell data1 to use datawitness as the witness server. That step fails with the following message:

alter database MyDatabase set witness = 'TCP://datawitness.somedomain.com:7024'

The ALTER DATABASE command could not be sent to the remote server instance 'TCP://datawitness.somedomain.com:7024'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

I've tested both port 7024 as well as 1433 using telnet and both servers can indeed connect with each other. I'm also able to add a connection to the witness server from SQL Server Manager on the primary server. I've used the Configuration Manager on both servers to enabled Named Pipes and verify that IP traffic is enabled and using port 1433 by default.

What else could it be? Do I need any additional ports open for this to work? (The firewall rules are very restrictive, but I know traffic on the previously mentioned ports is explicitly allowed)

Caveats that are worth mentioning here:

• Each server is in a different network segment

• The servers don't use AD and aren't part of a domain

• There is no DNS server configured for these servers, so I'm using the HOSTS file to map domain names to IP addresses (verified using telnet, ping, etc).

• The firewall rules are very restrictive and I don't have direct access to tweak them, though I can call in a change if needed

• Data1 and Data2 are using SQL Server 2008, Datawitness is using SQL Express 2005. All of them use the default instance (i.e. none of them are named instances)

Answer 1

After combing through blogs and KB articles and forum posts and reinstalling and reconfiguring and rebooting and profiling, etc, etc, etc, I finally found the key to the puzzle - an entry in the event log on the witness server reported this error:

Database mirroring connection error 2 'DNS lookup failed with error: '11001(No such host is known.)'.' for 'TCP://ABC-WEB01:7024'.

I had used a hosts file to map mock domain names for all three servers in the form of datax.mydomain.com. However, it is now apparent that the witness was trying to comunicate back using the name of the primary server, which I did not have a hosts entry for. Simply adding another entry for ABC-WEB01 pointing to the primary web server did the trick. No errors and the mirroring is finally complete.

Hope this saves someone else a billion hours.