SQL Server 2008 R2 Encryption Issue, when Decrypts Show Special Characters

Question

I am a quite new to SQL Server encryption and I need to encrypt some columns of my database. I managed to create the Master Key, Certificate, Symmetric key and encrypt my column, but when I decrypt it, it shows some characters similar to Chinese, have any one experienced something similar?

This is my code.

DROP SYMMETRIC KEY CL1AES256Key1;
GO
DROP CERTIFICATE CL1AES256Cert1
GO
DROP MASTER KEY
GO

USE MYDB
GO
IF NOT EXISTS 
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = '24RJFEGFJuifjei343'
GO

CREATE CERTIFICATE CL1AES256Cert1
   WITH SUBJECT = 'Encrypting certificate',
   EXPIRY_DATE = '12/31/2030';
GO

CREATE SYMMETRIC KEY CL1AES256Key1
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CL1AES256Cert1;
GO

OPEN SYMMETRIC KEY CL1AES256Key1
DECRYPTION BY CERTIFICATE CL1AES256Cert1;
INSERT INTO TMP VALUES(ENCRYPTBYKEY(KEY_GUID('CL1AES256Key1'), 'Yummi'))
CLOSE SYMMETRIC KEY CL1AES256Key1;

OPEN SYMMETRIC KEY CL1AES256Key1
DECRYPTION BY CERTIFICATE CL1AES256Cert1;
SELECT CAST(DECRYPTBYKEY(ENC) as nvarchar) FROM dbo.TMP
CLOSE SYMMETRIC KEY CL1AES256Key1;

Here is the result 留浭i

If somebody knows where I am making the mistake, it would help me a lot.

Regards,

Fabiano

Please decide if you're going to use `varchar` or `nvarchar`, and be consistent (I see both above). Also, [never declare these types without length](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx). — Aaron Bertrand, Aug 15 '14 at 20:24

score 8 · Answer 1 · answered Aug 15 '14 at 20:16

You are casting the encrypted value as an nvarchar, but when you use the insert statement, you specify a regular varchar. If you cast the result as a varchar, it should work as expected.

Also, if you prefix your string with the character N, like N'Yummi', it should also work, since using N tells SQL server that this string literal is unicode. See http://msdn.microsoft.com/en-us/library/ms179899.aspx

You can go down a very big rabbit hole on string collations in SQL server.

http://msdn.microsoft.com/en-us/library/ms143726.aspx

SQL Server 2008 R2 Encryption Issue, when Decrypts Show Special Characters

1 Answers1