15

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?

Thomas Stringer
  • 41,128
  • 9
  • 109
  • 152
gsharp
  • 327
  • 4
  • 13
  • 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
  • @RemusRusanu It's documented here http://technet.microsoft.com/en-us/library/aa337561 I just don't see the point of do a backup of it as it's not needed to restore a encrypted databse. I was just wondering if there are some scenarios where it might be needed as i can't find any. thanks. – gsharp Jul 23 '12 at 08:34
  • 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 08:48

3 Answers3

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.

Arijit
  • 96
  • 1
  • 2
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.

Thomas Stringer
  • 41,128
  • 9
  • 109
  • 152
  • Yes that's clear. My question is more why is there a need (or for what purpose) to backup the Service master key. See http://technet.microsoft.com/en-us/library/aa337561 – gsharp Jul 20 '12 at 13:51
-1

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

Tom V
  • 15,330
  • 7
  • 58
  • 86
JYatesDBA
  • 76
  • 1
  • 4