/*
Copyright 2006 Danal Technology Inc
*/
/*
SQL Server has a really straightforward way to encrypt data by using EncryptDataByPassphrase. What's handy about it is that it doesn't require any keys to be added to SQL Server as the other encryption mechanisms do, you just use DecryptByPassphrase to get the original data back.
*/
DECLARE @cyphertext VARBINARY(MAX)
SET @cyphertext = EncryptByPassphrase('some handy passphrase', 'encrypt me!')
SELECT CAST(DecryptByPassphrase('some handy passphrase', @cyphertext) AS VARCHAR(MAX))
/*
This produces:
encrypt me!
As expected
One of the questions that seems to come up quite often is "What the algorithm is being used?" Afterall if all it was doing was XORing the passphrase with the clear text that is input to it, it would not be much more useful than those "Secret Decoder" rings that sometimes are included as prizes in cerial boxes.
The algorithm isn't documented in the Books Online, but a number of people have speculated that triple DES is used. That kinda' makes sense because that's what SQL Server uses to encrypt database master passwords and such.
This blog enry shows how to confirm that EncyptByPassphrase, EBP for short, uses triple DES and also passes along a little bit of other info about symmetric encryption in SQL Server.
One strategy that might be used to confirm that EBP uses triple DES is to encrypt some data using a triple DES directly and then encrypt the same data with EBP and compare the results. This technique will not work, but let's try it anyhow and see why that is. In the end part of implementing this strategy will be useful for the one that does work.
Making a triple DES symmetric key in SQL Server is pretty easy:
*/
CREATE SYMMETRIC KEY MyKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = '58!^8Cg3G7)!1)4='
/*
This creates, in effect, a random symmetric key. We want some more control. Another way to create a symmetric key is to have the key be based on a passphrase... hmmmm maybe that's what EBP does?
*/
CREATE SYMMETRIC KEY MyKeyEBP WITH
ALGORITHM = TRIPLE_DES,
KEY_SOURCE='some handy passphrase'
ENCRYPTION BY PASSWORD = '58!^8Cg3G7)!1)4='
/*
Once we have a key we can encrypt some data.
*/
OPEN SYMMETRIC KEY MyKeyEBP DECRYPTION BY PASSWORD='58!^8Cg3G7)!1)4='
SELECT EncryptByKey(Key_GUID('MyKeyEBP'), 'encrypt me!')
/*
This produces some cyphertext:
0x006516ECAA24984EBC491AF8BB1266F30100000069B2C375271736F6F762E1721AB97AAEC297BE868E89865856DFDF60134A7E02
/*
A symmetric key must be opened before it is used. It has to be identified by it GUID, not its name, that's what the Key_GUID function is for.
Lets encrypt the same text using EPB with the passphrase the same as the KEY_SOURCE for our symmetric key.
*/
SELECT EncryptByPassphrase('some handy passphrase', 'encrypt me!')
/*
This produces:
0x01000000CD8F1DEDAFD40521968C9DC614463AB62D79B994A67C85F251E28D896EC51108
As you can see, this is not even close. This doesn't prove one way or the other that EPB uses triple DES, but what's going on here?
There are a couple of things working against us. To see one of them, lets redo the MyKeyEBP encryption again.
*/
SELECT EncryptByKey(Key_GUID('MyKeyEBP'), 'encrypt me!')
0x006516ECAA24984EBC491AF8BB1266F30100000013868CB3274E23A53E011ACADE6D5167520EA94616CA5ECA96DA903B59DB5446
/*
If you look closely you will see that even though we are encrypting the same text with the same key it is producing different results. That is because triple DES, as used by SQL Server, is non-deterministic. It does not produce the same results even if the same text is encrypted again with the same key.
Without going into a lot of detail it is due to the fact that triple DES is a block cypher and SQL Server uses a different random first block everytime it runs it, and includes the block it uses in the results.
This is actually a very good thing, otherwise the bad guys could recognize when the same text was being encypted.
So there is no reason to believe EBP would produce the same output as some other triple DES encrytion would.
But compare the EPB output and the triple DES output again. They are not even the same length. The reason for this will become apparent by looking at the DecryptByKey function.
*/
SELECT CAST( DecryptByKey(0x006516ECAA24984EBC491AF8BB1266F30100000013868CB3274E23A53E011ACADE6D5167520EA94616CA5ECA96DA903B59DB5446) AS VARCHAR(MAX))
/*
This produces:
encrypt me!
which is what we would expect.
Notice that all we gave DecryptByKey was the cypher text, we did not tell it which key to use. How did it pick the right one? The secret is that EncryptByKey always adds puts the guid for the key at the beginning of the cypher text it produces.
*/
SELECT CAST(Key_GUID('MyKeyEBP') AS VARBINARY(16))
/*
Produces:
0x006516ECAA24984EBC491AF8BB1266F3
which is the beginning of
0x006516ECAA24984EBC491AF8BB1266F30100000013868CB3274E23A53E011ACADE6D5167520EA94616CA5ECA96DA903B59DB5446
That means that if we put the GUID for the MyKeyEBP in front of the cypher text produced by EBP we should be able to decrypt it using DecryptByKey. The batch script below shows this. Make sure the MyKeyEBP is open when you run this script.
*/
-- make a variable to hold the cypher text
DECLARE @cyphertext VARBINARY(MAX)
-- use EBP to encrypt some text
SET @cyphertext = EncryptByPassphrase('some handy passphrase', 'encrypt me!')
-- select it just so we can see it
SELECT @cyphertext
-- next stick the guid for MyKeyEBP in front of the cypher text produced by EBP
SET @cyphertext = CAST(Key_GUID('MyKeyEBP') AS VARBINARY(16)) + @cyphertext
-- select it again just so we can see it
SELECT @cyphertext
-- now use DecryptByKey with the fixed up cyphertext
SELECT CAST(DecryptByKey(@cyphertext) AS VARCHAR(MAX))
/*
The script produces:
0x01000000A54E180B2BF9545268BE3E7CD59235E48053F4174FF1848C69A61ACA37159337
0x006516ECAA24984EBC491AF8BB1266F301000000A54E180B2BF9545268BE3E7CD59235E48053F4174FF1848C69A61ACA37159337
encrypt me!
*/
/*
So we can conclude that EBP uses triple DES becuase we can run the cyphertext it produce through a triple DES decypter and it produces the same text that EBP encrypted.
At this point some caveats are in order.
When you use a passphrase to encrypt data, or use one to produce a symmetric key, you are responsible for maintaining the privacy of the passphrase. This typically is not that easy to do, especially if the passphrase has to be given to others. Most of the other encryption features in SQL Server are there so that you will can use SQL Server to keep your keys private.
The passphrase you choose determines how strong the key is that encrypts your data, not so much the algorithm used. BTW 'some handy passphrase' is a terrible choice for a passphrase. It has to be much longer and preferably be just a random sequence of characters.
In any case if you abide by the caveats for EncryptByPassphrase is a very handy tool to have. It is very easy to use, and not even the sysadmin or database owner will be able to decrypt your data.
A Developers Guide To SQL Server 2005
*/
Posted
Apr 09 2006, 01:13 PM
by
dan-sullivan