SQL Server has a wonderful (and sometimes annoying) feature where you can encrypt entire columns. If you’re interested in a bigger picture and more details on SQL Server and encryption, check out this uber long post I wrote.
We won’t go into that much detail here. Let’s instead breeze through what you need to know:
- SQL server has symmetric and asymmetric encryption keys.
- Keys can themselves be encrypted by “security certificates.”
- Security certificates can be password-encrypted if you choose to do so.
- Columns can be encrypted with a key.
This means that to encrypt (or subsequently decrypt) columns, you will need a key and a certificate. Generating these are beyond the scope of this post; let’s just assume you have an AllPurposeKey which is encrypted with an AllPurposeCertificate.
For example, let’s say you had a User table that had an encrypted BirthDate column. If you tried to select all the birthdays, you would see something like:
0x002A5D0CDC10391F25DA73EA80F6AE3801000000E5177115DF0DC30BFFE9AE ...
0x002A5D0CDC10391F25DA73EA80F6AE3801000000A5EE2DED63FCF9F85688BA ...
0x002A5D0CDC10391F25DA73EA80F6AE3801000000B231814527B6C75A520721 ...
...
Clearly, this is unusable. To decrypt it, select it with your key and certificate, and convert it into a date, like so:
OPEN SYMMETRIC KEY AllPurposeKey DECRYPTION BY CERTIFICATE AllPurposeCertificate
SELECT CONVERT(DATE, DecryptByKey(u.BirthDate)) AS BirthDate
And instead, you will see (as expected), the unencrypted dates:
1961-09-07 00:00:00.000
1978-11-22 00:00:00.000
1953-03-31 00:00:00.000
...
Easy breezy! There are benefits to encrypting at a database level, as there are drawbacks, but I leave it to you to puzzle those out.
