August 17, 2009 at 10:36 pm
When encrypting a sql server 2008 database, an essential step is to backup the certificate(s) used to encrypt the Database Encryption key
What is the source of the private key that is backed up in this statement?
BACKUP CERTIFICATE sales09 TO FILE = 'c:\storedcerts\sales09cert'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = '9875t6#6rfid7vble7r' ,
FILE = 'c:\storedkeys\sales09key' ,
ENCRYPTION BY PASSWORD = '9n34khUbhk$w4ecJH5gh' );
What is the signficance of this private key - when does it get used in encryption? Does it get created during the certificate creation step?
I run into errors when restoring the encrypted backed up database on another server 'A key required by this operation appears to be corrupted. RESTORE FILELIST is terminating abnormally (Microsoft SQL Server, Error: 15507 )'
Is info on the private key associated with the certificate stored in SQL Server 2008? Can a user determine if the private key has been backed up, [maybe by using some dmv] so that one can avoid the warning message when enabling TDE :
'Warning: The certificate used for encrypting the database encryption
key has not been backed up. 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.'
TIA 🙂
August 18, 2009 at 6:36 pm
September 3, 2009 at 5:07 pm
Hopefully someone can point me in the right direction.
I Backed up the SMK
Created a DMK on the master DB
Created a Certificate on the master DB
Created an encryption key in my "test" database - based on the Certificate
Backed up the Certificate and Key
Then ran the ALTER DATABASE to set the Encryption ON
The process - ran REALLY fast... I checked the sys.dm_database_encryption_keys
It had a status of 2... 0 percent complete; I check this repeatedly... percent never changed.
Then both my test and the tempdb appeared complete - status = 3, but both still have
percent complete of zero.
Has something strange happened?
September 4, 2009 at 6:31 am
Hi Damon,
When the encryption is done, the percent_completed should be 0 itself. Did you by any chance take the back up and try to restore it without license and with license?
-Roy
September 4, 2009 at 10:33 am
I did attempt to restore the backup of the encrypted database to a different server – which produced the following result:
Cannot find server certificate with thumbprint '0x8BF8CD0AC53329DE9283087552646C5499E2C0AB'.
RESTORE FILELIST is terminating abnormally.
I got the same result when attempting to attach the .mdb and .ldf files to that server.
On the original server, I was able to restore the backup without issue.
I’m going to create a separate instance and try the restore, on that without the certificate… then create the certificate on the new instance and try the restore again.
Even if everything works properly, the encryption process does NOT give me a warm and fuzzy feeling.
Transparent Data Encryption seems a little too transparent.
Thanks for your help.
September 4, 2009 at 12:57 pm
So, It’s the old good news bad news:
The good news is that I was unable to restore the test DB backup on the new instance.
The bad news is that when I attempt to recreate the certificate using the backups of the TDE Cert and key on the new instance via….
USE [master]
GO
CREATE CERTIFICATE TDECertificate
FROM FILE = 'E:\MSSQL10.MSSQLSERVER\MSSQL\Backup\Certificate_EncryptionKey\TDECertificate.cert'
WITH PRIVATE KEY (
FILE = 'E:\MSSQL10.MSSQLSERVER\MSSQL\Backup\Certificate_EncryptionKey\TDE_Certificate_DYNAMICS.key',
DECRYPTION BY PASSWORD = ‘xxxxxxxx’)
I get the following:
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Which doesn’t look too good.
September 7, 2009 at 7:21 am
You have to create the Master key first before you create the Certificate. Try it out please and let me know.
-Roy
September 10, 2009 at 11:49 am
Sorry I took so long to get back... other things popped up.
Your advice put me on the right track. I restored the DMK, built the certificate, and restored the database successfully. 🙂
I was doing this restore on a separate instance, but on the same server where the original encrypted database was generated.
I assume that if this were a different server I would first have to restore the backup of the Service Master Key before starting the process.
In any event, thanks for your help. I appreciate it.
Damon
September 10, 2009 at 12:03 pm
No problem Damon. Glad that it got sorted out.
-Roy
February 3, 2010 at 12:57 pm
Hi Roy,
This is one of the best articles. I followed the exact steps and got the same error after disabling the TDE.
Just as an Update, the issue raised by Mohit and Grant will be fixed in the next release of SQL 2008
What version of Service pack do you have. I know that this was an issue with Service pack 1.
This was a known issue in Connect and Microsoft themselves made a statement that it will fixed with the next version of Service Pack
I just want the link where Microsoft declared that this as a bug and going to be fixed in Next service pack?
I need to show my management and convence them to wait until next service pack to use TDE feature.
I appreciate your help
By the way, I have tested this in SQL Server 2008 x86 with SP1 on Windows 2003 x86 with SP2
thanks again
February 3, 2010 at 1:30 pm
I think this is the URL you are looking for.
-Roy
February 3, 2010 at 3:36 pm
Hi Roy,
Thanks for the quick reply.
I went through the URL, and Microsoft person says the resolution to aviod the error as below:
'To avoid this, you can take a log backup and a database backup after TDE is disabled to remove dependency upon the encryption key (and hence the certificate) from that point forward. Alternatively, you can switch to the simple recovery model.'
But they did not confirm that it's a BUG in SQL Server 2008 SP1 and did not say that it's going to be fixed in next service pack release. And at the top, it's says that the issue is closed as by design.
can you put some more light on this
thanks
February 4, 2010 at 6:09 am
When one of my friend reported this error to Microsoft they said that they would fix it in the next release. Then they have taken a 360 degree turn saying that they wont be fixing it. They kept it like that by design and therefore it is termed as not a Bug.
I did not test it with the latest release of SQL 2008 yet. When I do get a chance to do that, I will test it and see if it is still there.
-Roy
April 26, 2010 at 3:59 pm
Yes... terrificly clear, and concise article. It let me do my own testing very quickly.
Is it correct that 'tempdb' will always have encryption enabled once any dataabase has used TDE? Even after those databases are removed from the instance? Seems that-- in my experience. Yikes...
eric johnson
ann arbor, mi
April 26, 2010 at 4:05 pm
Yes, once the first db has been marked for TDE, tempdb will as well since it is used by queries into the encrypted database.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 46 through 60 (of 81 total)
You must be logged in to reply to this topic. Login to reply