Encryption Varbinary Length

Using the new encryption included in SQL Server 2005, what is a good way to determine what length I should use for the column?

For example, I am encrypting a column, its maxlength is about 30 characters, but when encrypted, the encrypted value extends from between 50 and no more than 68 characters-

So if I had a column with a max of 500 or so characters, how could I know what varbinary length I should set it to if I were to encrypt it, without actually finding the highest value I could possibly fit into the field?

Is it good practice to just make it a varbinary(max) field?

-rob

My recommendation would be to create a dummy value of the maximum possible size (for example, on a varchar(500), create a 500 characters strings) and encrypt it with the same key and parameters you would normally use. That way you know you have reserved enough space to accept the maximum plaintext value your application accepts.

Example:

CREATE SYMMETRIC KEY key_demo WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = 'My 53Cr3+ p@.zzw0rD'

go

OPEN SYMMETRIC KEY key_demo

DECRYPTION BY PASSWORD = 'My 53Cr3+ p@.zzw0rD'

go

DECLARE @.MaxPlaintext nvarchar(500)

DECLARE @.Ciphertext varbinary(8000)

SET @.MaxPlaintext = replicate (N'a', 500)

-- Notice that I am using Unicode and the length is in bytes = 1000

PRINT datalength( @.MaxPlaintext )

-- Using the basic encryption parameters

SET @.Ciphertext = EncryptByKey( key_guid('key_demo'), @.MaxPlaintext )

-- Length using default parameters

PRINT datalength( @.Ciphertext )

-- Using the optional parameters

SET @.Ciphertext = EncryptByKey( key_guid('key_demo'), @.MaxPlaintext, 1, 'Dummy value' )

-- Length using optional parameters

PRINT datalength( @.Ciphertext )

go

I wrote an article (SQL Server 2005 Encryption – Encryption and data length limitations, http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx) that explains a little bit more about the encryption length limitations in SQL Server 2005. In that article I have a formula that may also help, but I would recommend using the method shown above instead of the formula in the article.

I hope I was able to help you. Please let us know if you have any further questions or feedback.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||This example will work for my needs - I should be able to get away with that as it is not often that a user will use the full length of a column. Thanks for validating.|||Raul, thanks for the solution on encrypting large amount of data. We have tried your approach on some BLOBs (e.g. files stored as binaries). The performance is very dismal though it works. Downloading a 3MB file that's encrypted took more than 45 seconds (the same file without encryption can be downloaded from the same webpage in 5 seconds). Any suggestions? I understand that SQL Server is not an efficient way to store files to begin with but we have to use it due to data center restrictions.|||

Hey Bob,

A lot of factors will impact the performance. Given the dramatic difference in times, I'm wondering if this is actually caused by the query you are using the retrieve the data? Are you only decrypting the blob or are other columns in the table encrypted as well?

I will run some tests on this, but if it is possible can you share the queries you are using with us? Or at least sample queries which approximate how you are retrieving the data?

Thanks,

Sung

|||

Sung:

There is only one field in the DB that's encrypted, the one containing the BLOB. The query is pretty much a copy of what Raul posted on his blog:

--
<edited to remove the source code sample>

|||

Unfortunately the work around I give is not very efficient with data that is larger than a few segments.

The main problem with this workaround (in terms of efficiency) is that the overhead SQL Server needs to pay besides the direct encryption/decryption (lookup for key in key ring, initialize key in cryptographic provider, verify inner header for consistency, etc.) will hit you multiple times for each blob. In the sample you mentioned of a 3MB file, you will be affected by this overhead more than 380 times.

Given the length and nature of the data you are manipulating, I would suggest using a CLR UDF instead and encrypt using CLR cryptographic APIs. The performance should be much better and also much more efficient in terms of space needed (not to mention a much cleaner code than my workaround).

I hope this information will help. Thanks a lot for your feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul, thanks for the reply. would a CLR UDF approach still use the SQL 2005's key/certificate infrstructure or it's completely .NET solution with separate keys? I have done file encryptions in .NET using X509 cert and symmetric keys, which are pretty easy to implement, but I'd like to leverage the keys created in SQL 2005 so the key management is consistent with other encrypted data in storage.

In addition, if you could show me some examples of using CLR UDF for SQL encryption that'd be great.

Thanks again

|||

Actually I was thinking of doing the whole key management and encryption directly in CLR due to the huge overhead you would have to pay for using the SQL Server infrastructure for large objects.

Another solution I can think of using SQL Server key management infrastructure would be to split the original plaintext in multiple rows and leverage on multiple client connections to encrypt/decrypt in parallel. For example: Create a table that will store the ciphertext as fragments in multiple rows (i.e. file_id, file_fragment_id used as primary key); your client can split the plaintext in segments (you could use a fixed length for the segments to make the file SEEK easier, let’s say 7K per segment) and using multiple connections send in parallel encrypt and insert requests to SQL Server (the fragment_id will be relative to the offset of the plaintext). As the fragments will be encrypted independently, they can also be decrypted independently and assembled again in the client application after decryption.

Hope this idea will work for you, or at least give you some other ideas.

-Raul Garcia

SDE/T

SQL Server Engine