16

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?

Improve this question
6
  • Are you sure you enabled encryption on your database? Also, did you create the database backup after TDE was enabled?
    – Thomas Stringer
    Jul 20, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 8:48

3 Answers 3

Reset to default
8

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.

Improve this answer
2

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.

Improve this answer
2
  • 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, 2012 at 13:51
  • Maybe when migrating system dbs to new server, in order for linked server/credentials to work maybe this key is required?
    – variable
    May 11 at 18:32
-1

One case where you need to backup and restore the SMK is when you are upgrading a replication topology.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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