Quanto tempo dura a saída de EncryptByPassPhrase (), em relação à entrada?

No que diz respeito aos meus testes (usando SQL Server Express 2014, SP1 e SQL Server Developer 2012 SP2, ambos 64-it), a fórmula ao não usar um autenticador para o valor de retorno ( VARBINARY) comprimento de:

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

é:

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

Tente o seguinte:

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];

Retorna:

ActualLength    EstimatedLength
92              92

E, se você alterar o tipo de dados de @ClearTextpara ser NVARCHAR(4000)e executá-lo novamente, ele retornará:

ActualLength    EstimatedLength
156             156

Observação: a fórmula parece que pode ser reduzida cancelando o 8s. No entanto, isso fará com que ele não funcione corretamente, pois são faixas de comprimentos de dados que cabem em um "balde":

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

Portanto, a (DATALENGTH(@ClearText) / 8)parte da fórmula impõe que os valores decimais sejam ignorados em vez de o valor ser arredondado. E isso é feito pelo comportamento padrão de dividir dois valores INT ;-).

ATUALIZAR:

O teste feito acima não usa uma opção disponível para ENCRYPTBYPASSPHRASE : especificando um "autenticador". Fazer isso adiciona 16 bytes ao comprimento mínimo e, enquanto os incrementos ainda estão em etapas de 8 bytes e enquanto os intervalos de faixas ainda são de 8 bytes cada, o intervalo inicial é de apenas 4 bytes, portanto, os intervalos são compensados ​​por 4 como em comparação com os limites do intervalo quando não estiver usando um autenticador. Para ajudar a ilustrar, o gráfico a seguir mostra os intervalos e seus comprimentos de resultado correspondentes:

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

A fórmula ao usar um autenticadorVARBINARY para o comprimento do valor de retorno ( ) de:

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

é:

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

NOTAS:

• O comprimento de PassPhrasenão tem efeito no comprimento do resultado
• Uma string vazia para PassPhrasenão tem efeito no comprimento do resultado
• Para que um Authenticatorvalor tenha algum efeito, o valor de Add_Authenticator(o 3º parâmetro de entrada) deve ser definido como1
• O comprimento de Authenticatornão tem efeito no comprimento do resultado , desde que seja pelo menos 1 .
• Uma string vazia para Authenticatorterá um efeito no comprimento do resultado, e esse efeito é o mesmo que definir Add_Authenticatorpara 0.
• Se Add_Authenticatorfor definido como 0, ou Authenticatorfor uma string vazia ou NULL, a fórmula será a mesma como se não houvesse "autenticador".

O seguinte é um teste expandido e aprimorado que mostra com e sem um "autenticador" e facilita a alteração do @PassPhrasevalor:

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];

Isso não é científico; a única maneira de obter a resposta científica seria examinar o código que os desenvolvedores do SQL Server usaram para implementar o algoritmo 3DES 128.

Dito isto, construiu um equipamento de teste rápido que tenta determinar o tamanho prático do texto criptografado.

;WITH Num1
AS 
(
SELECT TOP(4000) rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , pwd = REPLICATE('x', ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id))
FROM sys.objects o, sys.objects o1
)
, Num2
AS 
(
SELECT TOP(4000) rn = ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , dat = REPLICATE('x', ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id))
FROM sys.objects o, sys.objects o1
)
SELECT Encrypted = ENCRYPTBYPASSPHRASE(pwd,dat,1,CONVERT(VARBINARY(20), ROW_NUMBER() OVER (ORDER BY num1.rn, num2.rn)))
    , EncryptedLength = LEN(ENCRYPTBYPASSPHRASE(pwd,dat,1,CONVERT(VARBINARY(20), ROW_NUMBER() OVER (ORDER BY num1.rn, num2.rn))))
    , RowNum = ROW_NUMBER() OVER (ORDER BY num1.rn, num2.rn)
    , DatLen = LEN(dat)
    , Diff = LEN(ENCRYPTBYPASSPHRASE(pwd,dat,1,CONVERT(VARBINARY(20), ROW_NUMBER() OVER (ORDER BY num1.rn, num2.rn)))) - LEN(dat)
FROM Num1, Num2;

Isso gera senhas e dados de comprimento variável entre 1 e 4.000 bytes e, em seguida, compara a saída da ENCRYPTBYPASSPHRASEfunção com o comprimento dos dados inseridos. Descobri, no meu computador, que há entre 41 e 48 bytes "extras" na saída.

Dito isso, sugiro usar VARBINARY(8000)como tamanho do campo, pois essa é a saída máxima definida para a função.

Eu me deparei com isso para determinar o comprimento dos dados varbinary após criptografar com enryptbypassphrase

CEILING((CAST(DATALENGTH(@cleartext) as float) + 1) / 8) * 8 + 20