decrypted value is different from the original one

Hi,

I got an issue with encrypt and decrypt. I encrypted a data, say 'ok', then I decrypt the encrypted data, but what I got is not 'ok'. but if I encrypt a column, then after decryption, those data asr same with the original data in that column.

In my case,

...

create table mytable ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

The reason you are seeing a different output is because the plaintext is being cast from an MBCS string (varchar) to a Unicode string (nvarchar):

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')=> ‘ok’ is varchar (1 byte per character)

=> The result of EncryptByKey is varbinary

…

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

=>You are reading a varbinary and the system no longer knows it was originally a string, the decrypted blob is 2 bytes long (0x6F for ‘o’ & 0x6B)

=> Then you convert these 2 bytes to Unicode (nvarchar), and it converts to a 1 character string (0x6F6B ).

Probably the following example will make it clearer:

declare @.blob varbinary(10)

set @.blob = 0x6f6b

select cast( @.blob as nvarchar(5)), cast( @.blob as varchar(10))

go

I hope this information helps. Let us know if you have further questions or feedback.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine