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
Time: 6:13:38 PM
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]
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.
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;
- Start the Microsoft SQL Server Management Studio
- Connect to the database server that has an issue.
- Go to the Security folder
- Add the login name that is indicated in the Event Log. In my example MYDOMAIN\sqlservice
- Select the account you've just added and open its property (i.e., right click it)
- Select Securables
- Press Add...
- Select (leave it selected as) Specific Objects...
- Perss OK
- Under Select Objects dialog box press Object Types...
- Check Endpoints
- Press Browse...
- Add [Mirroring] object.
- Once back in the main property window, check mark Grant
Total hours to figure this stuff: 2 hours.