Using Encrypted columns with certificates in SQL Server

This article is going to walk through all of the steps required to demonstrate how to secure columns in a SQL Server database. We’re going to create a basic table to hold the information, a certificate, and a key to project some of those columns and grant the appropriate rights so permissioned users can access the values. 

The first thing we’re going to need is a database. For the sake of easy clean up, I’m going to create a brand new one for this. We’ll call it CaveOfWonders.

CREATE DATABASE CaveOfWonders;

Magic. We know we’re going to be creating a certificate and a key, so let’s get those set up now. We’ll choose a master password for the key, so be sure to make a note of this so it can be recorded for later use.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '4wholeNewWorld!';
GO
CREATE CERTIFICATE magicLamp WITH SUBJECT = 'To obsfucate Client Secrets';
GO
CREATE SYMMETRIC KEY aladdin WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE magicLamp ;
GO 
OPEN SYMMETRIC KEY aladdin DECRYPTION BY CERTIFICATE magicLamp;

We just need one more thing: a table to add the information we’ll be storing. Let’s create it and put some data into it.

CREATE TABLE ClientSecrets (ToolName NVARCHAR(128), SecretName NVARCHAR(128), EncryptedSecret VARBINARY(128));
INSERT INTO ClientSecrets(ToolName, SecretName, EncryptedSecret) VALUES
('Key Vault', 'Client ID', EncryptByKey(Key_GUID('aladdin'), N'super secret ID )),
('Key Vault', 'Client Secret', EncryptByKey(Key_GUID('aladdin'), N'super secret password' ));

And that’s it! We now have a table containing sensitive values which we can select and use in any other application that we can query SQL Server from, right?

USE CaveOfWonders;
SELECT *
 FROM dbo.ClientSecrets;

Not quite. We did not provide any instruction on how we should attempt to decrypt the values, so SQL Server just didn’t decrypt them. OK, so let’s have Aladdin use his lamp!

OPEN SYMMETRIC KEY aladdin DECRYPTION BY CERTIFICATE magicLamp; 
SELECT *
  FROM ClientSecrets;

Nope! We’re now using the key and certificate, but we need to provide explicit instructions to decrypt the values, as shown in the code below.

OPEN SYMMETRIC KEY aladdin DECRYPTION BY CERTIFICATE magicLamp; 
SELECT *, CONVERT(NVARCHAR(128), DecryptByKey(EncryptedSecret)) AS 'Decrypted ID Number'
  FROM ClientSecrets;

Excellent, we can now store, project and retrieve secrets in this table. Any user who has been granted the permissions to use the certificate and the key can now do so to retrieve the secrets. 

To grant the permissions required you’d need to add the following to a user who already has read access on the table

GRANT VIEW DEFINITION ON CERTIFICATE :: magicLamp TO SecretReaderUser;
GRANT VIEW DEFINITION ON SYMMETRIC KEY :: aladdin TO SecretReaderUser;