locked
Changing SQL Certificates and/or Symmetric Keys RRS feed

  • Question

  • Re: Changing SQL Certificates and/or Symmetric Keys

    We need to encrypt some sensitive data and are looking at SQL Column Level encryption, using symetric keys.

    For example, we are following this example

    http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/

    We are told that we might be required to change certificates and/or keys on a schedule, say every 1 year. How how do you change certificates and/or keys and what is the ramification for the data that is encrypted using them - say EncryptedColumn1? 

    -- Create self signed certificate
    USE encrypt_test;
    GO
    CREATE CERTIFICATE Certificate1
    WITH SUBJECT = 'Protect Data';
    GO
    -----------
    -- Create symmetric Key
    USE encrypt_test;
    GO
    CREATE SYMMETRIC KEY SymmetricKey1 
     WITH ALGORITHM = AES_128 
     ENCRYPTION BY CERTIFICATE Certificate1;
    GO
    -----------
    -- Populating encrypted data into new column
    USE encrypt_test;
    GO
    -- Opens the symmetric key for use
    OPEN SYMMETRIC KEY SymmetricKey1
    DECRYPTION BY CERTIFICATE Certificate1;
    GO
    UPDATE Customer_data
    SET Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number)
    FROM dbo.Customer_data;
    GO
    -- Closes the symmetric key
    CLOSE SYMMETRIC KEY SymmetricKey1;
    GO

    Thursday, October 16, 2014 2:43 PM

Answers

  • I think you are saying that to recycle the certificate (Certificate1) in my example to use anALTER SYMMETRIC KEY DROP ENCRYPTION  then ALTER SYMMETRIC KEY ADD ENCRYPTION and my column data will still be encrypted with the same key but built upon a different certificate.

    Yup.

    what happens when i need to recycle the key?

    Then it gets messy. You will need to descrypt and reencrypt with the new key.

    I'm not well versed in the requirements for which keys to recycle, but I note that in SQL Server, certificates has an expiration date. Symmetric keys have not. Also, certificates can be imported and be signed by a trusted provider. Symmetric keys are some random bits created in SQL Server. (With the exception of keys från EKM devices, which is a rare thing.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 17, 2014 9:17 PM

All replies

  • I don't see a reason to change the certificates and keys. May i know the reason for it?. Before doing you need to decrypt the encrypted column.

    close, drop the symmetric,certificate and create a new one.

    --Prashanth


    Thursday, October 16, 2014 3:22 PM
  • I work for a very large company. We have tons of security requirements. One of them is that certificates and keys must be changed annually.

    1) how do you change the certificate and/or key

    2) how does changing the certificate and/or key affect the encrypted data?

    Thursday, October 16, 2014 3:40 PM
  • You don't normally encrypt the data with a certificate, but you encrypt the data with a symmetric key. As long as you don't replace the symmertic key, you don't have bother about the data. What you use the certificate for is to encrypt the symmetric key.

    Wnen you replace the certificate, you first do ALTER SYMMETRIC KEY DROP ENCRYPTION and the drop the certificate and load the new one and then you use ALTER SYMMETRIC KEY ADD ENCRYPTION.

    The reason you don't encrypt data with the certificate directly is that this is a very expensive operation in terms of performance.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 16, 2014 9:28 PM
  • I don't have the terminology correct. but look at my code examples above. i believe that is encryption with a symmetric key. but the key is built upon a certificate. my work that all certificates and keys are recycled once annually. 

    I think you are saying that to recycle the certificate (Certificate1) in my example to use anALTER SYMMETRIC KEY DROP ENCRYPTION  then ALTER SYMMETRIC KEY ADD ENCRYPTION and my column data will still be encrypted with the same key but built upon a different certificate.

    what happens when i need to recycle the key?

     

    Friday, October 17, 2014 8:45 PM
  • I think you are saying that to recycle the certificate (Certificate1) in my example to use anALTER SYMMETRIC KEY DROP ENCRYPTION  then ALTER SYMMETRIC KEY ADD ENCRYPTION and my column data will still be encrypted with the same key but built upon a different certificate.

    Yup.

    what happens when i need to recycle the key?

    Then it gets messy. You will need to descrypt and reencrypt with the new key.

    I'm not well versed in the requirements for which keys to recycle, but I note that in SQL Server, certificates has an expiration date. Symmetric keys have not. Also, certificates can be imported and be signed by a trusted provider. Symmetric keys are some random bits created in SQL Server. (With the exception of keys från EKM devices, which is a rare thing.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 17, 2014 9:17 PM