4

I am adding column encryption to a few tables using symmetric key encryption. In each one of the procs, I write code to open and close a keys. It seems a bit verbose to open and close them in each proc. (example below )

Can I leave a key open permanently? MSDN says keys close when a session ends. Is it possible to open a key at the beginning of a session and leave it open for all queries?

CREATE PROCEDURE [dbo].[person_sel]
AS
BEGIN

open symmetric key demoKey decryption by certificate demoCert;

select 
    personId,
    name,
    email,
    cast( DECRYPTBYKEY( pin_enc) as varchar(MAX)) as pin
     from person;

close SYMMETRIC KEY demoKey;
END

1 Answer 1

1

Yes. Create (database) MASTER KEY first, then BACKUP, DROP, and CREATE the certificate (importing from earlier BACKUP), but this time WITHOUT a password. This will encrypt the certificate's private key for the certificate using the database master key, which will automatically open the private key as needed.

Realize by doing this, you are exposing your data (via the private key for the cert) to anyone that has access to the database and sysadmin to the server. In reality, all you have done is kept the data-at-rest encrypted on disk. Anyone with access to your backups and the service master key can decrypt your data.

Make sure you follow best practices for keys (and certificates) in SQL and backup the database master key.

You can view the private key encryption type by looking at the pvt_key_encryption_type_desc column in sys.certificates database dmv.

1
  • Also, anyone with access to the backup and the original password you used to create the database master key can restore the database and open the encryption using that password. You can drop the encryption by password on the database master key to eliminate this as a possibility. Jul 29, 2016 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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