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:

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

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