How to use AES encryption for large values in SQL Server?

Question

I have a SQL Server table tbl with the schema

ID int,
LongText varbinary(max)

And I have the following functions to encrypt/decrypt the LongText when inserting/fetching records.

For encrypting:

CREATE FUNCTION Encrypt
(  
    @ValueToEncrypt varchar(max)  
)  
RETURNS varbinary(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varbinary(max)  

    SET @Result = EncryptByKey(Key_GUID('My_Encryption_Symmetric_Key'), @ValueToEncrypt)  

    -- Return the result of the function  
    RETURN @Result  
END

For decrypting:

CREATE FUNCTION Decrypt
(  
    @ValueToDecrypt varbinary(max)  
)  
RETURNS varchar(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varchar(max)  
    SET @Result = DecryptByKey(@ValueToDecrypt)  

    -- Return the result of the function  
    RETURN @Result  
END

While inserting and fetching records, I am calling these functions as follows:

For inserting:

--Open symmetric keys
INSERT INTO tbl 
VALUES (1, Encrypt('some long text here'))

For fetching:

--Open symmetric keys
SELECT ID, Decrypt(LongText) 
FROM tbl

Now when I try to insert values in the table, for larger values I get an error

String or binary data would be truncated.
The statement has been terminated.

This happens for larger values which is understandable by the message. I read somewhere that EncryptByKey/DecryptByKey have a limit of 8000 bytes. Is it what is causing this problem? If Yes, is there any workaround or alternative approach that I can use? If No, then what am I doing wrong here? For the life of me, I am not able to figure out what the problem is.

Answer 1

Is it what is causing this problem? If Yes, is there any workaround or alternative approach that I can use?

Yes it is and is like this in all version. Unfortunately the work around is not very palatable, which is breaking the input up into sizes less than the 8000 byte maximum and encrypting those, in pieces. Interestingly enough none of these functions were enhanced, however hashbytes was enhanced in 2016 to remove the 8k limitation.

The other main alternative at 2008 or 2012 is to use CLR to do this for you, if you wanted this to stay in the database.

Last there is the "put it in the application" alternative, which will most certainly require application code changes.

In or Outside of the Database

This does bring up an extended question of if the encryption or decryption should be done inside of the database or at a different layer/tier. There are arguments each way, though the more secure way is to not have the keys inside the database. The main argument for this is having the DBAs or system administrators not be able to see the data.

In SQL Server 2016, this issue was attempted to be solved by adding in Always Encrypted. While it isn't directly the answer to what you were asking as it adds in much more functionality than needed, it could also serve as a possible drop in should you decide to go to 2016.