MS SQL Server EncryptByKey - String or binary data would be truncated

Question

In theory varchar(max) and varbinary(max) columns should be capable of storing up to 2GB of data but I cannot store a unicode string 5000 characters long.

I've looked through other questions on this topic and they all suggest checking column sizes. I've done this and see that all related columns are declared with max size.

The key difference from similar questions is that, when storing I'm encrypting data using EncryptByKey and I think that it's the bottleneck I'm looking for. From MSDN I know that return type of EncryptByKey has max size of 8000 bytes, and it is not clear what is max size of @cleartext argument, but I suspect it's the same.

The following code gives me error :

OPEN SYMMETRIC KEY SK1 DECRYPTION BY CERTIFICATE Cert1;

DECLARE @tmp5k AS NVARCHAR(max);
SET @tmp5k = N'...5000 characters...';
SELECT EncryptByKey(Key_GUID('SK1'), @tmp5k);
GO

[22001][8152] String or binary data would be truncated.

How to encrypt and store big strings (around 5k unicode characters)?

Answer 1

So I ran into this issue when using C# and trying to encrypt and inserts a long JSON string into SQL. What ended up working was converting the plain-text string to binary and then using the same SQL EncryptByKey function to insert that instead.

If you're doing this is just SQL, I think you can use this function:

CONVERT(VARBINARY(MAX), @tmp5k) AS ToBinary

So using our example:

OPEN SYMMETRIC KEY SK1 DECRYPTION BY CERTIFICATE Cert1;

DECLARE @tmp5k AS NVARCHAR(max);
SET @tmp5k = N'...5000 characters...';
SELECT EncryptByKey(Key_GUID('SK1'), CONVERT(VARBINARY(MAX), @tmp5k));
GO

And here's an example of using SQL to convert the binary back to a string:

CONVERT(VARCHAR(100), CONVERT(VARBINARY(100), @TestString)) AS StringFromBinaryFromString ;