Column-level encryption – A useful example
SQL Server BooksOnline is a good thing. Actually, let\’s face it, BOL is a GREAT thing that helps any DBA do their job.
My only complaint (and it\’s a minor one at that) is that sometimes you just need to know how to do something, not be given pointers in the right direction. OK, OK, so a little learning doesn\’t hurt anyone, but when you\’re in a rush etc….
I had reason to start looking at encrypted columns the other day. It\’s the sort of thing that you know exists, but you don\’t really have much cause to use it day-to-day – a bit like the WorkMate that\’s in the workshop, but you never get round to using properly.
So, like the good DBA that I am (!), I go hunting around in BOL for information on column encryption, and what I get (because I\’m in a rush and haven\’t got time to trawl the message boards and forums) is details on how to create a column that holds encrypted data from an EXISTING column. But I haven\’t got an existing column I want to have an encrypted copy of, I want to create a whole new column that has encrypted data in it from the get-go.
Cue a merry half-hour tweaking the examples and producing the following statements, which I hope you find useful:
Firstly, you\’re going to need a Master Key (if you don\’t already have one)…
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = \’YourPasswordHere\’
GO
…and you\’ll need a certificate to go with that…
CREATE CERTIFICATE YourCert
WITH SUBJECT = \’Certificate for testing column encryption\’;
GO
…and a symmetric key…
CREATE SYMMETRIC KEY Symmetric_Key_01
WITH ALGORITHM = DES — Example in BOL was AES-256, but that\’s not supported on xp (yes, this was done on SQL2008 R2 Developer Edition running on Windows XP. Sorry about that. But, hey, it works !
ENCRYPTION BY CERTIFICATE YourCert;
GO
(NB: Key management and encryption algorithms are in themselves huge topics, so I\’ll not cover the reasons why you need these things or how you should secure them / back them up, if you don\’t mind)
OK, now you\’ve got all the bits you\’re going to need, so let\’s open the symmetric key so we can actively use it…
OPEN SYMMETRIC KEY Symmetric_Key_01
DECRYPTION BY CERTIFICATE YourCert;
GO
Still not quite fully set-up yet. You\’ll need a table in your test database to hold the data in its encrypted format. Please note that the datatype for encrypted data is VARBINARY, and you\’ll need to set the length according to the data that you\’ll be encrypting. VARBINARY (256) is more than enough for this example…
CREATE TABLE YourTestDB.dbo.CipherTable
(cipherData VARBINARY (256))
GO
You\’re now ready to insert some encrypted data into the table. What\’s important here is the ENCRYPTBYKEY function. You could always use the GUID of the key explicitly here, but you may expose it to people that you may not intend to, so it\’s probably more secure to use the Key_GUID function (feel free to disagree with me about this)…
INSERT YourTestDB.dbo.CipherTable
(cipherData)
VALUES
(EncryptByKey(Key_GUID(\’Symmetric_Key_01\’), \’myEncryptedTextString\’))
GO
OK, you now have \’myEncryptedTextString\’ encrypted in your table. Sure ? Really sure ? If you don\’t believe me, try…
SELECT cipherData FROM YourTestDB.dbo.CipherTable
GO
This should be a whole set of binary. Enjoy.
Wait a moment, I hear you cry, are you really sure your data\’s there ? Yes, it is. To read the string unencrypted, try…
SELECT CONVERT(varchar, DecryptByKey(cipherData))
FROM YourTestDB.dbo.CipherTable
GO
And there you have your un-encrypted string. Nice.
Also, notice that you don\’t have to supply the symmetric key name, the certificate name, or the master key to read the data back out.
Oh, and you might want to tidy up when you\’ve finished the demo….
DROP TABLE YourTestDB.dbo.CipherTable
GO
CLOSE SYMMETRIC KEY Symmetric_Key_01
DROP SYMMETRIC KEY Symmetric_Key_01
GO
DROP CERTIFICATE YourCert
GO
DROP MASTER KEY
GO
Hope this helps you out a bit.
Back soon !
The Modern Dataist 