Problems with decrypting columns

Hi!

I want to encrypt a whole column in my table and I do this with this SQL code:

OPEN symmetric key Sym_Key DECRYPTION BY certificate My_Cert
GO

UPDATE [My_demo].[dbo].[My-DemoList]
SET [Test_crypt] = encryptByKey(Key_GUID('Sym_Key'),[Test])
GO

CLOSE all symmetric keys

GO

And this seems ok but when I want to decrypt it with the view I have created it seems that I get a "rubbish" character between each "real" character.

So my questions is: What am I doing wrong

Because if I do an insert like this

OPEN symmetric key Sym_Key DECRYPTION BY certificate My_Cert
GO

INSERT INTO [My-DemoList] (Test_crypt) VALUES(encryptByKey(Key_GUID('Sym_Key'),'1234567'))

GO

CLOSE all symmetric keys

And then use my view to look at the decrypted value that I put in its ok.

Many thanks in advance!



Answer this question

Problems with decrypting columns

  • rrrico74

    The problem is with Unicode vs. non-Unicode strings. What is the type of the Test column in your table and how is your view defined; more specifically, what type do you convert the decrypted value to

    I expect this behavior if the view converts to varchar but the column is nvarchar. Is that the case

    Thanks
    Laurentiu



  • ble0t

    Thanks Laurentiu!

    Thats was the problem!

    Must say that your blog is great!

    Best regards

    //Magnus


  • Mudbugz

    Thanks for reading my blog. I'm glad you found it useful.

    Laurentiu



  • Problems with decrypting columns