locked
Certificates and Key rotation... RRS feed

  • Question

  • We are planning to use TDE to encrypt a database for compliance reasons.  As part of the compliance, we must also rotate keys on a yearly basis. 

     

    Our security advisor believes that if we rotate the asymmetric key pair that guards the symmetric key, we should satisfy the compliance requirement without actually decrypting and re-encrypting all of the data. In SS08 speak, i want to create a new certificate yearly.  With that new CERTIFICATE, I want to reencrypt the DEK.


    Can this be done?

     

    Also, as a simpler trailing question, we actually have 3 databases we're going to encrypt.  Any arguments for or against using one certificate for all 3?

     

    Kevin

     

    Wednesday, August 27, 2008 10:01 PM

Answers

  •    I will start with the SQL Server 2008 TDE key rotation, as this is an easy answer. You can specify a different certificate to be used for the database DEK using ALTER DATABASE ENCRYPTION KEY. For example:

     

    USE [master]

    go

    -- Create certificate for DEK protection

    --

    CREATE CERTIFICATE [DekCert_db_Demo_2008]

      WITH SUBJECT = 'DEK protection certificate for db_Demo DB [db_Demo]'

    go

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

    -- IMPORTANT SECURITY NOTE

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

    -- Make sure to back up the certificate and the pvk

    -- The only mechanism to recover the DEK is via this certificate private key

    -- if this certificate is lost, all the data would be lost

    --

    -- Keep this certificate backup in a safe location so it

    -- can be recovered in case of an emergency, but

    -- making sure it is physically safe from an adversary

    --

    BACKUP CERTIFICATE [DekCert_db_Demo_2008]

      TO FILE = 'SAFE_LOCATION\DekCert_db_Demo_2008.0829.cer'

      WITH PRIVATE KEY

        (FILE = 'SAFE_LOCATION\DekCert_db_Demo_2008.0829.pvk',

         ENCRYPTION BY PASSWORD = 'di5@st3r R3c0verY p@zzword')

    go

     

    use [db_Demo]

    go

     

    CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = AES_256

    ENCRYPTION BY SERVER CERTIFICATE [DekCert_db_Demo_2008]

    go

    ALTER DATABASE [db_Demo] SET ENCRYPTION ON

    go

     

    -- Check the DEK state, dates and encryptor thumbprint

    --

    SELECT * FROM sys.dm_database_encryption_keys

     

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

    -- Advance "make-belive clock" 1 year

    -- it is time to regenerate the keys

    --

    use [master]

    go

     

    -- Create a new certificate for DEK protection & back it up

    --

    CREATE CERTIFICATE [DekCert_db_Demo_2009]

      WITH SUBJECT = 'DEK protection certificate for db_Demo DB [db_Demo]'

    go

    BACKUP CERTIFICATE [DekCert_db_Demo_2009]

      TO FILE = 'SAFE_LOCATION\DekCert_db_Demo_2009.0829.cer'

      WITH PRIVATE KEY

        (FILE = 'SAFE_LOCATION\DekCert_db_Demo_2009.0829.pvk',

         -- Notice that the password also changed,

         -- consider passwords keys and also have a password rotation policy

         ENCRYPTION BY PASSWORD = 'N3W d1Zast3R r3c0Very p4sswoRd')

    go

     

    use [db_Demo]

    go

     

    -- Two options:

    -- All in one shot...

    --

    ALTER DATABASE ENCRYPTION KEY

    REGENERATE

    WITH ALGORITHM = AES_256

    ENCRYPTION BY SERVER CERTIFICATE [DekCert_db_Demo_2009]

    go

    SELECT * FROM sys.dm_database_encryption_keys

    go

     

    -- ... or you can do it in two parts

    --

     

    -- Rotate DEK (probably a more common operation by itself)

    --

    ALTER DATABASE ENCRYPTION KEY

    REGENERATE

    WITH ALGORITHM = AES_256

    go

    -- Rotate certificate protecting DEK

    --

    ALTER DATABASE ENCRYPTION KEY

    ENCRYPTION BY SERVER CERTIFICATE [DekCert_db_Demo_2009]

    go

    SELECT * FROM sys.dm_database_encryption_keys

    go

     

      Remember that rotating the DEK or the certificate protecting the DEK will start a new re-encryption scan and while this scan is in progress: there will be some background activity (I/O and CPU bound) and maintenance activities on the database will be disabled until the scan ends. You should plan your key rotation schedules considering this overhead.

     

      Regarding the question of whether it is OK to protect multiple DEKs with a single certificate, it would really depend on your case. From what I can understand of your scenario, you have a small number of DBs you want to protect, and most likely it would be the same set of people who have to access and manage all of the DEKs; in such case, and considering that you have a good audit and rotation policies and that your security advisor seems to be OK with this schema, I personally don't see anything wrong with such approach and it could make management of the DEKs easier for you.

     

       Going back to the SQL Server 2005 key hierarchy. Key rotation of the asymmetric key/certificate protecting the symmetric is highly recommended and in some cases it may be a good-enough solution for key rotation, but  it really depends on the nature of your data and the kind of threats you are defending against.

     

      In some cases, you may also want to consider key rotation for the symmetric keys. Symmetric key rotation is not always easy because, as you already mentioned, it requires to decrypt and re-encrypt all data and may cause the data to become off-line for some time and/or may require changes in the application.

     

      If you are using the symmetric keys to protect data as a defense in depth along with TDE, I would say that it sounds like the benefits vs. cost of symmetric key rotation would be minimal (assuming such encrypted data is not leaked or moved across non-protected databases or other repositories) as long as you have a good TDE key rotation policy.

     

      I hope this information helps. We are currently working in an article (we want to write it in a white paper format) that talks about key rotations, please consider my comments and demo above a preview of this article. We will also appreciate any feedback you may have on this information and/or the demo I included.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

     

    Friday, August 29, 2008 7:32 PM

All replies

  •    I will start with the SQL Server 2008 TDE key rotation, as this is an easy answer. You can specify a different certificate to be used for the database DEK using ALTER DATABASE ENCRYPTION KEY. For example:

     

    USE [master]

    go

    -- Create certificate for DEK protection

    --

    CREATE CERTIFICATE [DekCert_db_Demo_2008]

      WITH SUBJECT = 'DEK protection certificate for db_Demo DB [db_Demo]'

    go

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

    -- IMPORTANT SECURITY NOTE

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

    -- Make sure to back up the certificate and the pvk

    -- The only mechanism to recover the DEK is via this certificate private key

    -- if this certificate is lost, all the data would be lost

    --

    -- Keep this certificate backup in a safe location so it

    -- can be recovered in case of an emergency, but

    -- making sure it is physically safe from an adversary

    --

    BACKUP CERTIFICATE [DekCert_db_Demo_2008]

      TO FILE = 'SAFE_LOCATION\DekCert_db_Demo_2008.0829.cer'

      WITH PRIVATE KEY

        (FILE = 'SAFE_LOCATION\DekCert_db_Demo_2008.0829.pvk',

         ENCRYPTION BY PASSWORD = 'di5@st3r R3c0verY p@zzword')

    go

     

    use [db_Demo]

    go

     

    CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = AES_256

    ENCRYPTION BY SERVER CERTIFICATE [DekCert_db_Demo_2008]

    go

    ALTER DATABASE [db_Demo] SET ENCRYPTION ON

    go

     

    -- Check the DEK state, dates and encryptor thumbprint

    --

    SELECT * FROM sys.dm_database_encryption_keys

     

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

    -- Advance "make-belive clock" 1 year

    -- it is time to regenerate the keys

    --

    use [master]

    go

     

    -- Create a new certificate for DEK protection & back it up

    --

    CREATE CERTIFICATE [DekCert_db_Demo_2009]

      WITH SUBJECT = 'DEK protection certificate for db_Demo DB [db_Demo]'

    go

    BACKUP CERTIFICATE [DekCert_db_Demo_2009]

      TO FILE = 'SAFE_LOCATION\DekCert_db_Demo_2009.0829.cer'

      WITH PRIVATE KEY

        (FILE = 'SAFE_LOCATION\DekCert_db_Demo_2009.0829.pvk',

         -- Notice that the password also changed,

         -- consider passwords keys and also have a password rotation policy

         ENCRYPTION BY PASSWORD = 'N3W d1Zast3R r3c0Very p4sswoRd')

    go

     

    use [db_Demo]

    go

     

    -- Two options:

    -- All in one shot...

    --

    ALTER DATABASE ENCRYPTION KEY

    REGENERATE

    WITH ALGORITHM = AES_256

    ENCRYPTION BY SERVER CERTIFICATE [DekCert_db_Demo_2009]

    go

    SELECT * FROM sys.dm_database_encryption_keys

    go

     

    -- ... or you can do it in two parts

    --

     

    -- Rotate DEK (probably a more common operation by itself)

    --

    ALTER DATABASE ENCRYPTION KEY

    REGENERATE

    WITH ALGORITHM = AES_256

    go

    -- Rotate certificate protecting DEK

    --

    ALTER DATABASE ENCRYPTION KEY

    ENCRYPTION BY SERVER CERTIFICATE [DekCert_db_Demo_2009]

    go

    SELECT * FROM sys.dm_database_encryption_keys

    go

     

      Remember that rotating the DEK or the certificate protecting the DEK will start a new re-encryption scan and while this scan is in progress: there will be some background activity (I/O and CPU bound) and maintenance activities on the database will be disabled until the scan ends. You should plan your key rotation schedules considering this overhead.

     

      Regarding the question of whether it is OK to protect multiple DEKs with a single certificate, it would really depend on your case. From what I can understand of your scenario, you have a small number of DBs you want to protect, and most likely it would be the same set of people who have to access and manage all of the DEKs; in such case, and considering that you have a good audit and rotation policies and that your security advisor seems to be OK with this schema, I personally don't see anything wrong with such approach and it could make management of the DEKs easier for you.

     

       Going back to the SQL Server 2005 key hierarchy. Key rotation of the asymmetric key/certificate protecting the symmetric is highly recommended and in some cases it may be a good-enough solution for key rotation, but  it really depends on the nature of your data and the kind of threats you are defending against.

     

      In some cases, you may also want to consider key rotation for the symmetric keys. Symmetric key rotation is not always easy because, as you already mentioned, it requires to decrypt and re-encrypt all data and may cause the data to become off-line for some time and/or may require changes in the application.

     

      If you are using the symmetric keys to protect data as a defense in depth along with TDE, I would say that it sounds like the benefits vs. cost of symmetric key rotation would be minimal (assuming such encrypted data is not leaked or moved across non-protected databases or other repositories) as long as you have a good TDE key rotation policy.

     

      I hope this information helps. We are currently working in an article (we want to write it in a white paper format) that talks about key rotations, please consider my comments and demo above a preview of this article. We will also appreciate any feedback you may have on this information and/or the demo I included.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

     

    Friday, August 29, 2008 7:32 PM
  • what if I don't want to regenerate the DEK again and only want to rotate the KEK (Key encrypting key, in this case, certificate)?  I only want to rotate the certificate that is used to encypte DEK.   I want to keey the same DEK since I don't want to have progressive scan of the data file again (Files are huge and take long time. don't have enough time to do so).  For compliance purpose, rotating KEK is enough to us.
    Tuesday, November 2, 2010 7:42 PM

  • I have set up database mirroring for TDE enabled database and they worked fine as far as I got all the key infrastructure identical on both Principal and mirrored SQL servers. Now it is coming to our yearly key rotation practice for compliance.  After I regenerate the DEK on principal server and also encrypt it with a newer certificate,  the mirroring is suspended and I am not able to resume it any more.  (Although I have added this newer certificate to the mirrored server too.) It is understandable because now the DEK is out of sync.  However, what are the correct steps to do the key rotation in the mirroring scenario? The bottom line is:  I DO NOT want to set up mirroring again since our backups are huge and take very long time to copy the backups from principal server to mirrored server. thanks for any input in advance.

    Friday, March 1, 2013 10:37 PM