Selecting Encrypted Columns in SQL Server

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.

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Tools, Web, Wndows Forms and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *