Encryption performed by these methods is done in chunks, with the maximum chunk size is the key length minus some internal overhead (117 bytes for 1024 bit keys, and 245 bytes for 2048 bit keys first introduced in SQL Server 2016).
If your input is any larger than that, you have to split it into chunks and encrypt one at a time, then concatenate the result.
Decryption, of course, should be performed accordingly. However, an important difference between the two is that encryption chunk size will be smaller than the key, and for decryption it should be exactly the key size. That's because any data, however short, will be encrypted into key long chunk, so that no guesses on the input length can be made by looking at the output.
Here is an excerpt from my encryption function (written for 2012 version, so 1024 bit keys are assumed):
create function [dbo].[security_EncryptByCert]
(
@ClearText varbinary(max)
)
returns varbinary(max) with schemabinding, returns null on null input as begin
-- Returned value
declare @Ret varbinary(max) = 0x,
-- Length of the ciphertext
@Lng int = datalength(@ClearText),
-- Starting offset of the chunk to encrypt
@i int = 1,
-- Chunk size, currently it can't be more than 117 bytes
@Size int = 100,
-- Certificate to encrypt data with
@CertId int;
-- Determine the certificate with which to perform encryption
select @CertId = Id from ...
-- Iterate chunk by chunk til the end of the text
while @i < @Lng begin
set @Ret += encryptbycert(@CertId, substring(@ClearText, @i, @Size));
-- Move the pointer to the next block
set @i += @Size;
end;
return @Ret;
end;
In this case, I used 100 byte chunks, not the largest possible ones. Don't really remember why, but you can use 245 bytes as a limit on 2016.
