Symmetric Key Experiment. Truth or fiction? You decide.

Last Updated on February 11, 2011 by John Morehouse

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.

http://www.flickr.com/photos/mag3737/1420554128/
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: http://msdn.microsoft.com/en-us/library/bb510663.aspx.

Let’s roll!

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

CREATE TABLE dbo.EncryptMe(
        Id INT IDENTITY(1,1)
	, City VARCHAR(50)
	)
GO
-- tally table script (below) written by Pinal Dave, http://sqlauthority.com, although slightly modified as I didn't need the names.
INSERT INTO dbo.EncryptMe (City)
	SELECT TOP 1000000
		CASE --City
		WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'Las Vegas'
		WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
		WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
		WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
	ELSE 'Houston' END
	FROM sys.all_objects a
	CROSS JOIN sys.all_objects b
GO
--got data?
SELECT * FROM dbo.EncryptMe
GO

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.

ALTER TABLE dbo.EncryptMe
	ADD EncryptedCity VARBINARY(256)
GO

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.

-- 1. Create the database master key
CREATE MASTER KEY Encryption BY
Password = 'HelloWorld'
GO
-- 2. Create the certificate
CREATE CERTIFICATE MyEncryptionTest
	WITH SUBJECT = 'MyCertificate';
GO
-- 3. Create the symmetric key
CREATE SYMMETRIC KEY TestKey
	WITH ALGORITHM = TRIPLE_DES
	ENCRYPTION BY CERTIFICATE MyEncryptionTest
GO

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

OPEN SYMMETRIC KEY TestKey
DECRYPTION BY CERTIFICATE MyEncryptionTest
UPDATE dbo.EncryptMe
SET EncryptedCity = ENCRYPTBYKEY(KEY_GUID('TestKey'),City)
GO

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?

http://www.flickr.com/photos/drocko/5082042423/
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!

CREATE VIEW dbo.vw_encryptMe
AS
OPEN SYMMETRIC KEY TestKey
DECRYPTION BY CERTIFICATE MyEncryptionTest
SELECT ID, city, CONVERT(VARCHAR(50),decryptbykey(city_2)) AS Unencrypted
FROM dbo.EncryptMe
GO

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.

CREATE FUNCTION myEncryptionTest (@encrypted varbinary(256))
RETURNS varchar(50)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @value VARCHAR(50)

OPEN SYMMETRIC KEY TestKey
DECRYPTION BY CERTIFICATE MyEncryptionTest
SET @value = CONVERT(varchar(50),decryptbykey(@encrypted))
RETURN @value
END

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

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.