Certificate expiry date

If you have a Database Mirroring setup or use Service Broker you may use certificates for endpoint authentication.

To create a certificate run:
CREATE CERTIFICATE [SQLService] ENCRYPTION BY PASSWORD = ‘YourStrongPassword’ WITH SUBJECT = ‘SQLService certified certificate’

When you run this a certificate is created.
If we query sys.certificates and take a look at what we created we can see the following:

select name, pvt_key_encryption_type_desc, start_date, expiry_date,is_active_for_begin_dialog from sys.certificates where name = ‘SQLService’

What we can see here is that our certificate is created and that we have a start date and expiry date. Since i didn’t set these when i created the certificate it’s created with default values which is the same start date as when i created it and the expiry date is one year later.

It might come as a surprise when your certificate suddenly has expired and your endpoints stops working and your database mirroring stops, not a very pleasant feeling.

So one thing to keep in mind is that whenever you create a new certificate make sure you set the expiry date.