Decrypting symmetric key encrypted data is failing...

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  November 12, 2009 at 11:30 am

  #213658

  So, I am trying to encrypt some sensitive data. I am using symmetric key encryption by password.

  I've created a master key. I've created a symmetric key (tried various algorithms and none work).

  I then test with this code and the decrypting of the data does not work. The decrypted value is just garbage data, rather than matching the original unencrypted data.

  declare @a varbinary(max)

  OPEN SYMMETRIC KEY MySymmetricKey

  DECRYPTION BY PASSWORD = 'AWickedStrongPass0rd!!!'

  set @a = EncryptByKey(Key_GUID('MySymmetricKey'), 'SensitiveData')

  select @a -- Show me the encrypted value (so far so good)

  select convert(nvarchar(max), DecryptByKey(@a)) -- When data is decrypted it's "garbage data". Example: 㠴㠰㄰〷㈰㌳㜸㠸

  Please help!

  
  
  A.J.
  DBA with an attitude

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  November 12, 2009 at 11:57 am

  #1078516

  Wow... now I feel really DUMB. :angry:

  I did figure out my own problem, and thought I would share it since it took me (i hate to admit this) a couple hours to figure this out.

  When encrypting / decrypting data... how you cast / convert data during decryption is a key. Example: if you encrypt using the ol' "N" like so.... EncryptByKey(Key_GUID('TestTableKey'), N'Adam Is Dumb'), then you must convert/cast to nvarchar. And likewise if you encrypt your data without the ol' "N" then you must convert/cast to varchar upon decryption. So to fix my example it was a one letter change, see below:

  declare @a varbinary(max)

  OPEN SYMMETRIC KEY MySymmetricKey

  DECRYPTION BY PASSWORD = 'AWickedStrongPass0rd!!!'

  set @a = EncryptByKey(Key_GUID('MySymmetricKey'), 'SensitiveData')

  select @a -- Show me the encrypted value (so far so good)

  select convert(varchar(max), DecryptByKey(@a)) -- When data is decrypted it's now correct

  
  
  A.J.
  DBA with an attitude

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  November 16, 2009 at 10:59 pm

  #1080042

  Yikes, that is good to know.. Thanks for the heads up..

  CEWII