AES Encryption

Question

I am using server 2005 running on a windows server 2003 platform. I am trying to do field encryption using symmetric key implemented by the AES algorithm. I created an AES key and apparently it encrypts the data with no problem, however when I try to retrieve the data after decryption the field is always null. I tried using the same process while using the DES or RC2 algorithm and I could both encrypt and decrypt information, however it does not decrypt for any of the AES algorithm i.e. AES_128, AES_192 and AES_256. Attached is a simple query I used to test it.


create symmetric key CCardKeys
with algorithm = aes_192
encryption by password = 'P@s$w0rD'

open symmetric key CCardKeys
decryption by password = 'P@s$w0rD'

        declare @Id varchar(50)
        set @Id = encryptbykey(key_guid('CCardKeys'),'Test')

        select convert(varchar(50),decryptbykey(@Id))

close symmetric key CCardKeys

Is there something I'm missing with AES.

Thanks for taking the time to help me.

Answer 1

The @id variable is too small. The result of encryption is 52 bytes. Make @Id to be varchar(52) and your code will work.

 

To figure this out, you could have printed the size of the encrypted blob:

 

print datalength(encryptbykey(key_guid('CCardKeys'),'Test'))

 

Thanks

Laurentiu

Answer 2

Duh,
I feel really stupid now, anyway thanks very much for your help; I needed someone to bring me back to earth.

Candy