April 12, 2012 at 9:07 pm
Comments posted to this topic are about the item Key Storage
April 13, 2012 at 5:25 am
Like the speaker you mentioned, all of our certificates are stored with the backup but password protected
However All of our passwords are stored using KeyPass (the keypass file is stored as well)
Access to the keypass is via a key file that is generated by the software itself based on random mouse movements. As a minimum, there are 3 copies of the key file. One is on me, One is in the fire safe at work, one is in a bank vault. Access to the latter 2 is controlled and reported to me if used.
The Encrypted Keypass file itself has Windows auditing turned on so that any access to the file is monitored.
But that's just how we do it 😀
April 13, 2012 at 7:16 am
Nice, that's an interesting system. I worry that the key file might not scale, but perhaps it does. Do you rotate key files often?
April 13, 2012 at 7:32 am
I am struggling with key mgmt in our organization as well. We store backups along with the data but keep the passwords on a central system seperate from the backups and data.
April 13, 2012 at 7:39 am
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...
April 13, 2012 at 11:52 am
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"
April 13, 2012 at 12:12 pm
Thanks for the reply Travis.
I don't think we're talking about the same thing. It looks like you're referring to TDE. I was referring to column level encryption. I'm not familiar with TDE nor have I ever seen that error message you posted.
I use a Database Master Key to protect a Certificate which in turn protects a Symmetric Key, then that key is used to encrypt certain columns within certain tables.
I have intentionally dropped then recreated the keys / cert from the script. Additionally, I have restored the database to the same server as well as to different servers. I've tried a number of different scenarios and have yet to lose the ability to decrypt the data. In fact, you don't even need the Database Master Key and Certificate - so long as you can create the Symmetric Key using the same ALGORITHM, KEY_SOURCE and IDENTITY_VALUE.
April 13, 2012 at 12:21 pm
Henry_Lee (4/13/2012)
Thanks for the reply Travis.I don't think we're talking about the same thing. It looks like you're referring to TDE. I was referring to column level encryption. I'm not familiar with TDE nor have I ever seen that error message you posted.
I use a Database Master Key to protect a Certificate which in turn protects a Symmetric Key, then that key is used to encrypt certain columns within certain tables.
I have intentionally dropped then recreated the keys / cert from the script. Additionally, I have restored the database to the same server as well as to different servers. I've tried a number of different scenarios and have yet to lose the ability to decrypt the data. In fact, you don't even need the Database Master Key and Certificate - so long as you can create the Symmetric Key using the same ALGORITHM, KEY_SOURCE and IDENTITY_VALUE.
Henry,
Sorry (you did not mention CLE in your post), but I got it, and you are correct. The main difference being is in TDE the Master Key and Certificate(s) are created in the Master database (hence the need for backup) In CLE(column-level encryption) the certificates and keys (Symmetric/Asymmetric) are created in the specific database and thus move with it.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
April 13, 2012 at 1:15 pm
Hi Steve
As I'm the only DBA in the building, the key file stays with me. I tend not to modify the key very often as it then means I have to note down which Key file is applicable to which set of backups.
April 13, 2012 at 1:41 pm
I don't mean to sound depressing here, but what if God forbid, something happened to you on the way home tonight and you were gone very quickly? How would your company find/use the key file(s)?:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
April 13, 2012 at 7:52 pm
TravisDBA (4/13/2012)
I don't mean to sound depressing here, but what if God forbid, something happened to you on the way home tonight and you were gone very quickly? How would your company find/use the key file(s)?:-D
No, no, no! Assume the DBA with the keys wins the lottery and chartered a private jet to The Unreachable Beach overnight.
Now, continue.
I use a Truecrypt volume with both all generated keys, and the create scripts used to generate those keys (OpenSSL and SQL Server both). The long, random password to that Truecrypt volume is stored in a file in another Truecrypt volume that houses my own passwords, as well as an encrypted Office 2007 document that other folks have access to.
Documents and Truecrypt volumes are backed up to tape, encrypted, and the keys to that are... and so on and so on.
April 14, 2012 at 8:45 am
Hi Travis
Writing this as I am from the unreachable beach 😛 sitting in My Kayak Sipping martini's to slake my unquenchable thirst 🙂
Our DR procedures manual (yes me sitting here means a disaster has occurred) tells the CFO to get the Firesafe/Vault copy and hand it over to the IT Manager. He and our 3rd party/DR support company have instructions on how to use it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply