2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
se7vanj
  • 160
  • 2
  • 8
  • All that the server can see is the encrypted value - even if it sorts *those* values, there's no guarantee that when those values are later decrypted that the same sort order would make sense. E.g if `A` is encrypted as `23`, `B` is encrypted as `17` and `C` is encrypted as `49`, do you really want the server performing that sort (so the values you eventually read come out in the order `B`, `A`, `C`)? – Damien_The_Unbeliever Aug 18 '17 at 14:25
  • ["Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (e.g. greater/less than, pattern matching using the LIKE operator, or arithmetical operations)."](https://learn.microsoft.com/sql/relational-databases/security/encryption/always-encrypted-database-engine). This makes sense if you think about how the database could possibly implement an ordering on encrypted items -- securely, that is. – Jeroen Mostert Aug 18 '17 at 14:28
  • Depending on what you need to `ORDER BY` for, there may be a workaround, the most obvious being performing the sorting on the client end after decryption. (SSMS doesn't have processing logic of its own, so it can't do nice things like that.) – Jeroen Mostert Aug 18 '17 at 14:37

1 Answers1

1

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.

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
  • Thought so, in a common scenario where the host WebAPI App talks to SQL 2016 DB and consumer website calls host API, what would be the suggestion here? We don't want to over kill either the API or the consumer to get all the records and then do it by their own? And the main problem is, when consumer asks for Paginated data in the request to API, it's not easy for the API to handle and return back in a paginated fashion and that makes to deal the unsecured data in-memory! Apparently using a CTE also would not help on this case, and no workaround within SQL! – se7vanj Aug 18 '17 at 16:39
  • @se7vanj: 1) a database engine that can sort and paginate, 2) a database engine that can't reveal unencrypted data even if untrusted parties have access -- pick one. If Always Encrypted is taking things too far, consider lesser alternatives like [Transparent Data Encryption](https://learn.microsoft.com/sql/relational-databases/security/encryption/transparent-data-encryption-tde) to protect the data at rest, or host the server on premise, or ensure it's secure in other ways (encrypting VM images, access control, etc.) – Jeroen Mostert Aug 18 '17 at 17:02
  • 1
    @JeroenMostert - I believe that there are some encryption schemes that do support being able to sort (search: `order preserving encryption`) but I don't believe that such schemes have become mainstream and I'm certain none exist in SQL Server. But the idea of being able to sort and paginate *and* keep data secure isn't 100% sci-fi. – Damien_The_Unbeliever Aug 21 '17 at 06:16
  • @Damien_The_Unbeliever: thanks for the info. [A question with background info on crypto.se](https://crypto.stackexchange.com/q/37375). Mainstream adoption is hampered by unknowns about how much information these schemes leak in practical applications. (I'm sure it would also be a challenge to implement it efficiently, certainly if you wanted to paginate rather than merely compare individual elements.) – Jeroen Mostert Aug 21 '17 at 07:08