Sql server asymmetric key decryption to Nvarchar issue

Question

I am facing a problem with data encryption and decryption in sql, below is the scenario I tried.

•Created table(@TABLE) with columns type NVARCHAR(MAX).(Trying to encrypt FirstName, LastName, MiddleName, Country, TIN)

•Created stored procedure to perform encryption ◦Stored procedure accepts input parameter of type NVARCHAR(MAX), encrypts the value, Convert the value to type NVARCHAR(MAX) and returns the value.

CREATE PROCEDURE DNB_ENCRYPT    
(   
    @Value NVARCHAR(MAX),
    @EncryptedValue NVARCHAR(MAX) OUTPUT    
)    
AS
BEGIN

    IF NOT EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'ASymKeyPwd')
        CREATE ASYMMETRIC KEY ASymKeyPwd WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = N'18SomeHiddenPassword!96';

    IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'SymKey')
        CREATE SYMMETRIC KEY SymKey WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY AsymKeyPwd;

    OPEN SYMMETRIC KEY SymKey DECRYPTION BY ASYMMETRIC KEY AsymKeyPwd WITH PASSWORD = N'18SomeHiddenPassword!96'    

    --INSERT INTO TESTTABLE(FIRSTNAME)VALUES(ENCRYPTBYKEY(KEY_GUID('SymKey'), @Value))

    SET @EncryptedValue = CONVERT(NVARCHAR(MAX), EncryptByKey(Key_GUID('SymKey'), @Value))
    CLOSE SYMMETRIC KEY SymKey;
END

•Created view on top of @TABLE, view decrypts and returns all the values in a table.

CONVERT(VARCHAR(MAX), DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymKeyPwd'), N'18SomeHiddenPassword!96',  PRIMARYTIN))

The issue is with decryption, some of the values are decrypted properly and for few values I am seeing nulls.

Joe - 蠀簴䝌誉ꢁ罫檉 - Joe

Antony - 蠀簴䝌誉ꢁ罫檉 - Antony

123121312-蠀簴䝌誉ꢁ罫檉- NULL

IN-蠀簴䝌誉ꢁ罫檉-IN

US-蠀簴䝌誉ꢁ罫檉-NULL

The whole process works fine if the table column is of type varbinary, but with nvarchar(max) few values are not decrypted as expected. I am unable to trace the problem. Could any one please help me in resolving this issue.

Thanks, Kris

Answer 1

You have syntax error, in decryption part

CONVERT(VARCHAR(MAX), DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('AsymKeyPwd'), N'18SomeHiddenPassword!96', PRIMARYTIN))

VARCHAR(MAX) should be NVARCHAR(MAX), as on all other places it is NVARCHAR(MAX) type.