Witness server suddenly stopped working

Question

We failed over to our mirror server over the weekend to carry out some electrical maintenance in the server room, and then when attempting to fail back over afterwards one of the database was fine, but the other was stuck in "synchronising" state and refused to fail over. In the end we disabled mirroring, backed up the database and dumped a log, restored onto the main server and started mirroring again with no problems.

When I tried to link the witness back up to the database I got:

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

And looking at the event log on the witness, it is absolutely littered with error messages from SQL Server, e.g.

Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (5) 5(error not found). State 87.'.  [CLIENT: (our main server ip)]

A cryptographic operation failed. This error indicates a serious problem with SQL Server. Check the SQL Server error log and the Windows event logs for further information.

A system cryptographic call failed during a Service Broker or Database Mirroring operation: system error '5(error not found)'.

An SNI call failed during a Service Broker/Database Mirroring transport operation. SNI error '10065(error not found)'.

An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8471, State: 2. (Near endpoint role: Initiator, far endpoint address: '10.248.95.4')

Everything was fine up until now, and as far as I know nothing has changed on the server. Does anyone know what's wrong?

P.S. The setup is three servers on different networks, and we are using certificates to link the servers up.

Answer 1

Have a look at this connect link https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126074&wa=wsignin1.0

---

Thanks, Leks

Answer 2

I granted the administrators group and the network service account full access to the machine key store, which didn't change anything, and even tried adding the network service to the administrators group. No change.

The principal server is in a domain with SQL Server running under local system. The other two machines are in workgroup mode and have SQL Server running under the Network Service. I'm using certificates with AES encryption on the endpoints.

Answer 3

Hi,

Please refer to the following KB article which describes the issue:
http://support.microsoft.com/kb/912412

If there are any more questions, please let me know.
Thanks.

---

***Xiao Min Tan***Microsoft Online Community***

Answer 4

Hi,
Please refer to my last post where I stated I have already changed permissions to the machine keys store, with no effect.

Answer 5

Answer 6

Update: I reinstalled SQL Server Express on the witness server, recreated and installed the certificates on all the servers, and set up the hosts files again.

The principal and the mirror can both telnet to the witness on port 7024. The witness can telnet to port 7024 on both the principal and the mirror. Mirroring is active between the principal and the mirror.

When trying to run:

ALTER DATABASE MyDatabase SET WITNESS = 'TCP://MYWITNESS:7024'

It fails with:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://MYWITNESS:7024'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the principal server I get the following entries in the principal server's event log:

Database mirroring is inactive for database 'MyDatabase'. This is an informational message only. No user action is required.
Database mirroring is active with database 'MyDatabase' as the principal copy. This is an informational message only. No user action is required.
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://MYWITNESS:7024'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

And in the mirror server's event log:

Database mirroring is inactive for database 'MyDatabase'. This is an informational message only. No user action is required.
Database mirroring is active with database 'MyDatabase' as the mirror copy. This is an informational message only. No user action is required.

But nothing at all in the witness server's event log

Answer 7

Hi Leon,

Sorry for getting back late .

Have you seen these links http://archive.networknewz.com/networknewz-10-20080721SQLServerUpgradeDatabaseMirroringIssues.html and http://kbalertz.com/940254/Error-message-witness-database-mirroring-session-Server-ALTER-DATABASE-command-could-remote-server-instance.aspx . I think the solution for problems like yours is discussed in detail .

---

Thanks, Leks

Answer 8

Hi Lekss,
Sorry, I'd given up on this thread and the email notification never seems to work for me.

The first link was one I tried before. I removed all references to mirroring, including all the certificates and deleting the mirror databases, and then set everything up from scratch. The only difference was that I stopped getting any error messages in the event log instead of all the weird ones in my first post.

The second was never an issue, as we are using the hosts file to map to the IP addresses, which was working fine for months until the disaster described above.

I fear we may have to use up one of our support calls for this one. I hear 2008 has some mirroring enhancements but we don't have the money to upgrade. Hopefully when we do, the enhancements will include proper error logging for mirroring.

Thanks for your help anyway.