locked
Always Encrypted Key Best Practices RRS feed

  • Question

  • Hi, I am looking for some best practices/rules of thumb around creating and managing keys. I have a medium sized database with a fairly small number of encrypted tables currently (probably will have 3 or 4 tables using encryption, a few columns in each) and I have it working (thanks to some help on these forums) but there are a few areas I'm not sure if how I am doing it is correct or ideal. The main issue so far has been the fact that we have a number of environments so the more keys I create I have to multiply that work by 5 (qa/stage/prod/demo/disaster recovery) so I'm hesitant to create master and column encryption keys for each table. 

    1. What would some guidelines for how many column master keys I should have for a given database? Given that they would all exist in the same place and likely would be accessed by the same principals (azure key vault), if one were compromised I would expect them all to be compromised. However, if I reuse a column encryption key for multiple columns I don't know if that would make it more possible to reverse-engineer the master key as well?

    2. Similar to question 1, what are some guidelines for when it would be OK to use the same column encryption key for multiple columns and/or tables vs when I would want to create a new column encryption key? One end of the spectrum would be I have 1 master and 1 encryption key that I re-use across several tables and the other end would be a master/column encryption key pair for each table.

    My feeling is that a good balance would be 1 master key and then a different column encryption key for each table, but do the benefits of this outweigh the effort of having to create the extra keys? The only reason that more column encryption keys is that much extra work is that it is not easy to apply the correct column encryption key for each database deployment within the same database project. 

    Thanks


    Thursday, February 9, 2017 8:36 PM

Answers

  • Hi questionasker1,

    Multiple Column encryption keys are designed for Role Separation rather than avoiding reverse-engineering. Theoretically speaking, the Column encryption keys is not easy to be reverse-engineered. Column encryption keys used with the AES-256 algorithm are 256-bit long, it is very safe and not easy to break.

    For the second question, based on the principle of encryption, we will not see a significant impact after using multiple Column encryption keys.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by questionasker1 Friday, February 10, 2017 2:56 PM
    Friday, February 10, 2017 8:04 AM

All replies

  • Hi questionasker1,

    Multiple Column encryption keys are designed for Role Separation rather than avoiding reverse-engineering. Theoretically speaking, the Column encryption keys is not easy to be reverse-engineered. Column encryption keys used with the AES-256 algorithm are 256-bit long, it is very safe and not easy to break.

    For the second question, based on the principle of encryption, we will not see a significant impact after using multiple Column encryption keys.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by questionasker1 Friday, February 10, 2017 2:56 PM
    Friday, February 10, 2017 8:04 AM
  • However, if I reuse a column encryption key for multiple columns I don't know if that would make it more possible to reverse-engineer the master key as well?

    No, because the CMK is not directly used to encrypt column values. The CMK is used to encrypt the CEK in database metadata and by clients to determine the CEK for encryption and decryption. Since the CMK is not used directly to encrypt column/parameter values, it can't be reverse engineered from the encrypted column value.

    Regarding your options, the extremes are to create a different CMK/CEK pair for each encrypted column even for multiple columns in the same table versus a single pair for all columns regardless of table. I understand your key management concerns and would opt for the middle ground of aligning keys by roles as Teige suggested. For example, you could create a CMK for HR (NPI information) and another for customer support (credit card number). In my opinion, there isn't much value in creating separate CEKs for different columns. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 10, 2017 1:04 PM
  • Thank you for your help, as of now we do not have enough different roles to justify different column encryption keys but as we add new roles I will follow your recommendations. 
    Friday, February 10, 2017 8:27 PM