Not able to apply max() for Encrypted Column on SQL Server

Question

I have a table with a datetime column that was encrypted using Always Encrypted feature on SQL Server 2016.

Now I am trying to perform this simple select:

select max(dt_order) 
from orders 
where customer = 123;

I am running into this error:

Msg 33299, Level 16, State 2, Line 5
Encryption scheme mismatch for columns/variables 'dt_order'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'myColHML', column_encryption_key_database_name = 'TESTING') and the expression near line '1' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).

In SSMS I already have set connect options "Column Encryption Setting=Enabled" and also I've set query options "Enable Parametrization for Always Encrypted"

Any idea?

Answer 1

Since your data is encrypted using a key on the client side, SQL Server will not be able to calculate max. This is due to the fact that SQL Server does not have the key. The main value proposition of Always Encrypted is that it protects the data from Admins of SQL Server. Currently, the only operation possible on encrypted columns is equality.

From official documentation

Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.