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!
A brief (real brief) back ground on SQL Server encryption. There are a couple of ways that you can encrypt data in SQL Server.
- Symmetric Key
- Aymmetric Key (Public/Private pair)
- 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 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!
Anybody got a match?
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.
I guess that it doesn’t like “OPEN SYMMETRIC KEY…” in the VIEW definition. Next!
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.