none
Need some validation concering TDE Certificate expiration

    Question

  • This was posted as a reply from Laurentiu Cristophor about TDE Certificate expiration. We have a DB with and expired certificate. We thought it would be good to create a new certificate with a future expiry date, however when we remove the old cert from the DB, backups made since creating the new cert will not restore without the old cert as well. Is there somewhere that MS has a definitive position on:

    1. Expiry date doesn't matter and the only consequence is during a restore "Your cert is expired"

    2. If 1 is not true how do you replace an expire certificate?

     

    Laurentiu Cristofor<abbr class="affil">Moderator</abbr>Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals

     
    note that expiration dates are not enforced for certificates - you can continue to decrypt with a certificate even if it is expired.

    Martin G. Bradburn
    Thursday, June 09, 2011 8:17 PM

Answers

  • This was a topic of discussion I had with fellow MVP Greg Low and eventually the Product Support team and Development Team at Microsoft two years ago at PASS Summit 2009.  The answer is number 1, the certificate expiration does not get checked for TDE and you just have to deal with the warning.  You don't have to do anything to change out the certificates for TDE like you would for Service Broker or Database Mirroring.  If you would like Microsoft confirmation of this, post back here and I will escalate this post and ask some of the Microsoft team members I know on here to reply to it.

    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Stephanie Lv Tuesday, June 21, 2011 2:32 AM
    Monday, June 13, 2011 3:06 AM
    Moderator
  • Hi Martin,

    According to your description, there are steps to remove the expired certificate and create a new one.

    First, disable the database encryption key on the user-defined database, and then drop it.
    USE <DATABASENAME>;
    go
    
    ALTER DATABASE <DATABASENAME>
    SET ENCRYPTION OFF
    go
    
    DROP DATABASE ENCRYPTION KEY;
    go
    
    

    Second, drop the expired certificate and create a new one.
    USE master;
    go
    
    DROP CERTIFICATE <CetificateName>;
    go
    
    CREATE CERTIFICATE <CetificateName> WITH SUBJECT = 'My TED Certificate'
    ,START_DATE = '12/1/2005',EXPIRY_DATE='6/11/2013';
    GO
    
    
    Finally, create the database encryption key again on the user-defined database and enable it.
    USE <DATABASENAME>;
    go
    
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE <CertificateName>
    GO
     
    ALTER DATABASE <DATABASENAME>
    SET ENCRYPTION ON
    GO
    
    After completing the steps below, the expired certificate will not effect on the TDE and you can restore the user-defined database with the new certificate to another server.
     

    Best Regards,
    Stephanie Lv

    • Marked as answer by Stephanie Lv Tuesday, June 21, 2011 2:32 AM
    Monday, June 13, 2011 2:45 AM

All replies

  • Hi Martin,

    According to your description, there are steps to remove the expired certificate and create a new one.

    First, disable the database encryption key on the user-defined database, and then drop it.
    USE <DATABASENAME>;
    go
    
    ALTER DATABASE <DATABASENAME>
    SET ENCRYPTION OFF
    go
    
    DROP DATABASE ENCRYPTION KEY;
    go
    
    

    Second, drop the expired certificate and create a new one.
    USE master;
    go
    
    DROP CERTIFICATE <CetificateName>;
    go
    
    CREATE CERTIFICATE <CetificateName> WITH SUBJECT = 'My TED Certificate'
    ,START_DATE = '12/1/2005',EXPIRY_DATE='6/11/2013';
    GO
    
    
    Finally, create the database encryption key again on the user-defined database and enable it.
    USE <DATABASENAME>;
    go
    
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE <CertificateName>
    GO
     
    ALTER DATABASE <DATABASENAME>
    SET ENCRYPTION ON
    GO
    
    After completing the steps below, the expired certificate will not effect on the TDE and you can restore the user-defined database with the new certificate to another server.
     

    Best Regards,
    Stephanie Lv

    • Marked as answer by Stephanie Lv Tuesday, June 21, 2011 2:32 AM
    Monday, June 13, 2011 2:45 AM
  • This was a topic of discussion I had with fellow MVP Greg Low and eventually the Product Support team and Development Team at Microsoft two years ago at PASS Summit 2009.  The answer is number 1, the certificate expiration does not get checked for TDE and you just have to deal with the warning.  You don't have to do anything to change out the certificates for TDE like you would for Service Broker or Database Mirroring.  If you would like Microsoft confirmation of this, post back here and I will escalate this post and ask some of the Microsoft team members I know on here to reply to it.

    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Stephanie Lv Tuesday, June 21, 2011 2:32 AM
    Monday, June 13, 2011 3:06 AM
    Moderator
  • I have same situation TDE certificate going to expire end of Feb 2014. I m thinking to use new one.what if i follow below step. Please suggest me. This is the correct way to renew TDE certificate or need to use other way. 

    1.

    CREATE CERTIFICATE New_MyServerCert_Name 
       WITH SUBJECT = 'New_My TDE Certificate', 
       EXPIRY_DATE = '2049-12-31';

    2.

    ALTER database encryption key 
    encryption by server certificate New_MyServerCert_name

    3.

    DROP CERTIFICATE OLD_MyServerCert_name

    Thank You,

    Eric

    Sunday, January 05, 2014 6:32 PM
  • Is there a KB or some article which specifically states that the TDE does not use the certificate expiration so that we can justify not changing it for PCI requirements to the business?
    Tuesday, March 21, 2017 4:20 AM