Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Key Storage Expand / Collapse
Author
Message
Posted Thursday, April 12, 2012 9:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #1282872
Posted Friday, April 13, 2012 5:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:48 AM
Points: 430, Visits: 312
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
Post #1283038
Posted Friday, April 13, 2012 7:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #1283114
Posted Friday, April 13, 2012 7:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:51 AM
Points: 119, Visits: 541
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.
Post #1283135
Posted Friday, April 13, 2012 7:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 320, Visits: 1,485
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...
Post #1283145
Posted Friday, April 13, 2012 11:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:03 PM
Points: 1,335, Visits: 3,069
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. ..."
Post #1283318
Posted Friday, April 13, 2012 12:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 320, Visits: 1,485
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.
Post #1283338
Posted Friday, April 13, 2012 12:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:03 PM
Points: 1,335, Visits: 3,069
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.


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1283339
Posted Friday, April 13, 2012 1:15 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:48 AM
Points: 430, Visits: 312
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.
Post #1283387
Posted Friday, April 13, 2012 1:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:03 PM
Points: 1,335, Visits: 3,069
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)?

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1283415
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse