Watch your CREATE CERTIFICATE *_DATE clauses syntax order!

  • Article

I just noticed that given the way SQL Server parser parses the CREATE CERTIFICATE statement, the order in which you specify START_DATE & EXPIRY_DATE clauses really matters.

If you specify the EXPIRY_DATE clause first, whatever value you specify is set as the expiry date attached to the certificate, but if a START_DATE clause is encountered afterwards then the start date is set to whatever value you provide in that clause AND the expiry date is updated with whatever value comes in the START_DATE clause plus one year.

Since the documentation doesn’t mention it is expected to work that way it is considered a defect which, by the way, has been fixed already in the code base for next major release. In the meantime, just have it in mind.

CREATE CERTIFICATE WhatYouExpect

ENCRYPTION BY PASSWORD = 'AAABBBCCC123!'

WITH SUBJECT = 'WhatYouWouldntExpect',

START_DATE = '05/12/2010', EXPIRY_DATE = '05/22/2010'

CREATE CERTIFICATE WhatYouWouldntExpect

ENCRYPTION BY PASSWORD = 'AAABBBCCC123!'

WITH SUBJECT = 'WhatYouWouldntExpect',

EXPIRY_DATE = '05/22/2010', START_DATE = '05/12/2010'

select name, start_date, expiry_date FROM sys.certificates

name start_date expiry_date

-------------------------------------------------------------------------------------------------------------------------------- ----------------------- -----------------------

WhatYouExpect 2010-05-12 00:00:00.000 2010-05-22 00:00:00.000

WhatYouWouldntExpect 2010-05-12 00:00:00.000 2011-05-12 00:00:00.000

Thanks,

Nacho