SQL Server column encryption
We use databases to store a lot of information. This can also include sensitive information we don’t want to give everyone access to. While we can use permission or views to ‘hide’ our sensitive information it will still be stored as plain text in our table. We can use column encryption to encrypt that sensitive data inside a table without the need to encrypt our entire database!
In this article we will be using symmetric encryption, since it is mathematically less complex then asymmetric encryption it is a lot faster.
Setting up the test environment
I already created a database to hold my table, so let’s start with creating a test table to hold our data:
-- Create a table to hold our sensitive data CREATE TABLE sensitive_info ( client_id INT IDENTITY, security_number VARCHAR(10), -- Can't be an INT security_number_encrypted VARBINARY(128) NULL -- Needs to be VARBINARY ) GO
It would seem pretty logical in this case to save our security_number as an INT in the table, however, there are some data type rules for us to be able to use encryption. We can only encrypt data of the type nvarchar, char, varchar, binary, varbinary or nchar. Keep that restriction in mind when working with encryption! We also need to make sure that the column that stores the encrypted data has a data type of varbinary.
Now that we know some of the data type rules of encryption and created the table, let’s fill it with some test data:
-- Insert 1000 records into the table with a random security_number INSERT INTO sensitive_info ( security_number ) SELECT ROUND(CAST(RAND() * 100000000 AS INT), 1.1) GO 1000
If you query the table you can see we got some good data to test with:
We have all of our test data read so the next step is to create a database encryption master key. We need this master key to generate our certificates which we will use to actually encrypt the data. I supplied a pretty basic password for testing purposes, use a strong one if you plan on doing this in production!
-- Create the database master encryption key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password123!' GO
Next step is creating a certificate:
-- We need to create a self-signed certificate CREATE CERTIFICATE SensitiveInfo WITH SUBJECT = 'SecurityNumber' GO
We are ready to create the actual (symmetric) encryption key that will encrypt the data for us. We can choose from various algorithms like 3DES and RC4, we will be using AES 128bits. A complete list of algorithms can be found here: //msdn.microsoft.com/en-us/library/ms188357(v=sql.120).aspx
-- Create a symmetric key CREATE SYMMETRIC KEY SymSecurityNumber WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE SensitiveInfo GO
Alright, let’s get cryptic!
The first thing we need to do before we can start encrypting data is opening the key we created in the previous step:
OPEN SYMMETRIC KEY SymSecurityNumber DECRYPTION BY CERTIFICATE SensitiveInfo GO
Let’s run a update statement against our test table to encrypt the security_number and write the encrypted value to the security_number_encrypted column:
UPDATE sensitive_info SET security_number_encrypted = ENCRYPTBYKEY(key_guid('SymSecurityNumber'),security_number) GO
If everything goes right we should see the following results when we run a SELECT against the sensitive_info table:
Looks like the encryption worked!
That wasn’t too bad now was it?
Decrypting column data works more or less as the same way as encrypting in.
We need to open the encryption key first, if we skip this we will get NULL values back in our SELECT:
-- Open the key OPEN SYMMETRIC KEY SymSecurityNumber DECRYPTION BY CERTIFICATE SensitiveInfo GO
Now we can select our data and decrypt it using the DECRYPTBYKEY function:
-- Select the data we need and decrypt the encrypted column SELECT client_id, security_number, CONVERT(VARCHAR,DECRYPTBYKEY(security_number_encrypted)) AS 'Decrypted' FROM sensitive_info GO
And again, if we run a SELECT against the table we should see decrypted values!
That’s all there is to encrypting a column in a table using a symmetric key!
The biggest challenge will most likely be certificate and key management, which always is the biggest challenge when dealing with certificates in any way.