SQL SERVER Column Encryption

Encryption has become a necessity when storing data.  There are many ways to encrypt data, here we are going to encrypt a column within the database using a key within the database.

Encryption Hierarchy - This is the encryption hierarchy within SQL SERVER

AES - starting with SQL Server 2016, we can only use AES_128, AES_192, and AES_256 encryption.

Looking at the standard encryption process, each layer of the hierarchy is encrypted by the layer above.  In this example we will use a symmetric key stored within our database.  We will encrypt our key with a certificate also within our database.  Our Certificate will be encrypted by the database master key DMK.  Continuing with standard SQL Server architecture, we also have a Service Master key SMK which is created when SQL SERVER is installed.  Our top level of encryption is Windows Data Protection API (DPAPI).  We could add additional layers.



In the following SQL we are using the our Proof of concept database POC_ENRCYPTION.  we need to create a DMK with a password.  Our DMK is still encrypted by the SMK.  the second step is to create the certificate TESTCERT encrypted by DMK.  Finally we create our symmetric key TESTSYMKEY, choosing an algorithm.  starting with SQL Server 2016, we can only use AES 128,192, or 256.  Our Key TESTSMYKEY is encrypted with the certificated TESTCERT.

USE POC_ENCRYPTION

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*mN83RxZs%YV6A&ih!VofQrg';
GO

CREATE CERTIFICATE TESTCERT WITH SUBJECT = 'Test Certificate'
GO

CREATE SYMMETRIC KEY TESTSYMKEY
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TESTCERT;

GO

 

Now we finally get to use our newly created TESTSYMKEY.  In the following example we need to open our key, encrypt our data and close the key.

USE POC_ENCRYPTION;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY TESTSYMKEY
DECRYPTION BY CERTIFICATE TESTCERT;
GO
UPDATE POC_TEST_TABLE
SET IdAfterEnncryption = EncryptByKey (Key_GUID('TESTSYMKEY'),CAST(ID AS VARCHAR))
FROM dbo.POC_TEST_TABLE;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY TESTSYMKEY;
GO

 

In our last snip-it of code we use our key to decrypt and select the data.

USE POC_ENCRYPTION;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY TESTSYMKEY
DECRYPTION BY CERTIFICATE TESTCERT;
GO
SELECT [ID],IdAfterEnncryption
,CONVERT(varchar, DecryptByKey(IdAfterEnncryption)) DECRYPTED_ID
FROM dbo.POC_TEST_TABLE;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY TESTSYMKEY;
GO