locked
TDE Certificate and the Private Key RRS feed

  • Question

  • Folks,

    Trying to get my head round TDE in SQL Server 2008.

    Can anyone explain the role of the private key in TDE certificates

    When I back up and recreate the TDE certificate I do so with a private key.

    i.e.

    BACKUP CERTIFICATE TestSQLServerCert 
    TO FILE = 'TestSQLServerCert'
    WITH PRIVATE KEY 
    (
        FILE = 'SQLPrivateKeyFile',
        ENCRYPTION BY PASSWORD = '**************'
    );
    GO

    What is this key? when is it created? what is it protecting and is there a corresponding public key if so where is it located?

    I read here that this is a symmetric key and here that there is both public and private keys?

    Any help would be greatly appreciated

    James


    • Edited by James_Quinn Friday, February 10, 2012 2:19 PM minor correction
    Friday, February 10, 2012 10:16 AM

Answers

  • A certificate has a pair of keys: public and private.

    The public key is used to protect the database encryption key.

    You will need both the certificate file and the private key file in order to recover the certificate. This is the case e.g. when you need to move a TDE enabled database to another instance.

    Quoting Il-Sung Lee: "While the public key of the certificate is used to encrypt the DEK, the private key is needed in order to decrypt the DEK.  Hence, without the private key, SQL Server cannot decrypt the DEK which means that it cannot decrypt the database."

    You can use the following statements to gather information about your certificates and keys:

    select * from sys.symmetric_keys
    select * from sys.asymmetric_keys
    select * from sys.certificates


    SQL Server Database Administrator


    • Edited by Marcelo Farinelli Friday, February 10, 2012 5:36 PM added SQL statements
    • Proposed as answer by Marcelo Farinelli Wednesday, February 15, 2012 8:45 PM
    • Marked as answer by James_Quinn Friday, February 17, 2012 10:34 AM
    Friday, February 10, 2012 5:27 PM

All replies

  • A certificate has a pair of keys: public and private.

    The public key is used to protect the database encryption key.

    You will need both the certificate file and the private key file in order to recover the certificate. This is the case e.g. when you need to move a TDE enabled database to another instance.

    Quoting Il-Sung Lee: "While the public key of the certificate is used to encrypt the DEK, the private key is needed in order to decrypt the DEK.  Hence, without the private key, SQL Server cannot decrypt the DEK which means that it cannot decrypt the database."

    You can use the following statements to gather information about your certificates and keys:

    select * from sys.symmetric_keys
    select * from sys.asymmetric_keys
    select * from sys.certificates


    SQL Server Database Administrator


    • Edited by Marcelo Farinelli Friday, February 10, 2012 5:36 PM added SQL statements
    • Proposed as answer by Marcelo Farinelli Wednesday, February 15, 2012 8:45 PM
    • Marked as answer by James_Quinn Friday, February 17, 2012 10:34 AM
    Friday, February 10, 2012 5:27 PM
  • Do you know where i can view this TDE certificates private key?

    I have tried

    select * from sys.symmetric_keys - Shows the DMK and SMK

    select * from sys.asymmetric_keys - Nothing

    select * from sys.certificates - I am able to see the TDE Certificate

    Thanks

    James


    • Edited by James_Quinn Monday, February 20, 2012 11:02 AM
    Monday, February 20, 2012 11:02 AM