For one reason or another data security and encryption has been coming up quite a bit in my day to day work recently and I’ve started to realise that it seems to be one of those things that people aren’t all that aware of.
As I’ve been meaning to write a little series on encryption in SQL Server for a while, now seemed a great time to start.
SQL Server gives us a few different options when it comes to encryption and I’m going to take a look at each of them at some point in this series but in this first post in the series I want to look at column level encryption.
Before we can even start thinking about encrypting our data, there are a few things that we’re going to need to set up first.
Database Master Key
The first thing that you’re going to want to do is create a database master key, this is the key that will be used to encrypt the subsequent certificate and key that we’re going to need.
I’m not going to explain the key hierarchy here but we did write a post about it a while ago in case you’re interested. https://sqlundercover.com/2018/08/09/sql-server-encryption-whats-the-key-hierarchy-all-about/
So to create the database master key, you’ll need to run the following against your database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P455w0rd'
You can query sys.symmetric_keys to check the key has been created and see some information about it.
SELECT name, algorithm_desc
FROM sys.symmetric_keys
You should have an entry for ##MS_DatabaseMasterKey##.
Certificate
Now that you’ve created your key, the next thing that you’re going to need to do is create a certificate.
CREATE CERTIFICATE EncryptionCert WITH SUBJECT = 'Certificate for column level encryption'
You can now check sys.certificates for information on the certificate that you’ve just created.
SELECT name, pvt_key_encryption_type_desc
FROM sys.certificates
The eagled eyed among you will have noticed that the certificate has been encrypted using the database master key that we created in the last step.
Symmetric Key
Now that we’ve got our certificate, the last thing that we’re going to need before we get encrypting our data is a symmetric key. This is the key that we’re going to be using to encrypt the data itself.
CREATE SYMMETRIC KEY EncryptionKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EncryptionCert
Notice that we’ve had to specify the encrypting algorithm, I’ve chosen AES 256 and would suggest that you do the same. We’ve also had to specify the certificate that we’re going to use to encrypt the key (there are other options for encrypting the key but I’m not going to get into those here), this should be the certificate that we’ve just created.
Let’s check sys.symmetric_keys again….
Nice, we’ve now got an entry for our new key, lets get on with actually encrypting some data.
VARBINARY(MAX)
One thing that you do need to be aware of when setting up your table to hold encrypted data is that any encrypted field must be a VARBINARY(MAX)
I’m going to start out by just creating a simple table for our demo
CREATE TABLE EncryptionTest
(ID INT IDENTITY (1,1),
EncryptedData VARBINARY(MAX))
Encrypting Data
The first thing that we’re going to need to do when we want to encrypt some data is to open the symmetric key.
OPEN SYMMETRIC KEY EncryptionKey DECRYPTION BY CERTIFICATE EncryptionCert
When opening the key you’re going to need to tell SQL what certificate to use to decrypt it, this should be the certificate that was used to encrypt it.
Now we can get encrypting some data. To encrypt we need to use the function, ENCRYPTBYKEY. It takes two parameters, the GUID of the symmetric key and the plain text that we’re going to encrypt. What’s that GUID I hear you ask? Well don’t worry about it too much, we can fetch that using the KEY_GUID() function easily enough.
So let’s pop some encrypted data into our table. I’m going to insert two rows and yes I’ve deliberately inserted the same value into both, I’ll tell you why in just a second.
INSERT INTO EncryptionTest (EncryptedData)
VALUES(ENCRYPTBYKEY(KEY_GUID('EncryptionKey'),'I Love SQLUndercover.com'))
,(ENCRYPTBYKEY(KEY_GUID('EncryptionKey'),'I Love SQLUndercover.com'))
Don’t forget to close that key
CLOSE SYMMETRIC KEY EncryptionKey
So now that we’ve got some encrypted data our table, shall we have a look at what it looks like?
Awesome stuff, our data is now encrypted! And do you notice something interesting?
Even though we inserted the exact same plain text values into each row, the encrypted text or cypher text is different. That’s because the encryption is what’s called non-deterministic, the cypher text will change every time a value is encrypted. This just helps make it much more difficult to crack.
Decrypting Data
It’s all very well encrypting our data but at some point we’re going to want to read it, let’s look at how to go about decrypting things.
To decrypt we’re going to use the DECRYPTBYKEY() function, oh and we’ll need to open and close the key as we did when encrypting.
OPEN SYMMETRIC KEY EncryptionKey DECRYPTION BY CERTIFICATE EncryptionCert
SELECT ID, DECRYPTBYKEY(EncryptedData)
FROM EncryptionTest
CLOSE SYMMETRIC KEY EncryptionKey
hmmm something’s not right is it? That data still looks like gobbledygook to me, what’s going on?
What’s going on is that DECRYPTBYKEY returns a VARBINARY datatype so to read the data we’re going to need to cast it back to it’s native datatype.
OPEN SYMMETRIC KEY EncryptionKey DECRYPTION BY CERTIFICATE EncryptionCert
SELECT ID, CAST(DECRYPTBYKEY(EncryptedData) AS VARCHAR)
FROM EncryptionTest
CLOSE SYMMETRIC KEY EncryptionKey
And BOOM, we’ve got back our decrypted plain text!
A lot of people seem scared of encryption in SQL Server for some reason and I’ve seen all sorts of weird and wonderful, home grown ways to encrypt data but with any luck I’ve shown you just how easy SQL makes it for us. If you’re storing any sort of sensitive data in your database you should be thinking about encryption and if it’s appropriate for you.
If you’re interested in some of the odd ball ways that I’ve seen people try to encrypt data, check out our Writing Your Own Encryption series of posts where I look at a few of them and why they may not be the best idea.