TDE - Backup of service master key necessary?

Question

TDE - Backup of service master key necessary?

freakypriest

SSC-Addicted

Points: 442
More actions
July 20, 2010 at 10:17 am

#235517

Hi,
this is my first post but I am reading the newsletter and the forums for quite some time now. I've got an interesting question about TDE:
Some Blogs say it is absolutely necessary to create a backup of the service master key when using TDE. Others say you have to back up the service master key and the database master key of the master database. Microsoft says you have to back up the certificate but nothing about the keys.
I did some testing today and I could always query my encrypted database after restoring just the certificate. Even dropping and recreating the database master key of the master database did not change that. So in my opinion it is enough to back up the certificate.
What do you think/know about this? Is there a case where you need the keys for a disaster recovery? I am just talking about TDE. I know you need key backups for other encryption features.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

If you need to restore your databases to a new server you need to either have backups of the service master key or the DDL to recreate the Database master keys if you need to change them.

The probability of survival is inversely proportional to the angle of arrival.

freakypriest SSC-Addicted Points: 442 More actions · Answer 2

I simulated the move to a new instance and it worked with only the backup of the certificate. This is what I did:

First backup of certificate:

BACKUP CERTIFICATE tdeCert TO FILE = 'c:\temp\tdeCert'

WITH PRIVATE KEY (

FILE = 'c:\temp\tdeCertPrivateKey',

ENCRYPTION BY PASSWORD = 'P@ssw0rdForC3rtificate');

Then backup of TDE encrypted database.

Then I dropped the certificate and the master key:

DROP CERTIFICATE tdeCert

DROP MASTER KEY

To simulate a new instance I regenerated the service master key:

ALTER SERVICE MASTER KEY REGENERATE

Then I created a new master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rdForM@sterK3y'

Then I restored the certificate:

CREATE CERTIFICATE tdeCert

FROM FILE = 'c:\temp\tdeCert'

WITH PRIVATE KEY (FILE = 'c:\temp\tdeCertPrivateKey',

DECRYPTION BY PASSWORD = 'P@ssw0rdForC3rtificate');

Last but not least I restored the encrypted database which was successful and I could query it as I liked. It looks like the certificate is reencrypted with the new master key and this is the reason you don't need to restore any master keys.

What do you think?

sturner SSC-Insane Points: 22411 More actions · Answer 3

that will work as well, just requires a little more TSQL than just restoring the service master key and certificate.

The probability of survival is inversely proportional to the angle of arrival.

Donald Stewart-399845 SSC Enthusiast Points: 121 More actions · Answer 4

Just a question/comment:

If you backup an SMK from one physical server and restore it to another you do indeed fix a particular DMK issue.

But if there are other databases that use encryption on that server, don't you run the risk of breaking the encryption on those databases by restoring a foreign SMK?

I am working thru a mirroring issue on a SQL-2005 implementation. I resolved my encryption issue with the priciple and mirror by restoring the SMK from the primary onto the mirror. Since the mirror was the only DB on the box, this worked fine. But I am looking for a solution that will work even when there are multiple DBs on the box with encryption.

The Alter commands look promising, and I am testing them, but at first glance it seems problematic:

1. I must failover to the mirror to issue the ALTER commands and this will create transactions in the TRX log.

2. It seems I lose these transactions when I fail back and I am right back where I started from.

Donald