SQL Server – Cell Level Encryption – Data Size

Background

A couple of months ago  as we were starting on a new Encryption project one of the developers that I will be working with asked me how big relative to the original data size will encrypted data be. I gave an answer based on thought, but not one based on actual exercise.

Exercise

Let us do better.

DDL

Declare our data structure

 

Create Schema


if schema_id('Constant') is null
begin

exec('create schema [Constant] authorization [dbo]')

end

 

Create View


if object_id('Constant.vw_Datatype') is null
begin

exec('create view [Constant].[vw_Datatype] as select [shell] = 1/0')

end
go

/*
exec sp_help '[Constant].[vw_Datatype]'

select *
from [Constant].[vw_Datatype]

*/
alter view [Constant].[vw_Datatype]
as

/*

SmallDatetime:
https://msdn.microsoft.com/en-us/library/ms182418.aspx
1900-01-01 through 2079-06-06
January 1, 1900, through June 6, 2079

Datetime
https://msdn.microsoft.com/en-us/library/ms187819.aspx
January 1, 1753, through December 31, 9999

tinyint
0 to 255

smallint
-2^15 (-32,768) to 2^15-1 (32,767)

bigint
-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

*/
select
[smalldatetimeMin] = cast('1900-01-01' as smalldatetime)
, [smalldatetimeMax] = cast('2079-06-06' as smalldatetime)

, [datetimeMin] = cast('1753-01-01' as datetime)
, [datetimeMax] = cast('9999-12-31' as datetime)

, [tinyintMin] = cast(0 as tinyint)
, [tinyintMax] = cast(255 as tinyint)

, [smallintMin] = cast(-32768 as smallint)
, [smallintMax] = cast(32767 as smallint)

, [intMin] = cast(-2147483648 as int)
, [intMax] = cast(2147483647 as int)

, [bigintMin] = cast(-9223372036854775808 as bigint)
, [bigintMax] = cast(9223372036854775807 as bigint)

, [charMin] = 'A'
, [charMax_4000] = cast(replicate('Z', 4000) as char(4000))
, [charMax_8000] = cast(replicate('Z', 8000) as char(8000))

, [ncharMin] = cast(N'A' as nchar(1))
, [ncharMax_2000] = cast(replicate(N'Z', 2000) as nchar(2000))
, [ncharMax_4000] = cast(replicate(N'Z', 4000) as nchar(4000))

go

Create Certificate and Symmetric Key

Create Certificate


CREATE CERTIFICATE [certPIA]
AUTHORIZATION dbo
WITH SUBJECT = 'certPIA'
;

Create Symmetric Key

CREATE SYMMETRIC KEY [SymmetricKeyPIA]
AUTHORIZATION [dbo]
WITH
ALGORITHM = AES_256
, IDENTITY_VALUE = 'SymmetricKeyPIA'
, KEY_SOURCE = 'SymmetricKeyPIA'

ENCRYPTION BY CERTIFICATE [certPIA]
;

Review

Tinyint


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

select
[tinyintMin]

, [tinyintMinEncryptedLen]
= datalength([tinyintMinEncrypted])

, [tinyintMax]

, [tinyintMaxEncryptedLen]
= datalength([tinyintMaxEncrypted])

from
(

select

[tinyintMin]
= [tinyintMin]

, [tinyintMinEncrypted]
= EncryptByKey(@keyGUID,cast([tinyintMin] as varbinary(max)), 0 )

, [tinyintMax]
= [tinyintMax]

, [tinyintMaxEncrypted]
= EncryptByKey(@keyGUID,cast([tinyintMax] as varbinary(max)), 0 )

from [Constant].[vw_Datatype]

) tblE

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

 

Output:
tinyInt

 

Smallint

 


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

select
[smallintMin]

, [smallintMinEncryptedLen]
= datalength([smallintMinEncrypted])

, [smallintMax]

, [smallintMaxEncryptedLen]
= datalength([smallintMaxEncrypted])

from
(

select

[smallintMin]
= [smallintMin]

, [smallintMinEncrypted]
= EncryptByKey(@keyGUID,cast([smallintMin] as varbinary(max)), 0 )

, [smallintMax]
= [smallintMax]

, [smallintMaxEncrypted]
= EncryptByKey(@keyGUID,cast([smallintMax] as varbinary(max)), 0 )

from [Constant].[vw_Datatype]

) tblE

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:

smallInt

 

int


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

select
[intMin]

, [intMinEncryptedLen]
= datalength([intMinEncrypted])

, [intMax]

, [intMaxEncryptedLen]
= datalength([intMaxEncrypted])

from
(

select

[intMin]
, [intMinEncrypted]
= EncryptByKey(@keyGUID,cast([intMin] as varbinary(max)), 0 )

, [intMax]
, [intMaxEncrypted]
= EncryptByKey(@keyGUID,cast([intMax] as varbinary(max)), 0 )

from [Constant].[vw_Datatype]

) tblE

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:
int

bigint


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

select
[bigintMin]

, [bigintMinEncryptedLen]
= datalength([bigintMinEncrypted])

, [bigintMax]

, [bigintMaxEncryptedLen]
= datalength([bigintMaxEncrypted])

from
(

select

[bigintMin]
, [bigintMinEncrypted]
= EncryptByKey(@keyGUID,cast([bigintMin] as varbinary(max)), 0 )

, [bigintMax]
, [bigintMaxEncrypted]
= EncryptByKey(@keyGUID,cast([bigintMax] as varbinary(max)), 0 )

from [Constant].[vw_Datatype]

) tblE

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:
bigint

smalldatetime


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

select
[smalldatetimeMin]

, [smalldatetimeMinEncryptedLen]
= datalength([smalldatetimeMinEncrypted])

, [smalldatetimeMax]

, [smalldatetimeMaxLen]
= datalength([smalldatetimeMaxEncrypted])

from
(

select

[smalldatetimeMin]

, [smalldatetimeMinEncrypted]
= EncryptByKey(@keyGUID,cast([smalldatetimeMin] as varbinary(max)), 0 )

, [smalldatetimeMax]

, [smalldatetimeMaxEncrypted]
= EncryptByKey(@keyGUID,cast([smalldatetimeMax] as varbinary(max)), 0 )

from [Constant].[vw_Datatype]

) tblE

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:
smallDatetime

 

datetime


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

select
[datetimeMin]

, [datetimeMinEncryptedLen]
= datalength([datetimeMinEncrypted])

, [datetimeMax]

, [datetimeMaxEncryptedLen]
= datalength([datetimeMaxEncrypted])

from
(

select

[datetimeMin]
, [datetimeMinEncrypted]
= EncryptByKey(@keyGUID,cast([datetimeMin] as varbinary(max)), 0 )

, [datetimeMax]
, [datetimeMaxEncrypted]
= EncryptByKey(@keyGUID,cast([datetimeMax] as varbinary(max)), 0 )

from [Constant].[vw_Datatype]

) tblE

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:

Datetime

CHAR / VARCHAR


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

select
[charMin]

, [charMinLen]
= datalength([charMin])

, [charMinEncryptedLen]
= datalength([charMinEncrypted])

, [charMax_4000Len]
= datalength([charMax_4000])

, [charMax_4000EncryptedLen]
= datalength([charMax_4000Encrypted])

, [charMax_8000Len]
= datalength([charMax_8000])

, [charMax_8000EncryptedLen]
= datalength([charMax_8000Encrypted])

from
(

select

[charMin]
, [charMinEncrypted]
= EncryptByKey(@keyGUID,cast([charMin] as varbinary(max)), 0 )

, [charMax_4000]
, [charMax_4000Encrypted]
= EncryptByKey(@keyGUID,cast([charMax_4000] as varbinary(max)), 0 )

, [charMax_8000]
, [charMax_8000Encrypted]
= EncryptByKey(@keyGUID,cast([charMax_8000] as varbinary(max)), 0 )

from [Constant].[vw_Datatype]

) tblE

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:

char

NCHAR/ NVARCHAR


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

select
[ncharMin]

, [ncharMinEncryptedLen]
= datalength([ncharMinEncrypted])

, [ncharMax_2000Len]
= datalength([ncharMax_2000])

, [ncharMax_2000EncryptedLen]
= datalength([ncharMax_2000Encrypted])

, [ncharMax_4000EncryptedLen]
= datalength([ncharMax_4000Encrypted])

from
(

select

[ncharMin]
, [ncharMinEncrypted]
= EncryptByKey(@keyGUID,cast([ncharMin] as varbinary(max)), 0 )

, [ncharMax_2000]
, [ncharMax_2000Encrypted]
= EncryptByKey(@keyGUID,cast([ncharMax_2000] as varbinary(max)), 0 )

, [ncharMax_4000]
, [ncharMax_4000Encrypted]
= EncryptByKey(@keyGUID,cast([ncharMax_4000] as varbinary(max)), 0 )

from [Constant].[vw_Datatype]

) tblE

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:

nchar

 

Findings Tabulated

 

Data Type Sample 1 Sample 2 Sample 3 Sample 4
tinyint Value 0 – Encrypted Length52 Value – Encrypted Length 52
smallint Value -32768 – Encrypted Length 52 Value 32767 Encrypted Length 52
int Value -2147483648 – Encrypted Length 52 Value 2147483647 Encrypted Length 52
bigint Value -9223372036854775808 – Encrypted Length 68 Value 9223372036854775807 Encrypted Length 68
smalldatetime Value 1900-01-01 00:00:00 – Encrypted Length 52 Value 2079-06-06 00:00:00 Encrypted Length 52
datetime Value 1753-01-01  – Encrypted Length 68 Value 9999-12-31  Encrypted Length 68
char/varchar Value A – Encrypted Length 52 Value char/length of 2000 Encrypted Length 2052 Value char/length of 4000 Encrypted Length 4052 Value char/length of 8000 Encrypted Length ?
nchar/nvarchar Value A – Encrypted Length 52 Value char/length of 2000 Encrypted Length 4052 Value char/length of 4000 Encrypted Length ?

 

 

Error

Error Scenario

By the way, Encryptbykey fails to trigger an error if it fails to successfully encrypt a value.

The basis of error includes data that is too long.

Sample Code

Here is sample code:


declare @symmetricKey varchar(60)
declare @keyGUID uniqueIdentifier

declare @charData varchar(8000)
declare @charDataEncypted varbinary(8000)

declare @iLength int
declare @iLengthMax int

declare @log varchar(255)
declare @iFailedPosition int
declare @strFailedLocation varchar(255)

declare @charDataLength int
declare @charDataEncyptedLength int

declare @strCharDataLength varchar(60)
declare @strCharDataEncyptedLength varchar(60)

declare @LOG_FORMAT_SUCCESS varchar(255)
declare @LOG_FORMAT_FAILED varchar(255)

declare @CHAR_TAB varchar(30)

set @LOG_FORMAT_SUCCESS = 'Successful at Plain Text Length %s and Encrypted Text Length %s'
set @LOG_FORMAT_FAILED = 'Failed at Position %s'

OPEN SYMMETRIC KEY [SymmetricKeyPIA]
DECRYPTION BY CERTIFICATE [certPIA];

set @CHAR_TAB = char(9)

set @symmetricKey = 'SymmetricKeyPIA'
set @keyGUID = key_guid(@symmetricKey)

set @iLength = 4000
set @iLengthMax = 8000

set @charData = replicate('*', @iLength)
set @charDataEncypted
= EncryptByKey(@keyGUID,cast(@charData as varbinary(max)), 0 )

set @iFailedPosition = -1

while (

( @charDataEncypted is not null)
and ( @iLength <= @iLengthMax)
)
begin

set @charData = @charData + '?'

set @charDataEncypted
= EncryptByKey(@keyGUID,cast(@charData as varbinary(max)), 0 )

IF (@@ERROR <> 0)
begin

print @CHAR_TAB
+'Error Number ' + cast(@@ERROR as varchar(60))

end

if (@charDataEncypted is null)
begin

set @iFailedPosition = @iLength
break;

end

set @charDataLength = len(@charData)
set @charDataEncyptedLength = len(@charDataEncypted)

set @iLength = @iLength + 1

end

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

if (@iFailedPosition != -1)
begin

set @strCharDataLength =cast(@charDataLength as varchar(60))

set @strCharDataEncyptedLength =cast(@charDataEncyptedLength as varchar(60))

set @strFailedLocation =cast(@iFailedPosition as varchar(60))

exec master.dbo.xp_sprintf
@log output
, @LOG_FORMAT_SUCCESS
, @strCharDataLength
, @strCharDataEncyptedLength
print @log

exec master.dbo.xp_sprintf
@log output
, @LOG_FORMAT_FAILED
, @strFailedLocation

print @log

end
else
begin

print 'Good'

end

 

Connect Item

  1. Error 735926
    • Submitted By – Erland Sommarskog
    • Date – April 8th, 2012
    • Name
      • Topic for EncryptByAsymKey fails to mention that return value may be NULL if input is too long. – by Erland Sommarskog
    • Description
    • Resolved ( Posted by Rick )
      • Thank you. Fixed in both SQL Server 2008 R2 and SQL Server 2012 by adding the following text:
        EncryptByAsymKey return NULL if the input exceeds a certain number of bytes, depending on the algorithm. The limits are: a 512 bit RSA key can encrypt up to 53 bytes, a 1024 bit key can encrypt up to 117 bytes, and a 2048 bit key can encrypt up to 245 bytes. (Note that in SQL Server, both certificates and asymmetric keys are wrappers over RSA keys.)This will take a few weeks to appear online for SQL Server 2012 and up to a couple months for SQL Server 2008 R2.
  2. Error 2043252
    • Submitted By :- Daniel Adeniji
    • Date – November 20th, 2015
    • Name
      • EncryptByKey fails without indicating error through @error nor raised exception

Quotes

I love quotes and really dig this one from Aaron Bertrand, SQL Server MVP.

Best approaches for running totals – updated for SQL Server 2012
I’ll restate that I don’t believe this approach is safe for production, regardless of the testimony you’ll hear from people indicating that it “never fails.” Unless behavior is documented and guaranteed, I try to stay away from assumptions based on observed behavior.

Summary

Again, taking to writing, we can go back and give an honest answer to our Developer’s question; as a reminder the question is how does encryption change the data’s foot-print.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s