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

Storing encrypted value in `varchar` column not `varbinary` looks like a bad idea for me. What if after encryption it will contain some non-ascii characters - how varchar field should handle this? Do you have strong reason to use `varchar` instead of `varbinary`? — Andrey Korneyev, Mar 20 '15 at 14:44
There is no strong reason to use varchar instead of varbinary. I wil prefer varbinary to varchar, that makes things easy for me. — krisjans, Mar 23 '15 at 20:31

score 0 · Accepted Answer · answered Jun 20 '16 at 18:28

0

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.

answered Jun 20 '16 at 18:28

TPAKTOPA

2,462
1
19
26

Sql server asymmetric key decryption to Nvarchar issue

1 Answers1