locked
Keeping encryption Symmetric Keys & Certificates safe from theft RRS feed

  • Question

  • Hi.  I am new to SQL encryption.  I have the syntax to create Master Key, Certificate, and Symmetric Keys; and to use EncryptByKey and DecryptByKey functions.  To my understanding, these keys and certificates are stored within the database (as you can select them from SYS.symmetric_keys for example, or see them under the security node).

    If someone steals your database, can't they just use TSQL to:

    open your key (OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert;)

    and decrypt your data (DecryptByKey function)?

    What is the means to protect against this?  I mean there's no point in keeping the pass-code taped to the side of the safe, correct?

    Wednesday, August 17, 2011 7:35 PM

Answers

  • Right. They could use the master database, but wouldn't be able to open anything encrypted. And could attach the user database, but couldn't decrypt fields. And if the attached database was encrypted with TDE, they wouldn't even get that far.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by 00011011 Wednesday, August 17, 2011 10:57 PM
    Wednesday, August 17, 2011 10:53 PM

All replies

  • Short answer, no. Keys and certificates in the user database are encrypted by keys in the master database. And the keys in the master database are encrypted by Windows. Some of these can be recreated if you have the passphrase, so you can move the database if you have enough information.

    For the long answer, see Encryption Hierarchy: http://msdn.microsoft.com/en-us/library/ms189586.aspx 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, August 17, 2011 9:41 PM
  • Hi Rick, thank you for your reply.  So what you're saying is:

    even if someone steals my User database AND the Master database, since they would be attempting to utilize them in a different installation of Windows, they wouldn't be able to decrypt the data unless they also had the password that I used to create the Master Key?


    Best regards, Andy Bruno
    Wednesday, August 17, 2011 9:46 PM
  • Right. They could use the master database, but wouldn't be able to open anything encrypted. And could attach the user database, but couldn't decrypt fields. And if the attached database was encrypted with TDE, they wouldn't even get that far.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by 00011011 Wednesday, August 17, 2011 10:57 PM
    Wednesday, August 17, 2011 10:53 PM
  • Can you talk about Best Practices when securing db user access to certificates and keys?  We are hoping to limit decrypt/encrypt ability thru stored procedures/UDF's that handle the opening and closing of keys and then securing those objects but how do we limit access to the opening and closing of these symmetric key/certs?
    Tuesday, September 6, 2011 9:13 PM