We are looking to do Column encryption on some of our DBs. So creating the keys and encrypting is all good. My boss is wanting to use Vault by Hashicorp to store the keys.

So I am wondering if anyone knows how you go about exporting the keys from SQL 2008 and then store them in Vault?

  • Add details about how you encrypted the column data. – Hannah Vernon Mar 13 '17 at 17:43
  • This is the SQL I used to create my tests. -- Create database Key USE D2Transfer_Master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123'; GO -- Create self signed certificate USE D2Transfer_Master; GO CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data'; GO -- Create symmetric Key USE D2Transfer_Master; GO CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate1; GO – Seth Mar 13 '17 at 19:01
  • The SQL I used is too long, is there a way I can post it, so it is legible? – Seth Mar 13 '17 at 19:02
  • GitHub works, or pastebin etc – Hannah Vernon Mar 13 '17 at 19:03

There are five types of keys in the heirarchy of any SQL Server column encryption. They are service master key, database master key, asymmetric key, certificate and symmetric key. SQL Server contains commands to back up the service master key, database master key and the certificate with the private key to separate password protected files. If you want to have something to preserve an asymmetric key, you can create one from a strong named key file (.snk) that already contains the key pair and save that. For the symmetric key, you will need to create it from a command, specify the key_source and identity_value and save those two values in your Vault. IF you create symmetric and asymmetric keys without saving the inputs, then you will not be able to export them. If you want to learn more about another method of securing the keys, I suggest reading about Extensible Key Management systems, which is a SQL Server enterprise edition feature. An EKM is a separate piece of hardware designed to hold the keys and perform the encryption and decryption functions.

  • Hey Michael, thank you for the response. I am testing using the symmetric key process with a certificate. What are the commands you use to back up those things you mentioned? – Seth Mar 13 '17 at 19:00
  • There is a backup certificate command documented on msdn. For the symmetric key, you will need to specify the input when you use the create symmetric key command and save it into the Vault. – Michael Keleher Mar 13 '17 at 19:11
  • Ok, so this is how I created the symmetric key for my testing: 1) Created database Master key, by password = 'my password' 2) Created self signed certificate, with a subject = 'Protect data' 3) Created my symmetric key:: CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate1; GO So what out of that would I put into Vault? – Seth Mar 13 '17 at 19:17
  • An example would be: CREATE SYMMETRIC KEY #MarketingXXV WITH ALGORITHM = AES_128, KEY_SOURCE = 'The square of the hypotenuse is equal to the sum of the squares of the sides', IDENTITY_VALUE = 'Pythagoras' ENCRYPTION BY CERTIFICATE Marketing25; If you don't specify those parameters, then the system will generate them for you. – Michael Keleher Mar 13 '17 at 19:19
  • Ah, ok. So create that and whatever info I supply to key_source and identity_value I will need to give to my Vault to hold the same? Once that is done, can I drop the symmetric key from the DB? Or it has to stay in place? – Seth Mar 13 '17 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.