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:
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.