Tell me more ×
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.
up vote 5 down vote favorite

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 1 down vote accepted

If you are talking about SQL service master key, then there is a rare chance when you really need to restore the same. I am thinking of couple of scenarios where you need to restore 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 SMK if you are using same SQL service account and password.

In TDE you don't need to restore SMK.As everyone said you just need certificate and 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
up vote 1 down vote

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

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.