How long is the output of EncryptByPassPhrase(), relative to the input?

I'm starting an encryption project, using EncryptByPassPhrase(), mostly on VARCHAR values. Naturally, the encrypted values are longer than the originals. Is there a formula I can use to calculate how long I need to make the new VARBINARY fields, so as to hold any possible value of the original VARCHAR field?

E.g., the first field I examined has values of up to 37 characters in length, and the encrypted values are up to 100 bytes; another has values up to 50 chars and encrypted values are up to 124 bytes. However, values as short as two or three characters can encrypt to 76 bytes. If I size the new fields as 75 + X bytes, will I have room to store the encrypted version of any possible text value of length X or less?

As far as my testing goes (using SQL Server Express 2014, SP1 and SQL Server Developer 2012 SP2, both 64-it), the formula when not using an authenticator for the return value (VARBINARY) length of:

ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', {anything})


is:

28 + (8 * (DATALENGTH(@ClearText) / 8))


Try the following:

DECLARE @ClearText VARCHAR(8000);
SET @ClearText = 'testdfdf gkdj flkgjdlfkgjdlf gjlf gklf TE%$%^&^%HFGHFhg fkgh jfgkhæ';

SELECT LEN(ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', @ClearText)) AS [ActualLength],
28 + (8 * (DATALENGTH(@ClearText) / 8)) AS [EstimatedLength];


Returns:

ActualLength EstimatedLength
92 92


And, if you change the datatype of @ClearText to instead be NVARCHAR(4000), and run it again, it returns:

ActualLength EstimatedLength
156 156


Note: the formula looks like it can be reduced by cancelling out the 8s. However, that will cause it to not work correctly since it is banded-ranges of datalengths that fit into a "bucket":

input bytes result length
----------- -------------
1 - 7 28
8 - 15 36
16 - 23 44
24 - 31 52


So the (DATALENGTH(@ClearText) / 8) portion of the formula is enforcing that decimal values are ignored rather than the value be rounded. And that is accomplished by default behavior of dividing two INT values ;-).

UPDATE:

The testing done above does not use an option available to ENCRYPTBYPASSPHRASE: specifying an "authenticator". Doing so adds 16 bytes to the minimum length, and then while the increments are still in 8-byte steps, and while the banded-ranges are still 8 bytes each, the initial range is only 4 bytes so the ranges are offset by 4 as compared to the range boundaries when not using an authenticator. To help illustrate, the following chart shows the ranges and their corresponding result lengths:

input bytes result length
----------- -------------
0 - 3 44
4 - 11 52
12 - 19 60
20 - 27 68


The formula when using an authenticator for the return value (VARBINARY) length of:

ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', {anything}, 1, {anything_1-128_bytes})


is:

44 + (8 * ((DATALENGTH(@ClearText) + 4) / 8))


NOTES:

• The length of PassPhrase has no effect on the result length

• An empty string for PassPhrase has no effect on the result length

• In order for an Authenticator value to have any effect, the value of Add_Authenticator (the 3rd input parameter) must be set to 1

• The length of Authenticator has no effect on the result length as long as it is at least 1.

• An empty string for Authenticator will have an effect on the result length, and that effect is the same as setting Add_Authenticator to 0.

• If either Add_Authenticator is set to 0, or Authenticator is an empty string or NULL, then the formula is the same as if there was no "authenticator".

The following is an expanded and improved test that shows both with and without an "authenticator", and makes it easier to change the @PassPhrase value:

DECLARE @ClearText NVARCHAR(4000),
@Authenticator sysname,
@PassPhrase VARCHAR(100);

SET @PassPhrase = '_My_PassPhr@zE_yo_';
SET @ClearText = 'testdfdf gkdj flkgjdlfkgjdlf gjlf gklf TE%$%^&^%HFGHFhg fkgh jfgkhæ';
SET @Authenticator = REPLICATE(N' ', 128);

SELECT LEN(ENCRYPTBYPASSPHRASE(@PassPhrase, @ClearText))
AS [ActualLengthSansAuthenticator],
28 + (8 * (DATALENGTH(@ClearText) / 8)) AS [EstimatedLengthSansAuthenticator];

SELECT LEN(ENCRYPTBYPASSPHRASE(@PassPhrase, @ClearText, 1, @Authenticator))
AS [ActualLengthWithAuthenticator],
44 + (8 * ((DATALENGTH(@ClearText) + 4) / 8)) AS [EstimatedLengthWithAuthenticator];


StackExchange Database Administrators Q#120929, answer score: 8