How to Use AES_256 Encryption and Decryption in SQL Server

encryptionSecuritysql-server-2016

I have an odd encryption and decryption problem. Encryption worked fine in SQL 2008 R2, we were using TRIPLE_DES_3KEY. This has been deprecated in SQL 2016, so we were are testing out AES_256. My original string keeps getting cut off, not sure what it's doing or how else to explain. I am sure I'm missing something, but not sure what. Detals below. Thanks!

My Symmetric Key: 

CREATE SYMMETRIC KEY SK03  
WITH  
    ALGORITHM = AES_256  
    ENCRYPTION BY PASSWORD = 'ThisIsMyAES_256EncryptionTest';

My Table: 

CREATE TABLE [dbo].[_EncryptionTest](  
    [OriginalValue] [varchar](max) NULL,  
    [Encryptedvalue] [varbinary](8000) NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My Stored Procedure: 

CREATE PROCEDURE [dbo].[_usp_EncryptionTest]  
    @InputString varchar(MAX),  
    @InputString2 varchar(MAX)  

AS  
BEGIN  
    SET NOCOUNT ON;  

    OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'  

    INSERT INTO _EncryptionTest
        (OriginalValue, Encryptedvalue)
    VALUES (
        @InputString
        ,ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY,@InputString2))
        )

    CLOSE SYMMETRIC KEY SK03;

END

My Test SQL: 

EXEC _usp_EncryptionTest 'Spring is Finally Here! Spring is Finally Here!','Spring is Finally Here! Spring is Finally Here!'

As you can see from the image below, the data gets updated to my table.

enter image description here

My Decryption SQL: 

OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'  
SELECT   
    [OriginalValue]   
    ,CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue]))  
FROM  
    _EncryptionTest  
CLOSE SYMMETRIC KEY SK03;

The image below should show the results with the decryoted value cutoff.
enter image description here

Best Answer

Try defining the length of the varbinary in your convert statement as varbinary(max). Per https://technet.microsoft.com/en-us/library/ms188362(v=sql.110).aspx it would default to 30.

You can replicate that with the following script:

DECLARE @STRING VARCHAR(100);
SELECT  @STRING = REPLICATE('X', 100);

SELECT  LEN(@STRING) AS String_Length,
        LEN(CONVERT(VARBINARY, @STRING)) AS Varbinary_Length ,
        LEN(CONVERT(VARBINARY(MAX), @STRING)) AS VarbinaryMax_Length;
Related Question