column level encryption

Question

Dear colleagues,

my client wants to encrypt specific columns in a table with different keys (to segregate more than the default encryption at rest).

I see this solution:

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data

BUT, I understand that it has the following drawbacks:

1. It is not transparent: the user must explicitly choose the key, encrypt/decrypt the column.
2. It is hardly portable to other clouds/DBs,
3. Efficient select/filter/search on the encrypted attribute is compromised because of impossibility of indexing on them.

(Please correct me if any of the above points is wrong.)

Do you know of any (Azure or partner) solution that implements encryption with different key for different columns and is as efficient (and possibly as transparent) as the default databases encryption at rest?

Answer 1

Hope this links also works for you

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves-configure-encryption-tsql?view=sql-server-ver16

To answer your question, Azure SQL Database provides a feature called "Always Encrypted with secure enclaves" which allows the creation and use of indexes on columns that are encrypted using deterministic or randomized encryption with enclave-enabled keys. These indexes are sorted based on ciphertext for deterministic encryption, and based on plaintext values for randomized encryption. The key values in the index data structure (B-tree) are encrypted and sorted based on their plaintext values​.

There are two methods for invoking indexing operations with column encryption keys:

1. Provided directly by the client: The application issuing a query that triggers an operation on an index must connect to the database with both Always Encrypted and enclave computations enabled for the database connection. The application must also have access to the column master key protecting the column encryption key for the indexed column. Once SQL Server Engine parses the application query and determines it will need to update an index on an encrypted column to execute the query, it instructs the client driver to release the required column encryption key to the enclave over a secure channel.
2. Using cached column encryption keys: Once a client application sends a column encryption key to the enclave for processing any query that requires enclave computations, the enclave caches the column encryption key in an internal cache. If the same or another client application used by the same or a different user triggers an operation on an index without providing the required column encryption directly, the enclave will look up the column encryption key in the cache.

As for encrypting different columns with different keys, the documentation on Always Encrypted with secure enclaves does not explicitly mention this. However, the logic of the feature suggests it should be possible, given that different column encryption keys can be used for different columns and these keys can be provided by the client or retrieved from the enclave's internal cache.