Recently I've been charged to column level encrypt some personally identifiable information. The present post is not intended to discuss the merits of column level encryption; rather, as I said it is to put a few code snippets up so that I can reference them later. If you should find yourself in a column level encryption predicament in a SQL Server 2008 environment, you may find these useful as well.
First thing's first. Get the database ready for column level encryption by creating a master key:
--if there is no master key create one IF NOT EXISTS ( SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101 ) CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'This is where you would put a really long key for creating a symmetric key.' GO
Now, you'll need a certificate or a set of certificates with which you will encrypt your symmetric key or keys:
-- if the certificate doesn't, exist create it now IF NOT EXISTS ( SELECT * FROM sys.certificates WHERE name = 'PrivateDataCertificate' ) CREATE CERTIFICATE PrivateDataCertificate WITH SUBJECT = 'For encrypting private data'; GO
Once you have your certificates, you can create your key or keys:
-- if the key doesn't exist, create it too IF NOT EXISTS ( SELECT * FROM sys.symmetric_keys WHERE name = 'PrivateDataKey' ) CREATE SYMMETRIC KEY PrivateDataKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE PrivateDataCertificate; GO
Before you can use your symmetric key, you have to open it. I recommend that you get in the habit of closing it when you're finished with it. The symmetric key remains open for the life of the session. Let's say that you have a stored procedure in which you open the symmetric key to decrypt some private data which your stored procedure uses internally. Someone who has access to the stored procedure can run it and then will have the key opened for use in decrypting private data. My point, close the key before you leave the procedure. Here's how you open and close keys.
-- open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY PrivateDataKey DECRYPTION BY CERTIFICATE PrivateDataCertificate; -- close the symmetric key CLOSE SYMMETRIC KEY PrivateDataKey;
Here's a little test script I wrote to demonstrate a few points. First, the syntax for encrypting and decrypting. Second, the fact that the the cipher text changes each time you do the encryption. This prevents a plain text attack.
-- open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY PrivateDataKey DECRYPTION BY CERTIFICATE PrivateDataCertificate; -- somewhere to put the data DECLARE @TestEncryption TABLE ( PlainText VARCHAR(100), Cipher1 VARBINARY(100), Cipher2 VARBINARY(100) ); -- some test data INSERT INTO @TestEncryption (PlainText) SELECT 'Boogers' UNION ALL SELECT 'Foobar' UNION ALL SELECT '457-55-5462'; -- ignoranus -- encrypt twice UPDATE @TestEncryption SET Cipher1 = ENCRYPTBYKEY(KEY_GUID('PrivateDataKey'), PlainText), Cipher2 = ENCRYPTBYKEY(KEY_GUID('PrivateDataKey'), PlainText); -- decrypt and display results SELECT *, CiphersDiffer = CASE WHEN Cipher1 <> Cipher2 THEN 'TRUE' ELSE 'FALSE' END, PlainText1 = CONVERT(VARCHAR, DECRYPTBYKEY(Cipher1)), PlainText2 = CONVERT(VARCHAR, DECRYPTBYKEY(Cipher2)) FROM @TestEncryption; -- close the symmetric key CLOSE SYMMETRIC KEY PrivateDataKey;