Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Decryption and CASTing

In my last encryption post I showed how to encrypt and decrypt data with a symmetric key. However there was a piece of the explanation I left out. If you look at that post, suppose that you ran this query after you’d encrypted the data:

-- decrypt the data
select 
  id
,firstname
,lastname
,title
,Salary = DecryptByKey(EnryptedSalary)
,EnryptedSalary
 from Employees
go

The results wouldn’t be what you’d expect:

decrypt

The binary data is returned, which isn’t rendered correctly. The salary column is the decryption, and the EncryptedSalary is the encrypted data. Note they are different.

This stumped me for awhile when I was playing with encryption and I checked the dercryptbykey page thoroughly before I realized that the return type was varbinary and needed to be CAST.

If I cast this back to nvarchar, I get the data:

select 
  id
, title
, Salary = cast(DecryptByKey(EnryptedSalary) as nvarchar) 
, EnryptedSalary
 from Employees

decrypt2

In my example, I CAST to nvarchar, and then to numeric, mostly for clean coding. This is numeric data. Can I cast directly?

select 
  id
, title
, Salary = cast(DecryptByKey(EnryptedSalary) as numeric(10,4))
, EnryptedSalary
 from Employees
go

No. I get an error.

Msg 8115, Level 16, State 6, Line 1

Arithmetic overflow error converting varbinary to data type numeric.

This isn’t a valid CAST, so I need to double up the CASTs as shown in the original post.


Filed under: Blog Tagged: encryption, syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...