OK, so I'm testing EncryptByCert with some code like the following:

DECLARE @v varchar(8000);
SELECT @v = REPLICATE('A', 117);
SELECT @v;
DECLARE @e varbinary(8000);
SELECT @e = EncryptByCert(Cert_ID(N'TestCertificate'), @v);
SELECT @e;

The function encrypts fine and I'm able to use DecryptByCert to get the
result.  Problem is if I change the line SELECT @v = REPLICATE('A', 117) to:

SELECT @v = REPLICATE('A', 118);

The EncryptByCert function returns NULL every time with 118 or higher.  BOL
states that the result is returned as a varbinary with a max length of
8,000.  I keep getting a varbinary with a max length of 128.  Can anyone
else reproduce this, or am I doing something wrong?

Thanks

I get the same result. Bug or "by design"? Weird, very weird.


ML

---
http://milambda.blogspot.com/

Here's my repro:

/* Certificate:
create certificate    SomeCert
    encryption by password = 'ulanbator'
    with subject = 'Madagascar'
*/

declare    @v    varchar(8000);

select    @v = 'Crazy frog jumped over a running rat seeking vengeance for
missing receipts. Jimmy was out there whacking Little Tommy and the storck
fell from the sky.'

select    @v as V
    ,datalength(@v) as LengthOfV;

declare    @e    varbinary(8000);

select    @e = encryptByCert(cert_id('SomeCert'), @v);

select    @e;
go


ML

---
http://milambda.blogspot.com/

Incidentally, SignByCert does not have the same problem.

My version:
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
    Apr 14 2006 01:12:25
    Copyright (c) 1988-2005 Microsoft Corporation
    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


ML

---
http://milambda.blogspot.com/

Hello ML,

Same behavior here. Suggest filing it as a bug on connect.

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

I took the liberty to use Mike C#'s example when reporting the bug to
Microsoft Feedback.

Link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=184982

If you can validate the issue, please notify the feedback center.


ML

---
http://milambda.blogspot.com/

This limit is due to the way SQL Server creates certificates. The modulus of a SQL Server certificate is 128 bytes and everything has to fit into that. This limits it to 127 characters for VARCHAR, less than half that for NVARCHARS. The details were blogged at http://blogs.msdn.com/yukondoit/

Dan

Thanks for pointing that out. This kind of information belongs in Books Online.


ML

---
http://milambda.blogspot.com/

58 characters for NVARCHARs.  Suprisingly BOL doesn't reflect this
information.  It states plainly that the result can be a varbinary with a
max length of 8,000 bytes.  If only 117 bytes are allowed to be encrypted
with certificates, the information in BOL needs to be fixed.

So for certificates imported into the system with (for instance) 3,456 bit
keys, the limit would be 421 bytes (i.e., 432 - 11)?  I'm surprised they
didn't automatically break larger strings up into the appropriate lengths,
encrypt them individually and concatenate the results back together.  I
guess a UDF is in order here.

Thanks for the link.

<Dan AT Pluralsight (Dan Sullivan)> wrote in message
Show quoteHide quote

Ever growing transaction log, using simple recovery?
Help with efficiently storing a variety nested queries
Max Row Date
Returning specific weekdays
Simple trigger question