July 9, 2010 at 10:41 am
I was trying to turn off TDE on a SQL Server 2008 instance. I performed the following steps:
USE MASTER
GO
ALTER DATABASE DBNAME
SET ENCRYPTION OFF
GO
USE master
DROP CERTIFICATE 'certname'
DROP MASTER KEY
I then bounced the instance. When it came up all of the previously encrypted databases are unavailable. I discovered that I FORGOT to run this statement for each database before dropping the certificate:
USE DBNAME
GO
DROP DATABASE ENCRYPTION KEY
GO
What can I do????
MM
July 10, 2010 at 1:33 am
I'm not familiar with TDE, but certificates are also used in Service Broker and my experience on this area has taught me to always keep a backup of all certificates (and passwords) in case of restore. If you have a backup of the certificate I guess you should get your data back if you restore it.
July 10, 2010 at 12:51 pm
I found the backup of the key and cert for most all of the instances and they were fine after I re-created the cert and key from those backups. On two instances the cert and key were lost and those instances had their databases restored.
There are some interesting quirks to TDE. If inspired I may try to write something about it here.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply