Henry_Lee (4/13/2012)
At the risk of veering slightly off topic, why backup the keys and certificates?I have found it much easier to store the script used to create the keys rather than the keys themselves. In fact, you can't even backup a Symmetric Key so you've got to keep that script around anyway. Yes, you've got to protect the script since it will contain password, key_source, identity_value, etc, but this is no different than protecting the keys themeselves. I like TrueCrypt volumes and PasswordSafe for sensitive material / password management.
The benefits I have found are store one item (one script) instead of three items (Database Master Key, Certificate and Symmetric Key script). I can put notes in the script for documentation. Also, if I recall there are some NTFS permission nuances when backing up the keys to a file server.
Please feel free to poke holes in this train of thought...
Because if you ever need to restore an encrypted database to another server the certificate is created on that new server from the backup of the original certificate not running the the original create certificate script again that you ran on the original server. This is why when you create the certificate on the original server it throws this warning:
"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."
SQL Server does not throw this warning for no reason. You then back it up on the original server with this syntax:
USE master
GO
BACKUP CERTIFICATE TDE_Testing TO FILE = 'c:\TDE_testing_cert_backup'
WITH PRIVATE KEY ( FILE = 'c:\TDE_testing_key' ,
ENCRYPTION BY PASSWORD = 'TDEtesting123' )
GO
Now to create that new certificate on the new server so you can restore database you must create the certificate from the backup like so:
--First move the 2 backup files above to the new server
CREATE CERTIFICATE TDE_Testing
FROM FILE = 'c:\TDE_testing_cert_backup'
WITH PRIVATE KEY (FILE = 'c:\TDE_testing_key' ,
DECRYPTION BY PASSWORD = 'TDEtesting123')
go
Now you can restore your encrypted database backup to this other server. Just running this original Create Certificate below script on the other server won't restore the encrypted database backup from the other server to my knowledge:
CREATE CERTIFICATE TDE_Testing WITH SUBJECT = ‘TDE Certificate’
go
However, you are kind of correct on one thing though. You can't back up an asymmetric key. However, I believe you can backup an symmetric key though. Steve, is this correct?
😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"