Martin Smith – StackOverFlow – DECRYPTBYKEY slower on SQL Server 2014 than SQL Server 2012

Intro

In our last post we spoke a bit about encryption, specifically sys.fn_varbintohexsubstring.

As I googled to find supportive and collaborative sources, I ran into Martin Smith’s question on encryption.

As Martin Smith’s word are far better, it does make sense to try to distill it.

Here are his words…

Martin Smith – DECRYPTBYKEY slower on SQL Server 2014 than SQL Server 2012

Link

We have been using symmetric keys for encryption/decryption on some SQL Server 2012 instances for a few years. We recently installed some new instances of SQL Server 2014 and came across some performance issues decrypting the data on SQL Server 2014 installations.

Consider a table looking like this:


CREATE TABLE [dbo].[tblCertTest]
(
     [article_id_enc] [varbinary](100) NOT NULL
   , [article_id] [int] NULL
) ON [PRIMARY]

And keys and certificates created like this:


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passwrrrrd12'

CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'A label for this certificate'

CREATE SYMMETRIC KEY MySymmetricKeyName WITH
IDENTITY_VALUE = 'a fairly secure name',
ALGORITHM = AES_256,
KEY_SOURCE = 'a very secure strong password or phrase'
ENCRYPTION BY CERTIFICATE MyCertificateName;

Our data set has about 90000 rows, article_id is a 5 digit number. A bit simplified, article_id_enc is encrypted with the following command:


update tblCertTest 

set article_id_enc

    = ENCRYPTBYKEY(
                        KEY_GUID('MySymmetricKeyName')
                       ,convert(varbinary(100), article_id)
                  )

We have applied all available hotfixes, we’ve tried with different instances of both SQL Server 2012 and SQL Server 2014 with different setups like ssd disks, ram disks etc.

We have tried the query locally on the SQL Server and remote.

Execution plans and indexes are the same on all servers.

This SELECT statement takes about 50 ms on any SQL Server 2012 server, including simple development machines. On any SQL Server 2014 server (including really powerful ones) the query takes at least 1500 ms.


OPEN SYMMETRIC KEY MySymmetricKeyName
       DECRYPTION BY CERTIFICATE MyCertificateName

SELECT CONVERT(int, DecryptByKey(article_id_enc))
FROM dbo.tblCertTest

Any suggestions on why the query is performing so bad on SQL Server 2014? What’s changed?

Continue reading

SQL Server – Symmetric Key – What permissions are needed to use?

Background

This is is something I have been trying to document for a while here. But, never quite got around to doing so.

It is Monday and though don’t really have a down time, I will take a little time to invest in the future.

 

Introduction

The basic question is that to use a symmetric key what are the minimal permissions needed?

 

LAB

Template

We will use Microsoft’s own sample available here to create a sample symmetric key.

Create Symmetric Key

Code


/*
 Create master key
*/
CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'My p@55w0Rd';
GO


/*
 Create certificate
*/
CREATE CERTIFICATE [cert_keyProtection] 
    WITH SUBJECT = 'Key Protection';
GO

/*
 Create Symmetric Key
*/
CREATE SYMMETRIC KEY [key_DataShare] WITH
      KEY_SOURCE = 'My key generation bits.'
    , ALGORITHM = AES_256, 
    , IDENTITY_VALUE = 'Key Identity generation bits.'
    , ENCRYPTION BY CERTIFICATE [cert_keyProtection]
    ;
GO

 

Create User

Code

We will create a simple database contained user as the user will only be used to validate access to symmetric keys.


create user [bookViewer] without login
go

 

Access Symmetric Key / As User

Let us attempt to access the symmetric key while connected as our “no privilege” user.

Code


set nocount on
set XACT_ABORT on
go

setuser ''
go

setuser 'bookViewer'
go

OPEN SYMMETRIC KEY [key_DataShare] 
    DECRYPTION BY CERTIFICATE cert_keyProtection;
GO

   SELECT [encrypted]
	= encryptbykey
             (
                  key_guid('key_DataShare')
                , 'MyData' 
             )
	GO


if exists
	(
   	   select *
	   from   sys.openkeys
	   where  key_name = 'key_DataShare'
	)
begin

   CLOSE SYMMETRIC KEY [key_DataShare] 

end
go

setuser ''
go

Error Message


Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'key_DataShare', because it does not exist or you do not have permission.

On Symmetric Key \ Grant View Definition to User

We will grant view definition privilege to the User.

Code


--Grant View Definition
GRANT VIEW DEFINITION ON SYMMETRIC KEY::[key_DataShare] TO [bookViewer]

Error Message

We went back and ran our test code, and came back with a new error message.


Msg 15151, Level 16, State 1, Line 2
Cannot find the certificate 'cert_keyProtection', because it does not exist or you do not have permission.

Explanation

  1. So the original error message stated that “Cannot find the symmetric key“. The new error message states “Cannot find the certificate

 

On Certificate \ Grant View Definition to User

Let us grant same view definition on the certificate.

Code


--Grant View Definition on Certificate
GRANT VIEW DEFINITION ON CERTIFICATE::[cert_keyProtection] TO [bookViewer]

Error


Msg 15151, Level 16, State 1, Line 2
Cannot find the certificate 'cert_keyProtection', because it does not exist or you do not have permission.

Explanation

  1. Same error “Cannot find the certificate

 

On Certificate \ Grant “Control” to User

Let us grant a different privilege; we will replace view definition with grant control on certificate.

Code


--Revoke view definition on certificate
REVOKE VIEW DEFINITION ON CERTIFICATE::[cert_keyProtection] FROM [bookViewer]

--Grant Control Certificate
GRANT CONTROL ON CERTIFICATE::[cert_keyProtection] TO [bookViewer]

Validation Code


set nocount on
set XACT_ABORT on
go

setuser ''
go

setuser 'bookViewer'
go

OPEN SYMMETRIC KEY [key_DataShare] 
   DECRYPTION BY CERTIFICATE cert_keyProtection;
GO

   select 
	  [sourced] = 'sys.openkeys'
	, tblOK.*
   from   sys.openkeys tblOK

   SELECT [encrypted]
	= encryptbykey
              (
                 key_guid('key_DataShare')
                 , 'MyData' 
              )

if exists
	(
	   select *
	   from   sys.openkeys
	   where  key_name = 'key_DataShare'
	)
begin

	CLOSE SYMMETRIC KEY [key_DataShare] 

end
go

setuser ''
go


Output:

validation-code-output

 

Quick Summary

To access a symmetric key, the minimum permissions required are:

  1. On the Symmetric Key, grant View Definition
  2. On the Certificate, grant Control

 

Crediting

Once again crediting Stack Overflow; specifically

  1. Mitolo – Cannot find the symmetric key ‘key_DataShare’, because it does not exist or you do not have permission

 

Listening

Listening to Birds of Tokyo – Lanterns

SQL Server – Encryption and Updating Related Tables Via Trigger

Background

We have a pretty straight forward Trigger that updates corresponding tables via a Trigger.

The original query is straightforward, but as we implement encryption, we find that our code was breaking.

Code

Original Code


ALTER TRIGGER [dbo].[tr_pended_status_changed]
ON [dbo].[Delivery]
FOR UPDATE
AS

	declare @delID uniqueidentifier

	declare @pended char(1)
	declare @studentID varchar(100)
	declare @name1 varchar(255)
	declare @name2 varchar(255)
	declare @address1 varchar(255)
	declare @address2 varchar(255)
	declare @address3 varchar(255)
	declare @city varchar(100)
	declare @state varchar(25)
	declare @postcode varchar(40)
	declare @country varchar(100)
	declare @areaCD varchar(10)
	declare @phoneNbr varchar(30)

	if update(pended)
	begin

		select
			@delID 		= inserted.deliveryID,
			@pended	= inserted.pended,
			@studentID 	= inserted.receiverID,
			@address1 	= isnull(rtrim(ltrim(inserted.address1)),''),
			@city    		= isnull(rtrim(ltrim(inserted.city)),''),
			@postcode 	= isnull(rtrim(ltrim(inserted.postcode)),'')
		from inserted

		if (@pended='N' and (@address1='' or @city = ''  or @postcode=''))
		begin

			select
					@address1 	= address1,
					@address2 	= address2,
					@address3 	= address3,
					@city    		= city,
					@postcode 	= postcode,
					@state    	= state,
					@name1 	= first_name+' '+last_name,
					@country	= country,
					@areaCD	= area_code,
					@phoneNbr	= phone_nbr

			from Student

			where studentID = @studentID 

			begin TRANSACTION

				update Delivery
				set
						address1   		= @address1,
						address2   		= @address2,
						address3   		= @address3,
						city       		= @city,
						postcode	    = @postcode,
						state   		= @state,
						name_on_label1 	= @name1,
						country			= @country,
						area_cd 		= @areaCD,
						phone_nbr		= @phoneNbr

				where deliveryID = @delID

				if (@@error<>0)
					ROLLBACK TRANSACTION

				COMMIT TRANSACTION

		end -- if (@pended='N' and (@address1='' or @city = ''  or @postcode=''))

	end -- if update(pended) 

go

 

Revised Code

sys.symmetric_keys

Intent

In the revised code, we will attempt to cover the following goals:

  1. Access the inserted virtual table
    • Read Source data using decryptByKey to decrypt the encypted columns
  2. Save data
    • Apply decrypted-data to Destination table
      • Invoke EncryptByKey pass in Symmetric Key GUID, decrypted data, 1, Salt
      • Because we need the Symmetric Key GUID, we need to either hardcode the GUID or access the sys.symmetric_keys to read it based on our Key Name

 

Code

 

Code – Snippet

  /*
     Get KeyGUID
  */
  set @keyName = 'SMKEY'
 
  select @keyGUID = tblSK.[key_guid]
  from   sys.symmetric_keys tblSK
  where  tblSK.[name] = @keyName

 

Code – Whole

 


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_pended_status_changed]
ON [dbo].[Delivery]
FOR UPDATE
AS
begin

	set nocount on;

	declare @keyName varchar(60)
	declare @keyGUID uniqueidentifier

	if update([pended])
	begin

		/*
			Get KeyGUID
		*/
		set @keyName = 'SMKEY'

		select @keyGUID = tblSK.[key_guid]
		from   sys.symmetric_keys tblSK
		where  tblSK.[name] = @keyName

		if (@keyGUID is null)
		begin

			raiserror('KeyGUID is null', 16,1)

		end	

		/*
			Get Student's Address Information, and update dbo.Delivery Table
				Filtering:
					a) Pended Column Toggled
					b) And
						0] Pended = 'N'
						i] Address1 is null or Address1 is empty
						ii] City is empty
						iii] Post Code is empty
		*/
		update tblD
		set
			   address1   =
					encryptByKey
					(
						@keyGUID
						, cast(DecryptByKey(tblS.Address1, 1, itvfS.Salt) as varchar(100))
						, 1
						, itvfS.Salt
								)
			 , address2   =
					encryptByKey
					(
						@keyGUID
						, cast(DecryptByKey(tblS.Address2, 1, itvfS.Salt) as varchar(100))
						, 1
						, itvfS.Salt
					) 

		   	, city       = tblS.City

		        , postcode   = tblS.PostCode

		        , [state]    = tblS.[State]

			 , [country]   = tblS.country

		from [dbo].[Delivery] tblD

		inner join inserted tblIns

			on tblD.[deliveryID] = tblIns.[deliveryID]

		inner join [dbo].[Student] tblS

			on tblD.[ReceiverID] = tblS.[studentID]

		cross apply dbo.itvf_Salt(tblS.[studentID]) itvfS

		where ( tblIns.[pended] ='N' )

		and   (

				   (
						   (tblIns.[Address1] is null )
						or (tblIns.[Address1] = '' )
				   )
				or ( tblIns.city = '' )
			    or ( tblIns.postcode='')

		     )

	end -- if update(pended) 

end
go			

Error

Here is an error we started getting


SQLERR: The SELECT permission was denied on the object 'symmetric_keys', database 'mssqlsystemresource', schema 'sys'. PROC: SaveDeliveryRecord 

 

Execute As

Intent

  1. If you run into this problem at 1 AM, I will say to modify the Stored Procedure definition and add “with execute as owner
    • The running user, which is far less privilege, does not have explicit access to read from the sys.symmetric_keys view
    • Adding “Execute as owner” allows us to override the current user’s security cloak and instead rely on the object’s owner ( sa )

Code


ALTER PROCEDURE [Products].[SaveDeliveryRecord]
(
	@deliveryID uniqueidentifier,
	@receiverID varchar(100),
	@refID varchar(100),
	@packageCD varchar(15),
	@deliveryCode varchar(25),
	@deliveryTo varchar(15),
	@bPended char(1),
	@bActive char(1),
	@upgrade_to varchar(300) = null,
	@name_on_label1 varchar(255),
	@name_on_label2 varchar(255),
	@Address1 varchar(255),
	@Address2 varchar(255),
	@Address3 varchar(255),
	@City varchar(100),
	@State varchar(25),
	@PostCode varchar(40),
	@country varchar(100),
	@area_cd varchar(10),
	@phone_nbr varchar(30),
	@bSignatureReq char(1),
	@tracking_nbr varchar(255) = null,
	@sentDate datetime = null,
	@keyGUID uniqueidentifier = null
)
with execute as owner
AS
BEGIN
 ....
 ....

END

sys.openkeys

Intent

  1. We intentionally denied the public account access to sys.symmetric_keys
    • When running as a privilege user we were good, but once we started running as a less privileged user, we failed with select permission
  2. A better approach is to access sys.openkeys
    • MSFT docs says
      • This catalog view returns information about encryption keys that are open in the current session

Code


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_pended_status_changed]
ON [dbo].[Delivery]
FOR UPDATE
AS
begin

	set nocount on;

	declare @keyName varchar(60)
	declare @keyGUID uniqueidentifier

	if update([pended])
	begin

		/*
			Get KeyGUID
		*/
		set @keyName = 'SMKEY'

                /*
		select @keyGUID = tblSK.[key_guid]
		from   sys.symmetric_keys tblSK
		where  tblSK.[name] = @keyName
                */

		select @keyGUID = tblOK.[key_guid]
		from   sys.openkeys tblOK
		where  tblOK.[key_name] = @keyName


                /*
                   Security blanket, if Symmetric Key is not opened
                */
		if (@keyGUID is null)
		begin

		   select @keyGUID = tblSK.[key_guid]
		   from   sys.symmetric_keys tblSK
		   where  tblSK.[name] = @keyName

                end

		if (@keyGUID is null)
		begin

			raiserror('KeyGUID is null', 16,1)

		end	

		/*
			Get Student's Address Information, and update dbo.Delivery Table
				Filtering:
					a) Pended Column Toggled
					b) And
						0] Pended = 'N'
						i] Address1 is null or Address1 is empty
						ii] City is empty
						iii] Post Code is empty
		*/
		update tblD
		set
			   address1   =
					encryptByKey
					(
						@keyGUID
						, cast(DecryptByKey(tblS.Address1, 1, itvfS.Salt) as varchar(100))
						, 1
						, itvfS.Salt
								)
			 , address2   =
					encryptByKey
					(
						@keyGUID
						, cast(DecryptByKey(tblS.Address2, 1, itvfS.Salt) as varchar(100))
						, 1
						, itvfS.Salt
					) 

		   	, city       = tblS.City

		        , postcode   = tblS.PostCode

		        , [state]    = tblS.[State]

			 , [country]   = tblS.country

		from [dbo].[Delivery] tblD

		inner join inserted tblIns

			on tblD.[deliveryID] = tblIns.[deliveryID]

		inner join [dbo].[Student] tblS

			on tblD.[ReceiverID] = tblS.[studentID]

		cross apply dbo.itvf_Salt(tblS.[studentID]) itvfS

		where ( tblIns.[pended] ='N' )

		and   (

				   (
						   (tblIns.[Address1] is null )
						or (tblIns.[Address1] = '' )
				   )
				or ( tblIns.city = '' )
			    or ( tblIns.postcode='')

		     )

	end -- if update(pended) 

end
go			

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.

Transact SQL – Error – Msg 210 – Conversion failed when converting datetime from binary/varbinary string

Background

Currently, we are heavy into an Encryption project and we are starting to see an error.

Error

Th error states:


Msg 210, Level 16, State 1, Line 62
Conversion failed when converting datetime from binary/varbinary string.

Basis

  1. Unlike some other vendor’s encryption technology, SQL Server’s implementation is highly opaque.  Seemingly, it is be design, as having it so
    • Ensures that metadata is not leaked

Remediation

In a lot of cases to check if a column contains data one will issue isdate. i.e

IsDate  ( Does not work)

Unencrypted Data


select 
           [username]
         , [dateofBirth]
         , [validDate] = isDate([dateofBirth])

from [dbo].[customer]

The above works

Encrypted Data

But, when applied against encrypted data, as in the sample below:


select 
           [username]
         , [dateofBirthEnc]
         , [validDate] = isDate(decryptByKey([dateofBirthEnc]))
         , [validDateAsVarchar] = isDate( cast (decryptByKey([dateofBirthEnc]) as varchar(60) ))
         , [validDateAsSmallDatetime] = isDate( cast(decryptByKey([dateofBirthEnc]) as smalldatetime ))

from [dbo].[customerEncrypted]


One might get errors such as Msg 210 – Conversion failed when converting datetime from binary/varbinary string.

MS SQL Server Prior to v2012

Check Encrypted Data’s Length ( Works)

Code



set nocount on;

declare @dateSDT	  smalldatetime
declare @dateEncyptedSDT  varbinary(200)

declare @dateDT		  datetime
declare @dateEncyptedDT	  varbinary(200)

declare @keyName	  sysname
declare @keyGUID	  uniqueIdentifier

declare @FORMAT_ERRMSG_KEYGUID_ISNULL varchar(60)

declare @LENGTH_OF_ENCRYPTED_SMALLDATETIME smallint
declare @LENGTH_OF_ENCRYPTED_DATETIME smallint

declare @errMessage varchar(500)

set @FORMAT_ERRMSG_KEYGUID_ISNULL = 'Key GUID (%s) is null'
set @LENGTH_OF_ENCRYPTED_SMALLDATETIME = 52
set @LENGTH_OF_ENCRYPTED_DATETIME = 68

set @keyName = 'skEnc'

open symmetric key [skEnc]
decryption by certificate [certEnc]

	set @keyGUID = KEY_GUID(@keyName)

	if (@keyGUID is null)
	begin

		exec master.dbo.xp_sprintf 
				  @errMessage output
				, FORMAT_ERRMSG_KEYGUID_ISNULL
				, @keyName

		print @errMessage

		return

	end

	set @dateSDT = getdate()
	set @dateEncyptedSDT = EncryptByKey
                                  (
                                       @keyGUID
                                     , cast(@dateSDT as varbinary)
                                 )

	select
		  [dateSDT] = @dateSDT
		, [dateEncyptedSDT] = @dateEncyptedSDT
		, [dateEncyptedSDTLength] = len(@dateEncyptedSDT)


	set @dateDT = getdate()
	set @dateEncyptedDT = EncryptByKey
                               (
                                     @keyGUID
                                   , cast(@dateDT as varbinary)
                               )

	select
			  [dateDT] = @dateDT
			, [dateEncyptedDT] = @dateEncyptedDT
			, [dateEncyptedDTLength] = len(@dateEncyptedDT)

	select
		[unecryptedSDT]
		   =
		     case len(@dateEncyptedSDT)

			when null then null

			when @LENGTH_OF_ENCRYPTED_SMALLDATETIME 
			   then cast(decryptByKey(@dateEncyptedSDT) as smalldatetime)

 			when @LENGTH_OF_ENCRYPTED_DATETIME
			   then cast(decryptByKey(@dateEncyptedSDT) as datetime)

			end		
					
		, [unecryptedDT]
		   =
		     case len(@dateEncyptedDT)

			when @LENGTH_OF_ENCRYPTED_SMALLDATETIME 
			   then cast(decryptByKey(@dateEncyptedDT) as smalldatetime)

			when @LENGTH_OF_ENCRYPTED_DATETIME 
			   then cast(decryptByKey(@dateEncyptedDT) as datetime)

		   end		
			
close symmetric key [skEnc]




Output

UsingLengthOfEncyptedData

Quick Explanation

Here is what our test looks like:

  1. We determine the expected length of our encrypted data, depending on your key size, yours will vary
  2. Prior to casting the data we receive from decryptByKey we compared against our expected length and choose our cast’s target accordingly

MS SQL Server v2012 and later

Try_Convert ( Works)

Code



set nocount on;
 
declare @dateSDT      smalldatetime
declare @dateEncyptedSDT  varbinary(200)
 
declare @dateDT       datetime
declare @dateEncyptedDT   varbinary(200)
 
declare @keyName      sysname
declare @keyGUID      uniqueIdentifier
 
declare @FORMAT_ERRMSG_KEYGUID_ISNULL varchar(60)
 
declare @LENGTH_OF_ENCRYPTED_SMALLDATETIME smallint
declare @LENGTH_OF_ENCRYPTED_DATETIME smallint
 
declare @errMessage varchar(500)
 
set @FORMAT_ERRMSG_KEYGUID_ISNULL = 'Key GUID (%s) is null'
set @LENGTH_OF_ENCRYPTED_SMALLDATETIME = 52
set @LENGTH_OF_ENCRYPTED_DATETIME = 68
 
set @keyName = 'skEnc'
 
open symmetric key [skEnc]
decryption by certificate [certEnc]
 
    set @keyGUID = KEY_GUID(@keyName)
 
    if (@keyGUID is null)
    begin
 
        exec master.dbo.xp_sprintf 
                  @errMessage output
                , FORMAT_ERRMSG_KEYGUID_ISNULL
                , @keyName
 
        print @errMessage
 
        return
 
    end
 
    set @dateSDT = getdate()
    set @dateEncyptedSDT = EncryptByKey
                                  (
                                       @keyGUID
                                     , cast(@dateSDT as varbinary)
                                 )
 
  
     set @dateDT = getdate()
     set @dateEncyptedDT = EncryptByKey
                               (
                                     @keyGUID
                                   , cast(@dateDT as varbinary)
                               )
 
		
    select
		   [dateSDT]
			= @dateSDT

		 , [dateEncyptedSDT]
			= @dateEncyptedSDT

         , [unecryptedSDTDataType]
           =
			 case

				when TRY_CONVERT(datetime, decryptByKey(@dateEncyptedSDT)) is not null 
					then 'DateTime'

				when TRY_CONVERT(smalldatetime, decryptByKey(@dateEncyptedSDT)) is not null 
					then 'SmallDateTime'

				else 'Unknown'
 
            end    

        , [unecryptedSDT]
           =
			 case

					when TRY_CONVERT(datetime, decryptByKey(@dateEncyptedSDT)) is not null 
						then TRY_CONVERT(datetime, decryptByKey(@dateEncyptedSDT))

					when TRY_CONVERT(smalldatetime, decryptByKey(@dateEncyptedSDT)) is not null 
						then TRY_CONVERT(smalldatetime, decryptByKey(@dateEncyptedSDT))
 
            end    


    select

		   [dateDT]
			= @dateDT

		 , [dateEncyptedDT]
			= @dateEncyptedDT

         , [unecryptedDTDataType]
           =
			 case

				when TRY_CONVERT(datetime, decryptByKey(@dateEncyptedDT)) is not null 
					then 'DateTime'

				when TRY_CONVERT(smalldatetime, decryptByKey(@dateEncyptedDT)) is not null 
					then 'SmallDateTime'

				else 'Unknown'
 
            end    

        , [unecryptedDT]
           =
			 case

				when TRY_CONVERT(datetime, decryptByKey(@dateEncyptedDT)) is not null 
					then TRY_CONVERT(datetime, decryptByKey(@dateEncyptedDT))

				when TRY_CONVERT(smalldatetime, decryptByKey(@dateEncyptedDT)) is not null 
					then TRY_CONVERT(smalldatetime, decryptByKey(@dateEncyptedDT))
 
            end    
		     
close symmetric key [skEnc]

Output:

TryConvert

Quick Explanation:

  1. We can see that issuing Try/Convert is more typesafe and provides more graceful handling of this error
  2. Here is Microsoft’s explanation
    • TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type.
    • If the cast succeeds,TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned.
    • However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

 

Things that did not work

  1. Using Variant data type
    1. SQL_VARIANT_PROPERTY (Transact-SQL)
      https://technet.microsoft.com/library/ms178550(v=sql.100).aspx

Other Vendors

Sybase

Preserving data types

http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc00467.1270/html/iqencryp/BABDFCHB.htm

Sybase IQ ensures that the original data type of the plaintext is preserved when decrypting data, if the AES_DECRYPT function is given the data type as a parameter or is within a CAST function. IQ compares the target data type of the CAST with the data type of the originally encrypted data. If the two data types do not match, a -1001064 error is returned with details about the original and target data types.

For example, given an encrypted VARCHAR(1) value and the following valid decryption statement:

For example, given an encrypted VARCHAR(1) value and the following valid decryption statement:

SELECT AES_DECRYPT ( thecolumn, ‘theKey’,VARCHAR(1) ) 
FROM   thetable

If you attempt to decrypt the data using the following statement:

SELECT AES_DECRYPT ( thecolumn, ‘theKey’,smallint ) 
FROM   thetable

the decryption error returned is:


Decryption error: Incorrect CAST type smallint(5,0) for decrypt data of type varchar(1,0).

To me Sybase’s implementation is more forgiven and thus Programmer’s time friendly.

Summary

In cases where you are using an authenticator the encrypted data’s length will be a bit larger.  And, so please take that into consideration.

SQL Server 2012 provides a credible option for dealing with this option and so if you are able to target v2012+ exclusively, I will suggest that you use the try_Convert statement.

On the other hand if you have to support legacy SQL Server Versions, you really have to tackle more laboriously.