I am trying to encrypt some data in SQL server using an existing key and certificate. I know this key and certificate work as I'm able to pull information from another table in the database but for some reason my SQL below isn't working. Am I doing something wrong?

declare @encryption table (
string varbinary(max)

open symmetric key aaaa decryption by certificate bbbb

insert into @encryption
select  encryptbykey(key_guid('aaaa'), 'Test', 1, convert(varbinary, 

select  string,
    convert(nvarchar(max), decryptbykey(string, 1, convert(varbinary, 
from    @encryption

I'm just getting this from the results.

string = 0x006924CD26547347A19BCA9B53E93E4501000000B01C808AB6802A5FD2781671BF50745638EDC8A55D0DC302F6CA9E3B7AE80913A297D0E34F14F4EA87B7E88F8066C38D718CA2E3092DF8A4C364CD8955CE61B7

decrypted string = 敔瑳

Appreciate any advise / help!


  • 2
    right of the bat, did you try to convert into varchar instead? – S4V1N Jul 21 '17 at 10:57
  • Oh balls...it was that easy. hangs head in shame – Lacutas Jul 21 '17 at 10:59
  • :) make sure you close the key afterwards – S4V1N Jul 21 '17 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.