Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm reading some documentation and whitepaper about Transparent Data Encryption. Some documentation mention to backup the Service Master Key as well (For clarification I'm not talking about the Database Master Key). I just don't exactly understand why this is necessary, because I was able to backup/restore a Database with TDE Encryption from Server A (backup) to Server B (restore) without using any Service Master Key.

In what scenario do I need to restore the Service Master Key?

share|improve this question
    
Are you sure you enabled encryption on your database? Also, did you create the database backup after TDE was enabled? –  Thomas Stringer Jul 20 '12 at 13:20
    
Yes I did. I needed the certificate and the key to restore it on Server B. (I did a backup of the Certificate and Key). However on B I created a new Master Key (not restored the one from Server A) and was able to Restore my database. –  gsharp Jul 20 '12 at 13:47
    
If you restored the TDE certificate and private key on server B, it should be able to decrypt the TDE database. Can you point to the document where you've read the requirement for SMK? Maybe is something more nuanced... –  Remus Rusanu Jul 20 '12 at 15:02
    
I agree with @RemusRusanu. The certificate is what drives the encryption. As for the Service Master Key, it is just general administrative best practice to back that up (something that should have been done initially) for DR, I believe. –  Thomas Stringer Jul 20 '12 at 16:17
1  
@gsharp: that documents how to backup the SMK. I was interested ina documentation that explains why is the SMK backup required when transfering a TDE encrypted DB. –  Remus Rusanu Jul 23 '12 at 8:48
show 1 more comment

2 Answers

up vote 2 down vote accepted

If you are talking about the SQL service master key, then there is a rare occurrence where you really need to restore it.

I'm thinking of a couple of scenarios where you need to restore the SMK...

  1. Somehow it got corrupted.

  2. You are rebuilding your SQL server and planning to restore every database including system databases from backup. Usually in this case also you might not need to restore the SMK if you are using the same SQL service account and password.

In TDE you don't need to restore the SMK. As everyone said, you just need the certificate and the private key. You don't need to have same database master key, also as when you create the certificate from backup it gets encrypted by the DMK of destination machine.

share|improve this answer
add comment

When you are moving a TDE database to a new instance, what you need to ensure is that the proper certificate (or asymmetric key) is also in the destination master's database. If you fail to do this, you'll receive the following error:

Msg 33111, Level 16, State 3, Line 2 Cannot find server certificate with thumbprint '0xA085414434DB4A36B29..................'.

It's not the Service Master Key that needs to move with the TDE-enabled database backup, but it would be the certificate. For instance, say you created your DEK (database encryption key) with a certificate in master named MyTDECert. Without that certificate on your destination instance, you won't be able to restore your database.

share|improve this answer
    
Yes that's clear. My question is more why is there a need (or for what purpose) to backup the Service master key. See technet.microsoft.com/en-us/library/aa337561 –  gsharp Jul 20 '12 at 13:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.