SQL Server 2016 – Implementing Column Level Encryption

encryption-keysql serversql-server-2016

When using the column level encryption approach, do we need to explicitly close the symmetric key in stored procedures/views or its not required? how it's handled for the subsequent openings ?

eg:

CLOSE SYMMETRIC KEY SQLSymmetricKey

Best Answer

Taking into consideration the remarks of CLOSE SYMMETRIC KEY (highlighting mine)

Open symmetric keys are bound to the session not to the security context. An open key will continue to be available until it is either explicitly closed or the session is terminated.

Security best practices usually suggest having a security mechanism (symmetric key) open for the minimum amount of time that is it necessary and only in the scope (stored procedure) necessary.

Referencing the example code in DECRYPTBYKEYAUTOCERT, there are basically two ways to decrypt column encrypted data.

OPTION ONE

  • Typically what you'd do in a stored procedure where you open the key, decrypt the data using DecryptByKey and then close the key before returning control.

OPTION TWO

  • Can be used in a view that utilizes the DecryptByKeyAutoCert function to automatically decrypt the data without having to explicitly open the key. (Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.)
--Create the keys and certificate.  
USE AdventureWorks2012;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';  
CREATE CERTIFICATE HumanResources037   
   WITH SUBJECT = 'Sammamish HR',   
   EXPIRY_DATE = '10/31/2009';  
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES  
    ENCRYPTION BY CERTIFICATE HumanResources037;  
GO  
----Add a column of encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037 ;  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber  
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
GO  
--  
--Close the key used to encrypt the data.  
CLOSE SYMMETRIC KEY SSN_Key_01;  
--  
--There are two ways to decrypt the stored data.  
--  
--OPTION ONE, using DecryptByKey()  
--1. Open the symmetric key  
--2. Decrypt the data  
--3. Close the symmetric key  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
SELECT NationalIDNumber, EncryptedNationalIDNumber    
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
CLOSE SYMMETRIC KEY SSN_Key_01;  
--  
--OPTION TWO, using DecryptByKeyAutoCert()  
SELECT NationalIDNumber, EncryptedNationalIDNumber   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;
Related Question