Symmetric Key Experiment. Truth or fiction? You decide.

Symmetric Key Experiment

At our last user group meeting, we mixed up the agenda a bit and did something that really
got the membership involved.  We had them be the speakers.  Our leaders invited the members (who were present and we had about 25 of them!) to throw out any topics about
SQL Server that suited their fancy!

One of the topics that caught my interested, was a question revolving around encryption.  The conversation that ensued went down the path about if it’s possible to create a view to display a decrypted value of an encrypted column.  Now that’s an interesting notion.  The group consensus was that it was not possible.  I thought, what a perfect blog post topic and be able to do a little experiment!

Brief Background

A brief (real brief) back ground on SQL Server encryption. There are a couple of ways that you can encrypt data in SQL Server.
Is this secured enough?
  • Symmetric Key
  • Aymmetric Key (Public/Private pair)
  • Certificates
  • Transparent Data Encryption
  • T-SQL Functions

As with many features in SQL Server, there is a time and place for each one of these. For our purposes today, we are going to look at the symmetric key, as that is probably the most common one you’ll find out in the field.

You can find a lot of information in Books Online (BOL).  Here is a good link:

Let’s roll!

Let’s set the stage. You can use any junk database that you have lying around.

Now we have a table with 100k worth of rows, nothing major. In order to setup the encryption, let’s add another column, EncryptedCity. This column will hold the encrypted value, which will be the city name. Note that the new column type will need to be VARBINARY(256). I missed this on my first attempt at this experiment.

Now we need to start to create the keys. There are some steps that you will need to do first and there is an order to it, so pay heed.
1. Create a database master key with a password.
2. Create a certificate.
3. Create a symmetric key to encrypt/decrypt.

Now that we have the master key, certificate, and symmetric key created, we can start to encrypt the data.

Now, if you do a select on the dbo.EncryptMe table, you should notice that EncryptedCity is indeed encrypted!

Encrypted baby!

Anybody got a match?
Light that fire!

Light that fire!

Now that we have the foundation set in terms of our table and encryption tools, let’s look at a couple of lines of thinking, the first being a view. We’ll create a new view, specify the key that we want to use and try to decrypt the data so that it’s visible from within the view. Hopefully we’ll have good results!

When executing, I got this error.

Oh, Clark, I'm sorry! You're wrong!

I guess that it doesn’t like “OPEN SYMMETRIC KEY…” in the VIEW definition. Next!

What if…

We created a function and then used that in the view?  Let’s give it a shot.

Let’s create our function.

I will note that when I parsed this out, it completed successfully. However, when I attempted to execute the command, presto-chango!

So as you can see, even though the parser said that it would work just fine, you have to keep in mind that the parser just checks the syntax of the query.  It doesn’t check to see if the commands that you issues are valid and complete.  I can’t get far enough to even put this function in the view.

So, at least with symmetric keys, the ability to decrypt the contents of our column doesn’t work in a view nor in a user defined function. Is using a stored procedure (and/or a plain jane t-sql query) the only way to decrypt the data? Seems like it, at least when using a symmetric key.  I might have to do another experiment with other encryption methods to see if they work in the same order.

If you know if another way with symmetric keys to make this work, drop me a comment. I’d love to hear from you.

© 2011, John Morehouse. All rights reserved.

One Reply to “Symmetric Key Experiment. Truth or fiction? You decide.”

Leave a Reply