June 13, 2016 at 1:24 pm
Howdy folks. I have kind of a rambling question, so please bear with me. I have taken a new job, and this company uses column-level encryption. I don't have a lot of prior experience with encryption, but I have been doing lots of research. I believe I have it mostly figured out, but I wanted to clarify a few things. According to MSDN, the following is true:
"The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password."
(https://msdn.microsoft.com/en-us/library/ms189060(v=sql.105).aspx)
If I understand that correctly, it means that if someone got a copy of our BAK files, and restored them on a new machine, they could not see the encrypted data because 1) they are not on the same machine that generated the SMK, and 2) they don't have the original service account credentials.
I have tested this by restoring a BAK on a new machine, and the encrypted fields all show as NULLs. I just wanted to run it by the community to see if I am missing anything, or if you could think of another way someone could bypass the encryption if they got our BAKs.
Thanks!
June 14, 2016 at 5:15 am
Clint-525719 (6/13/2016)
Howdy folks. I have kind of a rambling question, so please bear with me. I have taken a new job, and this company uses column-level encryption. I don't have a lot of prior experience with encryption, but I have been doing lots of research. I believe I have it mostly figured out, but I wanted to clarify a few things. According to MSDN, the following is true:"The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password."
(https://msdn.microsoft.com/en-us/library/ms189060(v=sql.105).aspx)
If I understand that correctly, it means that if someone got a copy of our BAK files, and restored them on a new machine, they could not see the encrypted data because 1) they are not on the same machine that generated the SMK, and 2) they don't have the original service account credentials.
I have tested this by restoring a BAK on a new machine, and the encrypted fields all show as NULLs. I just wanted to run it by the community to see if I am missing anything, or if you could think of another way someone could bypass the encryption if they got our BAKs.
Thanks!
Not exactly. The data is there it just shows null because you haven't moved the database master key or DMK. Essentially the DMK also needs to be backed up and restored to the destination server or you could create 2 identical DMKs on 2 different servers. here is some information that may help in understanding.
A word to note. If you used a certificate to encrypt the data you will also need to move that certificate to each server you intend to use the database on. The DMK uses the SMK to encrypt/decrypt your data. Its hierarchical in structure and good illustration can be found here
June 16, 2016 at 10:36 am
Thanks for the reply!
You mention the DMK needs to be backed up and restored to the new server. The DMK is stored within the database itself, so why would it not be automatically imported when the database is restored? Also, the SMK is stored in the master database, so if I restore that, shouldn't I get both the the SMK and the DMK?
I'm trying to clarify, because I did restore both of those databases (master and the user DB) and the data still shows NULL.
June 16, 2016 at 11:01 pm
You are correct. My apologies you need to backup and restore the SMK used on the original DMK. However this is not done by restore the master database.
But after reviewing the process you cant restore a database that is encrypted without these keys/certs in place. Otherwise you will get an error. Since you dont report an error message i must ask. Are you opening the key on the new server? Something like
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
Before you run your select?
I assumed this before because you need to do this or just open the key in each session before selecting the encrypted data.
June 17, 2016 at 12:01 pm
I have done that for testing, and it I think this tells me what I need to know. What I'm trying to prove is that if someone gets both our master DB backup and our encrypted user DB backup, that it is not enough to decrypt the data.
Thanks a bunch!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply