August 5, 2014 at 7:41 am
I am planning to implement TDE on our Database servers and I have the following questions on TDE.
Question 1:
I tried moving a TDE database to another server. I only needed a backup and restore of Certificate and the Private key associated with the Certificate.
I did not restore a backup of DMK(Database Master Key) on new instance. But some of the blogs that I read actually restores a copy of DMK to the new server before restoring a Certificate using the following scripts
Step 1:-- Restores master key
RESTORE MASTER KEY FROM FILE =
DECRYPTION BY PASSWORD = 'password'
ENCRYPTION BY PASSWORD = 'password'
Step 2:--- opens master key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
Step 3:
--- Creates Certificate from File
CREATE CERTIFICATE FROM FILE
WITH PRIVATE (FILE =
DECRYPTION BY PASSWORD = 'password'
Step 4:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Step 5:
Do the Backup and Restore
Step 6:
Close Master key.
When I implemented TDE on new server I just had to do Step 3, without restoring DMK. I am confused regarding when is the Restore of DMK required?
Question 2:
If I want to do a nightly restore from production to development which has different service accounts. Do I need to restore a SMK(Service Master Key) or a DMK?? Or is the restore of Certificate with Private key is enough?
Question 3:
If I want to fail over to a Mirror or Logshipping or Clustered instance, Do I need to restore SMK or DMK or just Certificate with Private Key is enough?
Question 4:
How do I encrypt Master Database? Can I turn on TDE System databases(Master,model,msdb)?
Question 5:
How is Master database DMK different from user database DMK? For TDE the DMK that is created using the following script
USE [master]
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
resides just on master database.
I could also create a Master key for a User database. What is the role of User databases DMK? I understand Database Encryption Key(DEK) is unique to each database and a copy is also stored in master database. How is user database DMK different from DEK?
Question 6:
This is just preparing for DR scenario.
Looks like Certificate & Private is more important than DMK & SMK to restore a backup of TDE database to new server.
If the certificate and the private key is lost Can I restore a backup of encrypted Database by restoring both SMK & DMK to new server?
August 5, 2014 at 8:32 am
lallu_jaya (8/5/2014)
Question 1:I tried moving a TDE database to another server. I only needed a backup and restore of Certificate and the Private key associated with the Certificate.
I did not restore a backup of DMK(Database Master Key) on new instance.
That is correct you just need a backup of the cert and its private key which you then restore to the new server
lallu_jaya (8/5/2014)
But some of the blogs that I read actually restores a copy of DMK to the new server before restoring a Certificate
That's because they do not fully understand how the encryption hierarchy works in SQL server.
If you restore a cert on the target server and a DMK does not exist you must create one but it should not be a direct copy of another servers DMK for obvious security reasons
lallu_jaya (8/5/2014)
When I implemented TDE on new server I just had to do Step 3, without restoring DMK. I am confused regarding when is the Restore of DMK required?
As i said above when creating\restoring a cert for the first time on a new instance you will be asked to create a DMK if one doesn't exist
lallu_jaya (8/5/2014)
Question 2:If I want to do a nightly restore from production to development which has different service accounts. Do I need to restore a SMK(Service Master Key) or a DMK?? Or is the restore of Certificate with Private key is enough?
Again, just the cert and its Private key
lallu_jaya (8/5/2014)
Question 3:If I want to fail over to a Mirror or Logshipping or Clustered instance, Do I need to restore SMK or DMK or just Certificate with Private Key is enough?
Again, just cert and private key
lallu_jaya (8/5/2014)
Question 4:How do I encrypt Master Database? Can I turn on TDE System databases(Master,model,msdb)?
system databases are not encrypted except tempdb
lallu_jaya (8/5/2014)
Question 5:How is Master database DMK different from user database DMK? For TDE the DMK that is created using the following script
USE [master]
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
resides just on master database.
I could also create a Master key for a User database. What is the role of User databases DMK?
A master key for a user database would be typically used for object level encryption, etc. Books online has more detail
lallu_jaya (8/5/2014)
I understand Database Encryption Key(DEK) is unique to each database and a copy is also stored in master database. How is user database DMK different from DEK?
The DEK is a fast lightweight symmetric key used for encryption, its protected by the certificate which is an assymetric key. The certs private key is in turn protected by the DMK which is a symmetric key.
lallu_jaya (8/5/2014)
Question 6:This is just preparing for DR scenario.
Looks like Certificate & Private is more important than DMK & SMK to restore a backup of TDE database to new server.
that is correct
lallu_jaya (8/5/2014)
If the certificate and the private key is lost Can I restore a backup of encrypted Database by restoring both SMK & DMK to new server?
No, you can't you need the backup of the cert and its private key. The SMK and DMK have absolutely no bearing on the TDE protected database. Think of the SMK and DMK as custodians of the private key while the cert is on that server. At all times protect the cert and forget the SMK and DMK.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 5, 2014 at 9:18 am
@perry Whittle
Thanks soo much for your reply. I was very confused regarding the concepts. your answers addressed all the questions.
I have the following questions
1. Is there any other way I could encrypt master database if not through TDE?
As master database is so imporatant, it has paths to all the files, logins,linked servers etc..our company would like to encrypt the master database as well.
2. In what scenarios would I be using the SMK & DMK backups?
Are these restored when Keys are corrupted or if someone accidentally deleted it?
3. Can I recover/restore a database if anyone of the following things are missing
SMK
DMK
Cert
Private key of Cert - If Private key file is lost, how can a Cert be restored?
Password for Private key of Cert - If I don't remember the password for Private key of Cert. Can DMK used to decrypt the Private key of Cert?
August 5, 2014 at 9:27 am
lallu_jaya (8/5/2014)
1. Is there any other way I could encrypt master database if not through TDE?As master database is so imporatant, it has paths to all the files, logins,linked servers etc..our company would like to encrypt the master database as well.
Why?
The paths are meaningless unless someone has physical access to the server (and if they do, you have much more serious problems), passwords are stored hashed anyway.
What are you trying to protect against here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2014 at 9:43 am
lallu_jaya (8/5/2014)
@Perry WhittleThanks soo much for your reply. I was very confused regarding the concepts. your answers addressed all the questions.
This article may help you too http://www.sqlservercentral.com/articles/Transparent+Data+Encryption+(TDE)/91712/[/url]
lallu_jaya (8/5/2014)
1. Is there any other way I could encrypt master database if not through TDE?As master database is so imporatant, it has paths to all the files, logins,linked servers etc..our company would like to encrypt the master database as well.
It's not supported, as Gail has said all sensitive info such as linked login passwords, etc are encrypted.
Encrypted by <gasp> the Service Master Key 😉
lallu_jaya (8/5/2014)
2. In what scenarios would I be using the SMK & DMK backups?Are these restored when Keys are corrupted or if someone accidentally deleted it?
When performing a restore of the instance during DR maybe
lallu_jaya (8/5/2014)
3. Can I recover/restore a database if anyone of the following things are missingSMK
DMK
Cert
Private key of Cert - If Private key file is lost, how can a Cert be restored?
Password for Private key of Cert - If I don't remember the password for Private key of Cert. Can DMK used to decrypt the Private key of Cert?
You dont need SMK or DMK to move the TDE database and its cert to another server. Read my guide above for more info.
Once the cert is restored to the server the private key is protected by the DMK so you dont need to worry. It's only when moving the cert around you need the cert and its private key backup, so don't lose them!!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 6, 2014 at 11:36 am
@PerryWhite, @gilamonster
I did the following test to see if Master database brings the keys & Cert along with it.
Server1: Has TDE encrypted user database DB1_tde_test.
Server2: Does not have the TDE encryption
I restored a copy of Master database from Server1(which had all the DMK,Certficate) and restored it onto Server2.
Then I took a backup of TDE database(DB1_tde_test) and restored it onto Server2. I was able to restore it just fine without having to restore Certificate or Private key or any other password. I was able to access the data just fine.
Based on this if anyone has access to backups Master & User database, even though DB1_tde_test is encrypted they will be able to restore the database. Looks like in situations like this Master database important and there is a need to encrypt Master database as well.
How can I get around this??
August 6, 2014 at 11:54 am
You cannot encrypt master!
TDE has never been about preventing people from accessing the DB. It protects the files at rest and the backups of the user databases, that is all. It's not a magic 'protect everything', it protects a specific thing. This is why you make sure you know what you are protecting against before implementing
As for how you get around what you demonstrated, you restrict the permissions to ensure that no unauthorised people can back up databases and make very, very, very sure you protect the backups of the system databases (ACLs, folder permissions, restricted physical access, encrypted folders, etc). Same as you make sure no unauthorised people have permission to run ALTER DATABASE or they could just disable TDE. Same as you make sure no unauthorised people have permission to query the database, or they could just query the tables and copy the data to excel. etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2014 at 12:00 pm
Protect the cert by backing it up and securely storing the keys. Restoring master across servers has its own ramifications, so don't do it!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply