November 12, 2009 at 11:30 am
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
November 12, 2009 at 11:57 am
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
November 16, 2009 at 10:59 pm
Yikes, that is good to know.. Thanks for the heads up..
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply