February 27, 2009 at 10:19 am
I am in the process of testing TDE in SQL Server 2008. One of my requirements is to provide database backups to 3rd parties.
I have done the following:
1) Enabled TDE on a database.
2) Disabled TDE on the same database.
3) Ran backup against the decrypted database.
4) Tried to restore to another SQL Server 2008 instance.
This was unsuccessful. I then removed the DNK, created a backup, and tried to restore on a different instance of SQL Server 2008.
After TDE is enabled, can a restore of a previously TDE enabled database ever take place without keys?
February 28, 2009 at 9:51 pm
Backups on TDE databases are encrypted, and they will need the certificate and private key in order to decrypt properly. So the answer to your question is no. Here's the relevant text from Books Online:
When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations.
If you're worried about encrypting the data at rest, you might look at Encrypting File System, Bitlocker, or a 3rd party disk encryption system. If you're looking at encrypting the backups while in transit, you might look at Quest's SQL LiteSpeed or Red Gate's SQL Backup, both of which can perform encryption.
K. Brian Kelley
@kbriankelley
March 2, 2009 at 7:24 am
But if data encryption is then turned off and a backup made, the database still cannot be restored. Can a database that has ever been encrypted and is not currently encrypted ever be restored?
March 2, 2009 at 8:07 am
You waited until the database was fully decrypted?
K. Brian Kelley
@kbriankelley
March 2, 2009 at 8:12 am
Yes. I ran the following and waited until I received 'Unencrypted' in the encryption_state_desc and then ran the backup:
SELECT DB_NAME(e.database_id) DatabaseName,
e.database_id,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END encryption_state_desc,
c.name,
e.percent_complete
FROM sys.dm_database_encryption_keys e
LEFT JOIN master.sys.certificates c
ON e.encryptor_thumbprint = c.thumbprint
March 2, 2009 at 9:21 am
When you open up the database backup file after the decrypt, can you find readable text strings? You should be able to do so if it's fully decrypted. Also, what error are you getting when you attempt to restore?
K. Brian Kelley
@kbriankelley
March 2, 2009 at 12:05 pm
I verified that I can read text strings within the backup file. When I then try to restore the bak file to another SQL Server instance I get the following error:
Restore failed for Server 'MyServer'. (Microsoft.SqlServer.SmoExtended)
Additional Information:
System.Data.SqlClient.SqlError: Cannot find server certificate with thumbprint ' '.
I receive the same error when I use the GUI and through a Query.
March 2, 2009 at 12:07 pm
I don't want my server certificates on the other instance that I am trying to restore to. I am trying to ensure that a 3rd party can do a data restore from the bak file provided.
March 4, 2009 at 1:48 pm
Anyone have any ideas?
April 2, 2009 at 12:00 pm
No help, but I'm having the same problem. Once encrypted I can't restore it to a different server without the keys.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy