Tuesday, November 27, 2007

SQL 2005 Mirroring Witness Connection Does Not Estalbish on one node

Symptom

When you've added an Witness Server to the mix, and you start to get the following error message in the Application Log. You are running all of the service under a specific domain user and everything runs but this. The Database Mirroring Monitor shows that Witness Connection is OFF (red X) on only one server.

Event Type: Information
Event Source: MSSQL$XYZ
Event Category: (4)
Event ID: 28048
Date: 11/27/2007
Time: 6:13:38 PM
User: N/A
Computer: SQLServer2
Description:

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

Cause

Root cause is really unknown. Probably a bad sequence of installing and configuring database mirroring. But the immediate cause that can be fixed is that the computer node which you have seen the event log is not granting the Connect permission on the Mirroring endpoint object in the securables for the log in account mentioned.

Fix

This is an issue of the Witness Server unable to establish a connection to the Principal or Mirror Server (whichever is broken). I was able to fix this by;

  1. Start the Microsoft SQL Server Management Studio
  2. Connect to the database server that has an issue.
  3. Go to the Security folder
  4. Add the login name that is indicated in the Event Log. In my example MYDOMAIN\sqlservice
  5. Select the account you've just added and open its property (i.e., right click it)
  6. Select Securables
  7. Press Add...
  8. Select (leave it selected as) Specific Objects...
  9. Perss OK
  10. Under Select Objects dialog box press Object Types...
  11. Check Endpoints
  12. Press Browse...
  13. Add [Mirroring] object.
  14. Once back in the main property window, check mark Grant
That has fixed it!

Total hours to figure this stuff: 2 hours.
Post a Comment