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
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:
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:
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.