none
TDE Certificates Private key

    Pertanyaan

  • Hi folks,

    Any ideas where i can gather info on the private key that is created when i create a TDE certificate.

    From

    http://msdn.microsoft.com/en-us/library/ms187798.aspx

    "when SQL Server generates a self-signed certificate, the private key is always created. By default, the private key is encrypted using the database master key"

    I have queried the sys.certificates,  sys.asymmetric_keys and sys.symmetric_keys and can see the  SMK and DMK in the master but I am unable to see the certificates private key. When i query the TDE Certificate in sys.certificates I see that the private key is encrypted by the DMK.

    Any thoughts



    21 Februari 2012 11:58

Jawaban

  • James,

    The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key.

    The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.

    SMK and DMK are not the same as private key, the private key is associated with the certificate, it was created when the certificate created.

    Or we can say that SMK is instance lever, DMK is database level, so they could be viewed by querying sys.symmetric_keys. But private key is a user level, it could not be viewed by querying system views.

    For using private key, we only need to backup it and encrypt it. 


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Disarankan sebagai Jawaban oleh yaphets 05 Maret 2012 9:42
    • Ditandai sebagai Jawaban oleh Iric WenModerator 07 Maret 2012 6:35
    02 Maret 2012 9:14

Semua Balasan

  • Hi James,

    What information do you want do get about private key? Since the private key would be backed up automatically when you created the TDE certificate, so there is no need to care about what is in the private key, we use it only by backing up and restoring it.

    Suggest you to see this article: Understanding Transparent Data Encryption (TDE)

    If anything unclear, please feel free to let me know.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    23 Februari 2012 3:01
  • Thanks for getting back Iric,

    It is to help my own understanding of TDE. I suppose 'see is believing', as mentioned I can see other keys in  the TDE Hierarchy  and how the TDE Certificate private key it is encrypted when i query sys.certificates.

    Thanks

    James

    23 Februari 2012 11:07
  • James,

    At the root of encryption tree is the Windows Data Protection API (DPAPI), which secures the key hierarchy at the machine level and is used to protect the service master key (SMK) for the database server instance. The SMK protects the database master key (DMK), which is stored at the user database level and which in turn protects certificates and asymmetric keys. These in turn protect symmetric keys, which protect the data. TDE uses a similar hierarchy down to the certificate. The primary difference is that when you use TDE, the DMK and certificate must be stored in the master database rather than in the user database. A new key, used only for TDE and referred to as the database encryption key (DEK), is created and stored in the user database.

    Query sys.certificates, you can get the information for private key as below:

    1. How the private key is encrypted

    2. Description of how the private key is encrypted.

    3. The date and time the certificate’s private key was last exported.

    You can refer to this article:

    http://msdn.microsoft.com/en-us/library/ms189774.aspx


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    29 Februari 2012 9:24
  • Thanks for the info Iric.

    Do you know where the TDE certificate's private key is stored?

    Thank

    James

    01 Maret 2012 11:05
  • James,

    If we want to get the private key, we need to back up the certificate and the private key to our hard disk first, because it is stored with the certificate, and we need to encrypt it while backing up. Please using the following statements:

    BACKUP CERTIFICATE sales05 TO FILE = 'c:\storedcerts\sales05cert'
        WITH PRIVATE KEY ( FILE = 'c:\storedkeys\sales05key' ,
        ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh' );
    GO

    For more information, please see: http://msdn.microsoft.com/en-us/library/ms178578.aspx


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    02 Maret 2012 1:46
  • Thanks for you help Iric, I appreciate it.

    My problem is that I am not able to see this private key prior to backing it up. From what I read on the subject  there is a private key created when the certificate is created. 

    But I am unable to see it using T-SQL.

    As mention earlier when I query the sys.asymmetric_keys and sys.symmetric_keys I see the other keys i.e. DMK and SMK and information on the certificate.

    Can this private key be viewed using T-SQL, or am i missing something very obvious?

    Thanks

    James

     

    02 Maret 2012 8:20
  • James,

    The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key.

    The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.

    SMK and DMK are not the same as private key, the private key is associated with the certificate, it was created when the certificate created.

    Or we can say that SMK is instance lever, DMK is database level, so they could be viewed by querying sys.symmetric_keys. But private key is a user level, it could not be viewed by querying system views.

    For using private key, we only need to backup it and encrypt it. 


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Disarankan sebagai Jawaban oleh yaphets 05 Maret 2012 9:42
    • Ditandai sebagai Jawaban oleh Iric WenModerator 07 Maret 2012 6:35
    02 Maret 2012 9:14