SQL Server – Encryption – Error – “Explicit conversion from data type varbinary to float is not allowed.”

Background

Getting en error when de-crypting data from float columns.

Scenario

Here is our lab.  Created a certificate, a symmetric key, and a table that will contain an encrypted data.

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]
    ;

Create Table



if schema_id('dataEncrypted') is null
begin

  exec ('create schema [dataEncrypted]')

end
go

if object_id('[dataEncrypted].[accountBalance]') is not null
begin

   drop table [dataEncrypted].[accountBalance]

end
go

if object_id('[dataEncrypted].[accountBalance]') is null
begin


  create table [dataEncrypted].[accountBalance]
  (
      [accountID]	    varchar(30)    not null
    , [balance]		    float          not null
    , [balanceEncrypted]    varbinary(150) null

    , constraint PK_DE_ACCOUNTBALANCE
	primary key
	   (
	     [accountID]
	   )
	)

end
go

Add Data


set nocount on;
go

truncate table [dataEncrypted].[accountBalance]
go

insert into [dataEncrypted].[accountBalance]
([accountID], [balance])
select 'A', 1000.89
UNION ALL
select 'B', 2000.89
UNION ALL
select 'C', 3000.89
UNION ALL
select 'D', 4000.89
UNION ALL
select 'E', 5000.89
go

Encrypt and Decry-pt Data

Code



set nocount on;
set xact_abort on;

declare @keyGUID uniqueIdentifier

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


if not exists
   (
      select *
      from   sys.openkeys tblKey
      where  tblKey.key_name = 'SymmetricKeyPIA'
   )
   begin

	print 'Key not opened!'
	return 

   end

set @keyGUID = KEY_GUID('SymmetricKeyPIA')

if (@keyGUID is null)
begin
	
	print 'keyGUID is null!'

	return

end

print 'keyGUID: ' + cast(@keyGUID as varchar(60))

begin tran

	print 'Updating Data ...'	

	update TOP (1000) tblAB
	set	   
	      [balanceEncrypted]
                  = ENCRYPTBYKEY( 
                                      @keyGUID
                                    , cast([balance] as varbinary)
                                    , 0
                                )     
	from    [dataEncrypted].[accountBalance] tblAB


	print 'Updated Data'	


	print 'Reading Data ...'	

	select top 10 *
		, [BalanceDecrypted] 
                    = cast(
                             DECRYPTBYKEY
                              ( 
                                  [balanceEncrypted]
                                 , 0
                                 , null
                              )  as float)
	from   [dataEncrypted].[accountBalance]

	print 'Read Data'	

rollback tran

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:

Textual:

Msg 529, Level 16, State 2, Line 55
Explicit conversion from data type varbinary to float is not allowed.

 

Image:

ExplicitConversionNotAllowed

 

 

Encrypt and Decry-pt Data

Preface

To get things to work, convert float to decimal.

Please pay attention to precision (Decimal M, N);  the code written here is just for demo.

Code



set nocount on;
set xact_abort on;

declare @keyGUID uniqueIdentifier

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


if not exists
		(
			select *
			from   sys.openkeys tblKey
			where  tblKey.key_name = 'SymmetricKeyPIA'
		)
	begin

		print 'Key not opened!'
		return 

	end

set @keyGUID = KEY_GUID('SymmetricKeyPIA')

if (@keyGUID is null)
begin
	
	print 'keyGUID is null!'

	return

end

print 'keyGUID: ' + cast(@keyGUID as varchar(60))

begin tran

	print 'Updating Data ...'	


	update  tblAB
	set	   
	      [balanceEncrypted]

                  = ENCRYPTBYKEY
                    ( 
                        @keyGUID
                      , cast(
                               cast
                                 (
                                    [balance] as decimal(30,5)
                                 ) as binary
                      )
                      , 0
                    )     

	from    [dataEncrypted].[accountBalance] tblAB


	print 'Updated Data'	


	print 'Reading Data ...'	

	select top 10 *
		, [BalanceDecrypted] 
                    = cast
                        cast(
                             DECRYPTBYKEY
                              ( 
                                  [balanceEncrypted]
                                 , 0
                                 , null
                              )  as decimal(30,5) )
                        as float)
	from   [dataEncrypted].[accountBalance]


	print 'Read Data'	

rollback tran

CLOSE SYMMETRIC KEY [SymmetricKeyPIA]

Output:

convertedToDecimal

 

SQL Server Version

Here is the version of SQL Server that we tested on:

Code


select @@version

Output:

Textual:


Microsoft SQL Server 2014 - 12.0.4213.0 (X64) 
	Jun  9 2015 12:06:16 
	Copyright (c) Microsoft Corporation
	Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Image

SQLServerVersion

Sample Code

Sample code published to Github @ https://github.com/DanielAdeniji/ExplicitConversionFromDatatypevarbinarytofloatisnotallowed

Microsoft Connect Item

It appears that there are no current Microsoft Connect Items Opened to track this, and so did so:

  1. ID 1902555

 

References

  1. SQL Server > SQL Server Security > decryptbykey multiple session issue
    https://social.msdn.microsoft.com/Forums/en-US/2da7e845-abec-4abf-be9d-f1be1c5d8b9b/decryptbykey-multiple-session-issue?forum=sqlsecurity
  2. Home » SQL Server 2005 » SQL Server 2005 Security » Problem with data conversion after…
    http://www.sqlservercentral.com/Forums/Topic709350-359-1.aspx

 

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