SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Key Storage


Key Storage

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62745 Visits: 19111
Comments posted to this topic are about the item Key Storage

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Paul Smith-221741
Paul Smith-221741
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 334
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 :-D
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62745 Visits: 19111
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?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Dustin W. Jones
Dustin W. Jones
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 548
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.

Dustin W. Jones - Database Tech.
Henry_Lee
Henry_Lee
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 1661
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...
TravisDBA
TravisDBA
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1990 Visits: 3069
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?

:-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"
Henry_Lee
Henry_Lee
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 1661
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.
TravisDBA
TravisDBA
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1990 Visits: 3069
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"
Paul Smith-221741
Paul Smith-221741
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 334
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.
TravisDBA
TravisDBA
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1990 Visits: 3069
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search