locked
AES Encryption RRS feed

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

    Friday, January 11, 2008 5:46 PM

Answers

  • 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

    Friday, January 11, 2008 7:01 PM

All replies

  • 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

    Friday, January 11, 2008 7:01 PM

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

    Candy
    Friday, January 11, 2008 8:21 PM