locked
ALTER the Expiration date of my CERTIFICATE (TDE SQL2008) RRS feed

  • Question

  • Hello ,

    after i created a DMK & CERTIficate and enabled TDE , i noticed my certificate will expire after one year , and i know that won't enforce any expiration for my certificate and it should be worke normaly even after the expiration date.. but my boss asked me to change the expiration date .. is it possible ?

    I have SQL 2008 x64 Ent Clustered..

    Tuesday, September 11, 2012 10:45 AM

Answers

  • Hi SQL Kitchen,

    Certificate expiration is not enforced when the certificate is used for encryption. It is point less to only create a create to set the expiry, if the certificate is used for TDE.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by SQL Kitchen Sunday, September 16, 2012 12:08 PM
    Wednesday, September 12, 2012 1:25 PM
  • Try like this :-

    • Set database encryption off
    • drop database encryption key
    • drop certificate at master
      create certificate with below command to have new expiry date
      CREATE CERTIFICATE <Certificate Name> with subject = 'Certificate Subject'
    • START_DATE = '9/10/2012',EXPIRY_DATE='9/16/2050';
    • create database encryption key
    • Set database encryption on

    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Tuesday, September 11, 2012 8:21 PM

All replies

  • drop the certificate  and recreate the certificate with a new expiry date, you can set own exipry date


    Ramesh Babu Vavilla MCTS,MSBI

    • Proposed as answer by Ramesh Babu Vavilla Wednesday, September 12, 2012 8:55 AM
    • Unproposed as answer by SQL Kitchen Sunday, September 16, 2012 12:08 PM
    Tuesday, September 11, 2012 10:53 AM
  • check this blog for soluion

    http://rusanu.com/2008/11/26/replacing-service-certificates-that-are-near-expiration/

    drop the certificate and re create a new certificate with your custom expiry date


    Ramesh Babu Vavilla MCTS,MSBI

    • Proposed as answer by Ramesh Babu Vavilla Wednesday, September 12, 2012 8:55 AM
    • Marked as answer by SQL Kitchen Sunday, September 16, 2012 12:08 PM
    • Unmarked as answer by SQL Kitchen Sunday, September 16, 2012 12:08 PM
    Tuesday, September 11, 2012 10:53 AM
  • Try like this :-

    • Set database encryption off
    • drop database encryption key
    • drop certificate at master
      create certificate with below command to have new expiry date
      CREATE CERTIFICATE <Certificate Name> with subject = 'Certificate Subject'
    • START_DATE = '9/10/2012',EXPIRY_DATE='9/16/2050';
    • create database encryption key
    • Set database encryption on

    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Tuesday, September 11, 2012 8:21 PM
  • Hi SQL Kitchen,

    Certificate expiration is not enforced when the certificate is used for encryption. It is point less to only create a create to set the expiry, if the certificate is used for TDE.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by SQL Kitchen Sunday, September 16, 2012 12:08 PM
    Wednesday, September 12, 2012 1:25 PM
  • Unfortunately Certificates stand alone but can have dependancies on other objects.

    There are number of problems with how microsoft by default has certificates with the Expiry_date set to one year from the creation date.

    If the certificate is shared between TDE and other services such as mirroring you now have a problem without knowing it. 

    I consider this more of a bug than a feature.  Who in there right mind thinks a database action by default should only last a year?

    You can't drop the certificate as long as it is being used by another service like replication or mirroring.

    Insteading of setting the expiration date to say 100 years by default, TDE just ignores the expiry_date, a have fix.

    Tuesday, August 13, 2013 2:37 PM