Always Encrypted - Data Types

Question

Always Encrypted - Data Types

DBANewbie

Hall of Fame

Points: 3095
More actions
January 8, 2020 at 9:05 am

#3713527

I am starting to take an in-depth look at always encrypted and have been working through some examples.
I have a column which is an integer and i have selected that column for always encrypted encryption.
When i query this column, the encrypted data is returned (not an integer) but the data type of the column remains INT. Why is this? I expected that it would have been converted to a varbinary.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

That's just the internal implementation of AE. The idea here is to not have to change your app, which might be expecting an int. I believe the metadata knows this is an int, and the ADO/OLEDB driver just converts things after decryption.

DBANewbie Hall of Fame Points: 3095 More actions · Answer 2

Hi.

Thanks so much for the reply, I’ve been going out of my mind tying to find an answer to this and there is very little information out there.

I understand the concept, I just don’t understand why the column remains as an INT but if I select the data out of the column a bytestream made up of lots of alphanumeric characters is returned. So this encrypted data is stored in the database as an alphanumeric stream of characters in a column with a data type of INT. How is this possible?

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

The underlying storage is binary. If you don't have the certificate on your machine, the result set will contain binary data inside of the that result set for those encrypted columns. This is by design, and your application will need to handle this case. However, binary-> int is an implicit converion (https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15)

The INT is metadata. You're assuming that sys.columns, and reading the metadata of the result set means that the column is stored this way. That's an incorrect conflation. The data will be an int when decrypted, which is why the meta data isnt' changed. The application, if authorized with a cert on the client, will get the stream of binary data, decrpyt it, and return an int, which the client is expecting.

How this specifically works? No idea, and don't care. Microsoft has made a contract with the SQL Server drivers to do it this way and they handle it. You don't need to know how it works.

DBANewbie Hall of Fame Points: 3095 More actions · Answer 4

Thanks again for your reply Steve.

I would assume that if I modified a database data file and hard coded a string value into a regular integer column (one without encryption) I’d get an error when that data was queried.

I was curious why that isn’t the case with integer columns / always encrypted.

it sounds like the database engine will handle the fact that the data which is stored does not match the column data type in this scenario. I just haven’t found anywhere that this is referenced online or in the official certification exam textbooks.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

You can't store binary data in an int column. If you hacked the db, you'd have 4 bytes, but whatever you put in there would be interpreted as a number.

AE isn't well documented. I'm not sure if I've heard this from MS or in some public talks, but essentially they are storing binary data, but preserving the metadata. Might be able to find some details in old talks from Ignite or TechEd in 2015 or early 2016.