SQL Server 2005: How to determine what key was used to encrypt a piece of data

Let’s say we have some data that is encrypted and we would like to find out what key was used to perform the encryption. SQL Server 2005 knows what key was used to encrypt the data because the key identifier (the key_guid value) is prefixed to the encrypted data. We can find out the key same as SQL Server does with a one-line TSQL statement. Here’s a small demo that wraps that one-line statement in a function for easier use:

— Set up a database for this demo

create database test;

use test;

— We use the identity_value clause to obtain the same fixed GUID on all systems

create symmetric key skey with algorithm = triple_des, identity_value = ‘Test’ encryption by password = ‘Anss$pt@Ihnbwef&o!’;

— The following query should return
— 5D910600-5D5F-874F-54F5-1892D884C477

select key_guid from sys.symmetric_keys where name = ‘skey’;

— create a simple function to return the guid of the key that was used to encrypt a piece of data

create function key_guid_from_bytes (@data varbinary(8000))
returns uniqueidentifier
return convert(uniqueidentifier, @data);

— encrypt using skey and use the key_guid_from_bytes function to examine the encrypted data
— we should retrieve the key guid – 5D910600-5D5F-874F-54F5-1892D884C477 and the key name – skey

open symmetric key skey decryption by password = ‘Anss$pt@Ihnbwef&o!’;

select dbo.key_guid_from_bytes(encryptbykey(key_guid(‘skey’), ‘Top Secret’));
select name from sys.symmetric_keys where key_guid = dbo.key_guid_from_bytes(encryptbykey(key_guid(‘skey’), ‘Top Secret’));

close symmetric key skey;

— Cleanup

use master;
drop database test;

Comments (1)

  1. This is some great info!   Your posts are very clear and very informative.  Keep up the great posts!