SQL Server 2005 Encryption – Encryption and data length limitations

Article
11/23/2005

SQL Server 2005 encryption solution has some limitations and one of them that has raised a few questions is the limited amount of plaintext that can be encrypted. I hope this article helps answer this question.

What is the limit on the data length that can be encrypted?
The answer to this question is a little bit more complex than a simple number: the limit on the input (plaintext) really depends on the output (ciphertext), and it depends on the key you are using and on whether you are using optional features of the encryption builtins.

You can use the following formula to predict the length of the ciphertext that will result from calling EncryptByKey based on the plaintext length, the key algorithm used and whether the optional authenticator parameter is being used or not. Note: this formula is valid only for the DES and AES key families, it is not valid for RC4, and I would recommend to not even use RC4 at all)

@CipherLen = FLOOR( (8 +@PTLen + (@UsesAuth * @HASHLEN) ) / @BLOCK) + 1 ) * @BLOCK + 16 + @BLOCK + 4

Where:
@CipherLen:     The ciphertext length in bytes
@PTLen:           The plaintext length in bytes
@UsesAuth:      1 if using the optional authenticator parameter, 0 otherwise
@HASHLEN:   20 bytes for SHA1 (The authenticator parameter adds a SHA1 hash to the plaintext)
@BLOCK:       The length in bytes per block. 8 for the DES family, 16 for AES

Now let’s explain every part of the formula to make it easier to understand:
FLOOR( (
8                                     Encrypted internal header
+@PTLen                      Plaintext length
+ (@UsesAuth * @HASHLEN)            If the authenticator parameter is used, the plaintext will include a hash derived from it.
) / @BLOCK) + 1 ) * @BLOCK    This portion of the formula as a whole predicts the length of the ciphertext, including padding. Note: If the plaintext length fits exactly in one block, there will be an additional block of padding. This part of the formula reflects this behavior
   + 16     Key GUID (not encrypted).    This information is used during decryption to identify the key that needs to be used
   + @BLOCK                Initialization vector (IV)
   + 4                               Internal header that describes the BLOB version number

Once we have reviewed this formula, we can discuss the limitations on the encryption builtins: The output (ciphertext) is limited to up to 8000 bytes; what does this mean? This means that the plaintext limit is a little bit below the 8000 bytes (i.e. for AES encryption, using the authenticator parameter it should be around 7920 bytes ). If you try to encrypt a larger plaintext, the builtin will fail (return null) as it won’t be able to fit all the output in the available buffer.

For certificates, the way to calculate the maximum length of plaintext we can encrypt, as well as the ciphertext length, is easier because it is based on key modulus.
@Cipher_len = key_modulus (regardless of the plaintext length)
@Max_Plaintext_len = key_modulus – 11

By default, the self-signed certificates created by SQL Server 2005 have a 1024 bit modulus = 128 bytes, therefore the cipher texts are always 128 bytes and the maximum plaintext that can be encrypted is 117 bytes. The same formula applies for any certificate you may want to export into the system (the limit is 3456 bits for the private keys modulus).

For more information on this one, I recommend you to go to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptencrypt.asp and look at the remarks section for CryptEncrypt.

Should I always use this formula to calculate the size of my encrypted columns?
I strongly recommend against considering the result of the formula as a hardcoded length for your encrypted columns. I would suggest to estimate what would be the expected maximum ciphertext length (either by creating a sample using EncryptByKey directly on your system or by using the formula) and add a little bit of extra space. My personal suggestion would be to reserve at least 1 extra block (8 bytes for DES family, 16 bytes for AES), but it would really depend on the specific scenario.

The formula I shared here is a way to predict the length of the plaintext, but there is no guarantee that this formula will work for future algorithms or even for the existing algorithms in future versions of the product. As you have noticed, SQL Server encryption includes a few fields that are not part of the ciphertext itself, and in future versions of the product there may be a need to include new fields.

But I really need to encrypt BLOBs larger than 8000 bytes! Is there anything I can do?
Using the native solution, there is nothing you can do. Your application will need to slice the input before encrypting it.
I have created a small sample code that creates a UDF (user defined function) that is essentially a wrapper around EncryptByKey. It basically partitions the data in different pieces, encrypts them, and then pastes them together in a single BLOB that can be decrypted by its counterpart wrapper UDF for DecryptByKey. I created two different set of UDFs, one for symmetric keys and one for certificates.

The main idea behind this demo is to split the BLOB we want to encrypt in smaller segments that we can encrypt and then concatenate their ciphertexts in such a way that we can recover the individual segments, decrypt them, and paste them again in a single LOB (the original plaintext).

Demo

/***********************************************************************
* This posting is provided "AS IS" with no warranties,
* and confers no rights.
*
* Authors: Raul Garcia,
* Tanmoy Dutta,
* Pankaj Kamat,
* Laurentiu Cristofor
* Date: 11/07/2005
* Description:
*
* Create a scalar functions that allow encryption and decryption
* of large objects (> 8000 bytes).
*
* (c) 2005 Microsoft Corporation. All rights reserved.
*
**********************************************************************/

CREATE DATABASE demo_LobEncryption
go

USE demo_LobEncryption
go

--------------------------------------------------------------------
-- Create a new Encryption function – EncryptLob.
--
-- This is a wrapper around the EncryptByKey builtin
-- In order to keep this demo simple, it doesn't support
-- all the options of that builtin
--
-- PARAMETERS
-- @key_guid: Symmetric key GUID,
-- equivalent to EncryptByKey’s first argument.
-- This key needs to be already opened
-- before calling the function
-- @lob: The plaintext to encrypt.
-- Limits for the datalength are defined
-- by the varbinary(max) data type
-- RETURN VALUE
-- varbinary(max): If the encryption succeeded,
-- it will return the encrypted data
-- as described in NOTES
-- NULL is returned if there is any error
-- NOTES:
-- The plaintext will be split in blocks that can be
-- encrypted individually and then will be concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.EncryptLob(
@key_guid uniqueidentifier,
@lob varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @PlaintextColumnLimit int
declare @Cipher varbinary(max)

-- Limit for the PT block
SET @PtLimit = 7800
SET @CurPos = 1
SET @Total = datalength( @lob )
SET @Cipher = null

---- Simple validation of the input parameters

if(
@lob is not null
AND encryptbykey( @key_guid, 0x00 ) is not null
-- Can we encrypt with the key specified by the GUID
)
BEGIN
WHILE @CurPos <= @Total
BEGIN
-- Get a new PT block
SELECT @Aux = substring( @Lob, @CurPos, @PtLimit )
SET @CurPos = @CurPos + @PtLimit

-- Encrypt the PT block
SET @Aux = encryptbykey(@key_guid, @Aux)
-- If any block failed to decrypt,
-- we should just return NULL (failed)
if( @Aux is null )
return null

-- Get the ciphertext length
-- 2 bytes should be enough
SET @AuxLen = datalength( @Aux )
SET @Aux = convert( binary(2), @AuxLen ) + @Aux

-- Only set the @Cipher for the first block,
-- otherwise concatenate the existing data
-- with the new cipher block
if( @Cipher is null )
SET @Cipher = @Aux
ELSE
SET @Cipher = @Cipher + @Aux
END
END

return @Cipher
END
go
-------------------------------------------------------------------
-- Create a new Decryption function - DecryptLob.
--
-- This is a wrapper around the decryptByKey builtin.
-- It is the counterpart of the EncryptLob function.
--
-- PARAMETERS
-- @Cipher: A ciphertext that was generated by
-- EncryptLob.
-- The decryption key needs to be already
-- opened before calling the function
-- RETURN VALUE
-- varbinary(max): If the decryption succeeded, it will
-- return the plaintext as varbinary(max)
-- NULL is returned if there is any error
--
-- NOTES:
-- The ciphertext will be split in blocks that can be
-- decrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.DecryptLob( @Cipher varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @LobPt varbinary(max)

SET @CurPos = 1
SET @Total = datalength( @Cipher )
SET @LobPt = null

-- No op for null or empty data
if( @Cipher is not null
AND @Total > 0
)
BEGIN
WHILE @CurPos <= @Total
BEGIN
-- Read the next cipher block length
SELECT @AuxLen = substring( @Cipher, @CurPos, 2 )
SET @CurPos = @CurPos + 2

-- Length field validation.
-- Any unexpected length will result in error
if( @AuxLen <= 0 OR @AuxLen > 8000 )
return null

-- Get the next cipher block
SELECT @Aux = substring( @Cipher, @CurPos, @AuxLen )
SET @CurPos = @CurPos + @AuxLen

-- If there is any discrepancy,
-- it is either a data truncation error
-- (cipher was truncated)
-- or a data corruption error.
-- We will fail the whole operation.
if( datalength( @Aux ) <> @AuxLen )
return null

-- Decrypt the current cipher block
SET @Aux = decryptbykey( @Aux )

-- ... and make sure we could decrypt it.
-- Again, any error here will terminate
-- the operation and return null
if( @Aux is null )
return null

-- Either set (if first block) or concatenate
-- to the available PT we already decrypted
if( @LobPt is null )
SET @LobPt = @Aux
ELSE
SET @LobPt = @LobPt + @Aux
END
END

return @LobPt
END
go

---------------------- Test
CREATE SYMMETRIC KEY key1 WITH ALGORITHM = AES_192 ENCRYPTION BY PASSWORD = '|\/|y p@22\/\/0rD'
go

OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = '|\/|y p@22\/\/0rD'
go

--------------- Quick Demo
declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 15 -- PT will be a little bit less than 2MB
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLob( key_guid('key1'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
if( @y is not null )
BEGIN
SELECT @z = convert( varchar(max), dbo.DecryptLob( @y ))

if( @z is not null )
BEGIN
SELECT datalength( @x) as 'original PT len', datalength(@z) as 'decrypted PT len', substring( @z, datalength(@z) - 16, 100) as 'Last 16 chars'
if( @z = @x )
print 'decrypted PT == original PT'
else
print 'Unexpected error. PTs didn''t match'
END
ELSE
print 'Failed to decrypt. Make sure the key is opened'
END
ELSE
print 'Failed to encrypt. Make sure the key is opened'
go

--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
-- Create a new Encryption function - EncryptLobByCert.
--
-- This is a wrapper around EncryptByCert builtin
-- In order to keep this demo simple, it doesn't support
-- all the options EncryptByCert supports.
--
-- PARAMETERS
-- @cert_id: Cert ID, equivalent to EncryptByCert
-- first argument.
-- @lob: The plaintext to encrypt.
-- Limits for the datalength are defined
-- by the varbinary(max) data type
-- RETURN VALUE
-- varbinary(max): If the encryption succeeded,
-- it will return a byte stream that
-- with the encrypted data. The byte stream
-- format is described in NOTES
-- NULL if there is any error
-- NOTES:
-- The plaintext will be split in blocks that can be
-- encrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
-- Remember that asymmetric key encryption/decryption is orders
-- of magnitude more expensive than symmetric key
-- encryption/decryption.
-- We recommend avoiding the use of asymmetric key encryption for
-- large amounts of data, but these functions may be used to
-- protect data < 1000 bytes that are larger than what
-- the SQL builtins are currently supporting
-- (for example, data larger than 117 bytes)
CREATE FUNCTION dbo.EncryptLobByCert( @cert_id int, @lob varbinary(max) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @PlaintextColumnLimit int
declare @Cipher varbinary(max)

SET @Aux = encryptbycert( @cert_id, 0x00 )
if( @Aux is null ) -- Return null if we cannot encrypt by the specified cert
return null

-- Calculate the limit for the PT block
SET @PtLimit = datalength( @Aux ) - 11

SET @CurPos = 1
SET @Total = datalength( @lob )
SET @Cipher = null

---- Simple validation of the input parameters
WHILE @CurPos <= @Total
BEGIN
-- Get a new PT block
SELECT @Aux = substring( @Lob, @CurPos, @PtLimit )
SET @CurPos = @CurPos + @PtLimit

-- Encrypt the PT block
SET @Aux = encryptbycert( @cert_id, @Aux)
-- If any block failed to decrypt,
-- we should just return NULL (failed)
if( @Aux is null )
return null

-- Get the ciphertext length
-- 2 bytes should be enough
SET @AuxLen = datalength( @Aux )
SET @Aux = convert( binary(2), @AuxLen ) + @Aux

-- Only set the @Cipher for the first block,
-- otherwise concatenate the existing data with
-- the new cipher block
if( @Cipher is null )
SET @Cipher = @Aux
ELSE
SET @Cipher = @Cipher + @Aux
END

return @Cipher
END
go

---------------------------------------------------------------------
-- Create a new Decryption function - DecryptLobByCert.
--
-- This is a wrapper around DecryptByCert builtin.
-- It is the counterpart of EncryptLobByCert function.
--
-- PARAMETERS
-- @cert_id: Cert ID, equivalent to EncryptByCert
-- first argument.
-- @Cipher: A ciphertext that was generated by
-- EncryptLobByCert.
-- @Password: Password for the private key.
-- If encrypted by DBMK, you should use null
-- for this parameter.
--
-- RETURN VALUE
-- varbinary(max): If the decryption succeeded, it will
-- return the plaintext as varbinary(max)
--
-- NOTES
-- The plaintext will be split in blocks that can be
-- encrypted individually and then concatenated together:
-- ([2 bytes - ciphertext block length][ciphertext block])
-- per plaintext block
CREATE FUNCTION dbo.DecryptLobByCert(
@cert_id int,
@Cipher varbinary(max),
@Password nvarchar(4000) )
returns varbinary(max)
as
BEGIN
declare @PtLimit int
declare @Total int
declare @CurPos int
declare @Aux varbinary(8000)
declare @AuxLen int
declare @LobPt varbinary(max)

SET @CurPos = 1
SET @Total = datalength( @Cipher )
SET @LobPt = null

-- Length field validation. Any unexpected
-- length will result in error
if( @AuxLen <= 0 OR @AuxLen > 8000 )
return null

-- Get the next cipher block
SELECT @Aux = substring( @Cipher, @CurPos, @AuxLen )
SET @CurPos = @CurPos + @AuxLen

-- Decrypt the current cipher block
SET @Aux = decryptbycert( @cert_id, @Aux, @Password )

-- ... and make sure we could decrypt it.
-- Again, any error here will terminate
-- the operation and return null
if( @Aux is null )
return null

-- Either set (if first block) or concatenate
-- to the available PT we already decrypted
if( @LobPt is null )
SET @LobPt = @Aux
ELSE
SET @LobPt = @LobPt + @Aux
END
END

return @LobPt
END
go

------------ Test
CREATE CERTIFICATE certTest ENCRYPTION BY PASSWORD = 'c3R+ p@zz\/\/0Rd!' WITH SUBJECT = 'Demo - LOB encryption'
go

declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 4 -- PT will be a little bit more than 1k
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLobByCert( cert_id('CertTest'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
--select @y as CipherTextLob
if( @y is not null )
BEGIN
SELECT @z = convert( varchar(max), dbo.DecryptLobByCert( cert_id('CertTest'), @y, N'c3R+ p@zz\/\/0Rd!' ))

------ Test2
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D8|\/||< p455\/\/oRx!'
go

CREATE CERTIFICATE certTest2 WITH SUBJECT = 'Demo - LOB encryption DBMK protected'
go

declare @x varbinary(max)
declare @y varbinary(max)
declare @z varchar(max)
declare @i int
set @i = 0
SET @x = convert( varbinary(max), '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
while @i < 4 -- PT will be a little more than 1k
BEGIN
SET @i = @i + 1
SET @x = @x + @x
END
SELECT @y = dbo.EncryptLobByCert( cert_id('CertTest2'), @x )
SELECT datalength( @y ) as 'Ciphertext length'
--select @y as CipherTextLob
if( @y is not null )
BEGIN
-- Use null for the password parameter
SELECT @z = convert( varchar(max), dbo.DecryptLobByCert( cert_id('CertTest2'), @y, null ))

DROP DATABASE demo_LobEncryption
go
----------------------- END OF DEMO -----------------------

SQL Server 2005 Encryption – Encryption and data length limitations

Additional resources