How to perform "Order by" on an encrypted column (deterministic encryption - SQL Server 2016) ?
I am getting error when executed on SSMS 2017 (with required settings for AE)
SELECT *
FROM [dbo].[X]
ORDER BY lastName
The lastName column is defined like this:
[lastName] [varchar](60) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [X]
I get an error:
Msg 33299, Level 16, State 2, Line 9
Encryption scheme mismatch for columns/variables 'lastName'. 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 = 'X', column_encryption_key_database_name = 'X') and the expression near line '3' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).
Order by is not supported on encrypted columns.
More details can be found on this article
The Database Engine never operates on plaintext data stored in encrypted columns, but it still supports some queries on encrypted data, depending on the encryption type for the column. Always Encrypted supports two types of encryption: randomized encryption and deterministic encryption.
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. Use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records and are not used to join tables. For details on Always Encrypted cryptographic algorithms, see Always Encrypted Cryptography.