locked
Alter expiry_date of a Certificate RRS feed

  • Question

  • Hi all,

    Is it possible to change the expiry date of an encryption certificate in SQL Server 2005?

    We just noticed that the default expiry seems to be one year hence and would like to change it.

    If we drop the certificate and re-create it will the not be issues with what is essentially a new certificate/signature?

    Regards
    Dave
    • Moved by CoolDadTx Tuesday, October 13, 2009 12:55 AM SQL related (From:Visual C# General)
    Monday, October 12, 2009 3:59 PM

Answers

  •   I think you have uncovered a bug, when the start date clause is defined after the expiration date, the expiration date is reset to the “default” value of “start_date + 1 year”.

       The workaround for this bug should be fairly simple: if you invert the order of START_DATE & EXPIRY_DATE clauses, the behavior will as expected.

    CREATE CERTIFICATE DavesCertificate_fixed WITH SUBJECT = 'Test',

    START_DATE = '10/15/2009', EXPIRY_DATE = '01/01/2050'

     

    select name, start_date, expiry_date FROM sys.certificates

     

    name                    start_date              expiry_date

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

    DavesCertificate        2009-10-15 00:00:00.000 2010-10-15 00:00:00.000

    DavesCertificate_fixed  2009-10-15 00:00:00.000 2050-01-01 00:00:00.000

     

      SQL Server itself will only use the CERTIFICATE key pair, the rest of the information (including expiration date) is available for applications which may take different actions based on these values.

      The only exception of this rule is Service Broker, which uses the certificate for authentication purposes and therefore makes use of the start_date and expiry_date.

      I hope this information helps,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, October 15, 2009 12:49 AM
  • Really?  What is the point of having start & expiry dates on the certificate at all? 

    I answered this and other related points here: http://blogs.msdn.com/lcris/archive/2007/10/04/sql-server-2005-a-note-about-the-use-of-certificates.aspx
    Unless I'm doing something drastically wrong you cant set expiry date at all. 
    DROP CERTIFICATE DavesCertificate 
    
    
    
    CREATE CERTIFICATE DavesCertificate WITH SUBJECT = 'Test',
    
    EXPIRY_DATE = '01/01/2050', START_DATE = '10/15/2009'
    
    
    
    SELECT name, subject, expiry_date, start_date  
    
    FROM sys.certificates
    
    WHERE name = 'DavesCertificate'
    
    

    Which returns...
    DavesCertificate Test 2010-10-15 00:00:00.000 2009-10-15 00:00:00.000




    Reagrds
    Dave  

    It looks like this behavior is a bug and the expiry_date is ignored if it precedes the start_date. Changing the order of the start/expiry dates seems to work as expected, so you can use that to get the certificate created with the proper dates. I.e., this will work as expected:

    CREATE

     

    CERTIFICATE DavesCertificate2 WITH SUBJECT = 'Test',
    START_DATE = '10/15/2009', EXPIRY_DATE = '01/01/2050'

    I'll point this issue to the security team, but I suggest you also open a report using the instructions from the sticky thread in this forum - issues reported by customers are treated with higher priority. Thanks for catching this!


    This post is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Alex Feng (SQL) Thursday, October 15, 2009 6:22 AM
    • Marked as answer by york0001 Thursday, October 15, 2009 4:28 PM
    Thursday, October 15, 2009 12:59 AM

All replies

  • You don't need to change the expiration date.  It doesn't have any effect, because SQL Server ignores the expiration date of all certificates.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, October 13, 2009 10:55 PM
  • Really?  What is the point of having start & expiry dates on the certificate at all? 

    Unless I'm doing something drastically wrong you cant set expiry date at all. 

    DROP CERTIFICATE DavesCertificate 
    
    CREATE CERTIFICATE DavesCertificate WITH SUBJECT = 'Test',
    EXPIRY_DATE = '01/01/2050', START_DATE = '10/15/2009'
    
    SELECT name, subject, expiry_date, start_date  
    FROM sys.certificates
    WHERE name = 'DavesCertificate'

    Which returns...
    DavesCertificate Test 2010-10-15 00:00:00.000 2009-10-15 00:00:00.000




    Reagrds
    Dave  

    Wednesday, October 14, 2009 11:30 AM
  •   I think you have uncovered a bug, when the start date clause is defined after the expiration date, the expiration date is reset to the “default” value of “start_date + 1 year”.

       The workaround for this bug should be fairly simple: if you invert the order of START_DATE & EXPIRY_DATE clauses, the behavior will as expected.

    CREATE CERTIFICATE DavesCertificate_fixed WITH SUBJECT = 'Test',

    START_DATE = '10/15/2009', EXPIRY_DATE = '01/01/2050'

     

    select name, start_date, expiry_date FROM sys.certificates

     

    name                    start_date              expiry_date

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

    DavesCertificate        2009-10-15 00:00:00.000 2010-10-15 00:00:00.000

    DavesCertificate_fixed  2009-10-15 00:00:00.000 2050-01-01 00:00:00.000

     

      SQL Server itself will only use the CERTIFICATE key pair, the rest of the information (including expiration date) is available for applications which may take different actions based on these values.

      The only exception of this rule is Service Broker, which uses the certificate for authentication purposes and therefore makes use of the start_date and expiry_date.

      I hope this information helps,
      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, October 15, 2009 12:49 AM
  • Really?  What is the point of having start & expiry dates on the certificate at all? 

    I answered this and other related points here: http://blogs.msdn.com/lcris/archive/2007/10/04/sql-server-2005-a-note-about-the-use-of-certificates.aspx
    Unless I'm doing something drastically wrong you cant set expiry date at all. 
    DROP CERTIFICATE DavesCertificate 
    
    
    
    CREATE CERTIFICATE DavesCertificate WITH SUBJECT = 'Test',
    
    EXPIRY_DATE = '01/01/2050', START_DATE = '10/15/2009'
    
    
    
    SELECT name, subject, expiry_date, start_date  
    
    FROM sys.certificates
    
    WHERE name = 'DavesCertificate'
    
    

    Which returns...
    DavesCertificate Test 2010-10-15 00:00:00.000 2009-10-15 00:00:00.000




    Reagrds
    Dave  

    It looks like this behavior is a bug and the expiry_date is ignored if it precedes the start_date. Changing the order of the start/expiry dates seems to work as expected, so you can use that to get the certificate created with the proper dates. I.e., this will work as expected:

    CREATE

     

    CERTIFICATE DavesCertificate2 WITH SUBJECT = 'Test',
    START_DATE = '10/15/2009', EXPIRY_DATE = '01/01/2050'

    I'll point this issue to the security team, but I suggest you also open a report using the instructions from the sticky thread in this forum - issues reported by customers are treated with higher priority. Thanks for catching this!


    This post is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Alex Feng (SQL) Thursday, October 15, 2009 6:22 AM
    • Marked as answer by york0001 Thursday, October 15, 2009 4:28 PM
    Thursday, October 15, 2009 12:59 AM
  • Replying to an old post , to facilitate searchs

    Certificate expirty date will be validated for Mirroring and Service Broker

    But you can replace the end certificates

    http://rusanu.com/2008/10/25/replacing-endpoint-certificates-that-are-near-expiration/
    Blog: http://dineshasanka.spaces.live.com
    Thursday, February 18, 2010 5:17 AM