Hi,

 i have ran into some strange issue again. I am unable to add witness server to my mirror configuration. my error log is throwing error 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89'. help will be apprciated

• Edited by SeekWellDBA 22 hours 29 minutes ago
• Moved by Eric__ZhangMicrosoft contingent staff, Moderator 3 hours 58 minutes ago Move to a proper forum
• Moved by Lydia ZhangMicrosoft contingent staff, Moderator 3 hours 54 minutes ago more appropriate

To add or replace witness server you can follow all following steps

1. After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree.

2. Expand Databases, and select the principal database of the session to which you are adding or replacing a witness.

3. Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.

4. Click Configure Security.

5. If the Configure Database Mirroring Security Wizard welcome screen appears, click Next.

6. In the Include Witness Server dialog box, click Yes, and then click Next.

7. In the Choose Servers to Configure dialog box, the Witness server instance check box is automatically checked. Click Next.

8. On the Principal Server Instance dialog box, keep the existing port and endpoint. Click Next.

9. On the Witness Server Instance dialog box, click Connect.

10. In the Connect to Server dialog box, specify the witness server instance in the Server name field, and use Windows Authentication (the default). Click Connect.

11. Once a connection is established, the listener port and database mirroring endpoint of the witness server instance are displayed on the Witness Server Instance dialog box. Click Next.

12. The Service Accounts dialog box contains fields for the domain service accounts of the principal, mirror, and witness server instances.

    • If the server instances all use the same service account, leave the fields blank.  

    • If the witness server instance uses a different service account from either of the partners, fill in the Principal, Mirror, and Witness fields with the account name:

      DOMAINNAME\username

      The domain name must be in upper case.

    Click Next.

13. On the Complete the Wizard summary screen, optionally, verify the witness configuration, and then click Finish.

14. On finishing, the wizard returns you to the Database Properties dialog box where the server network address of the witness now appears in Witness field. Also, High-safety mode with automatic failover (synchronous), which is required with a witness, is automatically selected.

    To enable the witness and change the session to high-safety mode with automatic failover, Click OK.

Hi SeekWellDBA,

Which server traces the handshake failed error? This error is related to the endpoint authentication, so the problem is from the certificates. Please make sure that the right certificates are deployed: look into master.sys.certificates and validate that the certificate thumbprints match between the two instances.

If I'd have to make a wild guess, I'd say: try adding a start/expiration date to the certificates you create. There is a problem with certs created in the eastern UTC time zones that makes the certs unusable for a numbers of hours if a start date is not provided. I believe Italy is GMT-1, so the problem would manifest as the handshake failing for 1 hour after the certs are created, then it would start working.

Hi SeekWellDBA,

Do you create a database mirroring session using certificate-based authentication?

If that is the case, please manually copy certificates around Principal, Mirror and Witness firstly. Then drop certificates, logins, users and recreate logins, users, certificates. Last, grant connect endpoint at all the servers. In this way, the above error should be eliminated .

For more detailed steps about setting up database mirroring with certificates, please review the following article:
https://msdn.microsoft.com/en-us/library/ms191140%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396

Thanks,
Lydia