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
,Salary = DecryptByKey(EnryptedSalary)
 from Employees

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:

, 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?

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

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

3 Responses to Decryption and CASTing

  1. Kevin Boisits says:

    The reason you can not cast directly to numeric is because you casted to varchar when you encrypted the data.

    If when you encrypted it you casted to varbinary: ENCRYPTBYKEY(key_guid(‘MySalaryProtector’),CAST(salary AS VARBINARY))

    You could then use a single cast to decrypt it: cast(DecryptByKey(EnryptedSalary) as numeric(10,4))

    This seems to work well for encrypting/decrypting numbers and dates.

    Hope that helps,

  2. Ken says:

    Still requires 2 CAST statements…

Comments are closed.