Watch your CREATE CERTIFICATE *_DATE clauses syntax order!

Watch your CREATE CERTIFICATE *_DATE clauses syntax order!

  • Comments 1

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

Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
  • Molas más que la música de los caballitos pero dosifícate los posts para que te duren todo el año ;-)

Page 1 of 1 (1 items)