November 15, 2007 at 9:52 am
Hi,
I am testing database certificates that are encrypted using the database master key. I have read that a copy of the database master key is stored in the system database with encryption by the service master key and another copy is stored with the user database. I ran a test and backed up the database and restored it onto another server and I was able to access the encrypted columns without any issues. The question is, if someone ever gets hold of a backup then the person can simply look up the certificate ids from the system table and use it to decrypt the encrypted columns. I feel like I am missing something or misunderstanding how this is suppose to work. If you use certificates encrypted by a database master key (by password) then can anyone simply get to the encrypted data on the backup? How do you prevent someone who has gotten a hold of a backup from being able to decrypt the encrypted columns? Any help would be greatly appreciated.
Thanks!
MP
November 15, 2007 at 10:25 am
I guess we can just encrypt the backups using password protection and encrypt the data using the certificates. 🙂
November 15, 2007 at 8:18 pm
MP (11/15/2007)
I ran a test and backed up the database and restored it onto another server and I was able to access the encrypted columns without any issues. The question is, if someone ever gets hold of a backup then the person can simply look up the certificate ids from the system table and use it to decrypt the encrypted columns. I feel like I am missing something or misunderstanding how this is suppose to work. If you use certificates encrypted by a database master key (by password) then can anyone simply get to the encrypted data on the backup? How do you prevent someone who has gotten a hold of a backup from being able to decrypt the encrypted columns? Any help would be greatly appreciated.
When you say you accessed the encrypted columns without any issues, did you do anything like open the master key first? Did you execute a stored procedure where that may have been embedded?
K. Brian Kelley
@kbriankelley
November 16, 2007 at 7:49 am
Hi Brian,
I used the certificate to decrypt the encrypted columns and since you can see the certificates in the sys table, I figured someone could easily decrypt the columns as well if they ever got hold of a backup. Its my first year as a DBA so still learning a lot as I go. I guess the certificates will only ensure data encryption. I was originally thinking that maybe the certificates didn't travel along with the backup (actually not sure what I expected) because if someone ever hacks the db or gets hold of a backup thats not encrypted then the encryption seems essentially useless from that stand point. I guess I need to look at it as encryption from a data perspective (on a non compromised db) and not a database perspective.
MP
November 16, 2007 at 8:23 am
The database master key and all certificates, symmetric, and asymmetric keys do travel with the backup. However, usually you have to know the password to decrypt the database master key. Hence the reason you have extra steps with database mirroring if encryption is being used.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply