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.

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