locked
algorithm used for protecting private keys of certificate RRS feed

  • Question

  • I am using SQL SERVER 2008 R2.

    Can anyone tell me what algorithm is used to protect the private keys of certificate (self signed, sql server generated private keys)

    If the certificate is protected by password?

    If the certificate is protected by database master key?

    thanks

    Thursday, November 6, 2014 9:40 PM

Answers

  • Hi Phani vanka,

    Quote: In the example you have given yes certificate is protected by database master key. But what algorithm is used to encrypt the private keys?
    Supported encryption algorithms in SQL Server Transparent Data Encryption (TDE) are AES with 128-bit, 192‑bit, or 256‑bit keys or 3 Key Triple DES. And we can set the algorithm we want to encrypt the private keys by using the following query:

    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE MyCertificate;
    GO

    If you have any question, please feel free to let me know.

    Regards,
    Jerry Li

    Sunday, November 9, 2014 7:45 AM
  • >What algorithm does sql server use to protect certificates with master key?

    This is not explicitly documented, but you can infer that answer is AES.

    "SQL Server 2014 uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). "

    Service Master Key

    It's implied by the statement that SQL Server uses AES to protect the Database Master Keys (DMK).   The SMK is used to protect the DMKs (Encryption Hierarchy), so logically the SMK must use AES.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, November 11, 2014 4:37 PM

All replies

  • Hi Phani vanka,

    According to your description, you want to know how the private keys of certificate is protected in SQL Server. Right?

    We can find out the answer of this question by going through the steps to use Transparent Data Encryption (TDE) in SQL Server. If we want to use a certificate to encrypt a database, we need to follow steps below:

    1. Create a master key
    2. Create or obtain a certificate protected by the master key
    3. Create a database encryption key and protect it by the certificate
    4. Set the database to use encryption

    So according to step 2, we can see that the certificate is protected by database master key.

    For more details about these steps and how Transparent Data Encryption (TDE) works, please refer to the following article: http://msdn.microsoft.com/en-us/library/bb934049.aspx

    If you have any question, please feel free to let me know.

    Regards,
    Jerry Li

    Friday, November 7, 2014 8:54 AM
  • Thanks Jerry for the information. I looked at that link and I dont believe it has the answer to my question.

    In the example you have given yes certificate is protected by database master key. But what algorithm is used to encrypt the private keys?

    If you look at "Create Certificate" command (http://msdn.microsoft.com/en-us/library/ms187798.aspx) you can notice that we have to option to protect certificate by a password. What algorithm is used to encrypt private keys in this case?

    if you look at "Create symmetric key" documentation, it clearly states that when a symmetric key is protected by password TDES is used to protect the key. I dont see any algorithm mentioned for protecting the keys of a certificate. http://technet.microsoft.com/en-us/library/ms188357(v=sql.120).aspx

    Friday, November 7, 2014 2:31 PM
  • Hi Phani vanka,

    Quote: In the example you have given yes certificate is protected by database master key. But what algorithm is used to encrypt the private keys?
    Supported encryption algorithms in SQL Server Transparent Data Encryption (TDE) are AES with 128-bit, 192‑bit, or 256‑bit keys or 3 Key Triple DES. And we can set the algorithm we want to encrypt the private keys by using the following query:

    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE MyCertificate;
    GO

    If you have any question, please feel free to let me know.

    Regards,
    Jerry Li

    Sunday, November 9, 2014 7:45 AM
  • what about when certificate is protected by password?
    Monday, November 10, 2014 7:35 PM
  • Certificates are protected by master key.

    In any cases...

    SQL Server allows administrators and developers to choose from among several algorithms, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.

    Please refer 

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

    --Prashanth

    Monday, November 10, 2014 7:48 PM
  • thanks for the info. I understand sql server provides different algorithms to encrypt the data. I am interested in finding out the encryption algorithm used for protecting the keys themselves.

    As you said certificates are protected by master key. What algorithm does sql server use to protect certificates with master key? I dont see an option in "create certificate" command that allows you to specify the algorithm used to protect the private keys of certificate themselves.

    Also, certificate can be protected by password as well. What algorithm does sql server use to protect certificate when a password is used.

    Tuesday, November 11, 2014 3:09 PM
  • I don't think any algorithm is required to protect the certificate. it's just secured with password. 

    We can create two types of certificates, one is protected by master key and other is self signed certificated protected with password.

    The algorithms are used to encrypt data and mainly for application compatibility. The application should be able to decrypt the data without much complexities.

    This is my understanding. Lets wait for other experts to give their opinion:-)

    --Prashanth


    Tuesday, November 11, 2014 4:23 PM
  • >What algorithm does sql server use to protect certificates with master key?

    This is not explicitly documented, but you can infer that answer is AES.

    "SQL Server 2014 uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). "

    Service Master Key

    It's implied by the statement that SQL Server uses AES to protect the Database Master Keys (DMK).   The SMK is used to protect the DMKs (Encryption Hierarchy), so logically the SMK must use AES.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, November 11, 2014 4:37 PM