Which is better, certificates OR asymmetric keys for encrypting symmetric keys used for data encryption?

  • In http://msdn.microsoft.com/en-us/library/cc837966.aspx it is stated that, in terms of encrypting symmetric keys used for data encryption:

    In practice, Certificate objects are always recommended over AsymmetricKey objects

    Can anyone please provide a justification as to why that should be so?

    Is it because of increased security, performance or both?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Marios Philippopoulos (4/9/2009)


    In http://msdn.microsoft.com/en-us/library/cc837966.aspx it is stated that, in terms of encrypting symmetric keys used for data encryption:

    In practice, Certificate objects are always recommended over AsymmetricKey objects

    Can anyone please provide a justification as to why that should be so?

    Is it because of increased security, performance or both?

    From the same article:

    There is little difference between a SQL Server certificate and a SQL Server asymmetric key; the primary difference is that the asymmetric key cannot be exported. For this reason, they are treated as the same thing in this discussion. Create Certificate objects unless you have a reason to prefer an asymmetric key.

    That's why. You have a recovery option with certificates that you don't with asymmetric keys.

    K. Brian Kelley
    @kbriankelley

  • K. Brian Kelley (4/9/2009)


    Marios Philippopoulos (4/9/2009)


    In http://msdn.microsoft.com/en-us/library/cc837966.aspx it is stated that, in terms of encrypting symmetric keys used for data encryption:

    In practice, Certificate objects are always recommended over AsymmetricKey objects

    Can anyone please provide a justification as to why that should be so?

    Is it because of increased security, performance or both?

    From the same article:

    There is little difference between a SQL Server certificate and a SQL Server asymmetric key; the primary difference is that the asymmetric key cannot be exported. For this reason, they are treated as the same thing in this discussion. Create Certificate objects unless you have a reason to prefer an asymmetric key.

    That's why. You have a recovery option with certificates that you don't with asymmetric keys.

    Thank you, but it seems to me that being able to export them makes certificates less secure than asymmetric keys. Isn't that the case?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Marios Philippopoulos (4/9/2009)


    Thank you, but it seems to me that being able to export them makes certificates less secure than asymmetric keys. Isn't that the case?

    Yes and no. Part of the C-I-A Triad in security is Availability. So yes, it means that someone is more likely to be able to get access to the symmetric key and therefore decrypt the data. However, you can put controls in place to mitigate this (just like you do to protect backups, sensitive passwords, etc.). By being able to export the certificates to disk, you now have a recovery option. Which means if something happens, you have availability, whereas with an asymmetric key you wouldn't.

    K. Brian Kelley
    @kbriankelley

  • K. Brian Kelley (4/9/2009)


    Marios Philippopoulos (4/9/2009)


    Thank you, but it seems to me that being able to export them makes certificates less secure than asymmetric keys. Isn't that the case?

    Yes and no. Part of the C-I-A Triad in security is Availability. So yes, it means that someone is more likely to be able to get access to the symmetric key and therefore decrypt the data. However, you can put controls in place to mitigate this (just like you do to protect backups, sensitive passwords, etc.). By being able to export the certificates to disk, you now have a recovery option. Which means if something happens, you have availability, whereas with an asymmetric key you wouldn't.

    Makes sense, thanks for explaining this.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • What do the other 2 letters stand for in C-I-A?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Confidentiality - Ensuring only authorized personnel/services can see the data.

    Integrity - Ensuring only authorized personnel/services can modify the data through authorized processes.

    The CIA Triad

    K. Brian Kelley
    @kbriankelley

  • So I have backed up my certificate (certificate protecting the private key of a symmetric key through which I en(de)crypt data):

    use encryption_test;

    BACKUP CERTIFICATE ct_data_access

    TO FILE = 'C:\_MARIOS\BACKUPS\encryption_test_db_ct_data_access'

    WITH PRIVATE KEY

    (

    FILE = 'C:\_MARIOS\BACKUPS\encryption_test_db_ct_data_access_key'

    ,ENCRYPTION BY PASSWORD = '%T^Y&U*IF$#@GT%');

    The symmetric key is related to the certificate like this:

    CREATE SYMMETRIC KEY

    sk_data_access

    WITH

    ALGORITHM = AES_256

    ENCRYPTION BY

    CERTIFICATE ct_data_access

    GO

    I then restore it on another db on the same instance:

    USE encr_restore;

    IF NOT EXISTS (

    SELECT * FROM sys.symmetric_keys

    WHERE name = '##MS_DatabaseMasterKey##')

    BEGIN

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'b^d$H^%$#kofdYUH&^%';

    END

    ELSE

    BEGIN

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'b^d$H^%$#kofdYUH&^%';

    END

    CREATE CERTIFICATE ct_data_access2

    FROM FILE = 'C:\_MARIOS\BACKUPS\encryption_test_db_ct_data_access'

    WITH PRIVATE KEY

    (

    FILE = 'C:\_MARIOS\BACKUPS\encryption_test_db_ct_data_access_key'

    ,DECRYPTION BY PASSWORD = '%T^Y&U*IF$#@GT%');

    Now I'm not sure what to do next.

    How can I retrieve the symmetric key (protected by the certificate) in the new database?

    I'm not sure what I have achieved by backing up the certificate...

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • "Yes and no. Part of the C-I-A Triad in security is Availability. So yes, it means that someone is more likely to be able to get access to the symmetric key and therefore decrypt the data. However, you can put controls in place to mitigate this (just like you do to protect backups, sensitive passwords, etc.). By being able to export the certificates to disk, you now have a recovery option. Which means if something happens, you have availability, whereas with an asymmetric key you wouldn't."

    That all depends on how you integrate your Asymmetric key.

    As stated Certs are less secure, but Keys are less available, however, if your Asymetric key is in a KMS/HSM, and has things like Quorum (Zero Trust Methodology to prevent a key from being deleted on purpose, accident or maliciously, a key undo or soft delete policy, and multiple points of failure and high availability then your Asymetric approach becomes more secure, more available, if not more so since Certs need to be backed up or master DB needs to be backed up and restored. Plus, depending on how you implement it could also add RBAC access to the key, and logging of who used the key, where is the cert there is no means to track or limit who uses it.

    • This reply was modified 1 year, 1 month ago by  SecurityDBA.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply