SQL Yoga : Certificate Expired, Mirroring Stopped

Database Administrators might face this issue while certificate gets expired and database mirroring (Non domain database mirroring) gets disconnected as two servers, Primary and Secondary cannot communicate with each other. The error message can be found in Log as follows: Message Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'. [CLIENT: xxx.xxx.xxx.xxx] In this post, We are going to review step by step process to resolve this issue by providing renew parameters with certificate configurations. 1. Create a new certificate with longer endpoint (on Principal):

  1: -- Create a new certificate for the endpoint
  2: USE master;
  3: CREATE CERTIFICATE [Principal_Certificate_New]
  4:    WITH SUBJECT = 'Principal Certificate',
  5:     START_DATE='01/01/2014', -- Provide date prior to current date
  6:     EXPIRY_DATE='12/31/2020'; -- Provide this as future date
  7: GO

2. Take backup of the newly created certificate (on Principal):

  1: USE master;
  2: BACKUP CERTIFICATE [Principal_Certificate_New] TO FILE = N'F:\Backup\Principal_Certificate_New.cer';
  3: GO

3. Set mirroring to use the newly created certificate (On Principal):

  1: ALTER ENDPOINT DBMirrorEndPoint
  2: FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [Principal_Certificate_New])
  3: GO

4. Delete the old certificate for endpoint (on Principal):

  1: USE master;
  2: DROP CERTIFICATE [Principal_Certificate_Old]
  3: GO

5. Drop the Old Certificate for Principal Login (on Mirror):

  1: USE master;
  2: DROP CERTIFICATE [Principal_Certificate_Old]
  3: GO

6. Create a new certificate from the principal backup file (on Mirror):

  1: USE master;
  2: CREATE CERTIFICATE [Principal_Certificate_New] AUTHORIZATION PrincipalServerUser
  3: FROM FILE = N'F:\Backup\Principal_Certificate_New.cer';
  4: GO

7. Create a new certificate with longer endpoint (on Mirror):

  1: use master;
  2: CREATE CERTIFICATE Mirror_Certificate_New
  3: WITH SUBJECT = 'Mirror Certificate New'
  4:  ,EXPIRY_DATE = '12/31/2020' -- Provide this as future date
  5: GO

8. Take backup of newly created certificate (on Mirror):

  1: USE master;
  2: BACKUP CERTIFICATE [mirror_new_cert] TO FILE = 'F:\Backup\Mirror_Certificate_New.cer';
  3: GO

9. Set mirroring for newly created certificate (on Mirror):

  1: USE master;
  2: ALTER ENDPOINT DBMirrorEndPoint
  3: FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [Mirror_Certificate_New])
  4: GO

10. Drop the old certificate for endpoint (On Mirror):

  1: USE master;
  2: DROP CERTIFICATE [Mirror_Certificate_Old]
  3: GO

11. Drop the old certificate for Mirror login (On Principal):

  1: USE master;
  2: DROP CERTIFICATE [Mirror_Certificate_Old]
  3: GO

12. Create a new certificate from the mirror backup file (On Principal):

  1: USE master;
  2: CREATE CERTIFICATE [Mirror_Certificate_New] AUTHORIZATION MirrorServerUser
  3: FROM FILE = 'F:\Backup\Mirror_Certificate_New.cer'
  4: GO

13. Resume the mirroring session for each database(On Principal and Mirror):

  1: USE master;
  2: ALTER DATABASE [Mirrored_Database_Name] SET PARTNER RESUME
  3: GO

By following the mentioned steps, we can resolve the certificate issues for Mirroring database and Mirroring will be resumed.
Note : 1. Always prefer to provide the endpoint date as far date, so that issue doesn’t occur very soon. 2. During this process, Primary database is available without any interruption.
Reference: Tejas Shah (www.SQLYoga.com)