How does the client know which columns to encrypt with SQL Server Always encrypted?

Question

I am wondering how a SQL client knows which columns to encrypt before sending data to the SQL Server? After all, the encrypted setting is managed in SQL Server.

There is no trace of these settings in de EF meta data. Also, I found out that the encryption setting of a column can be changed, without the need to deploy an updated version of the application.

Context:

• Microsoft documentation on Always Encrypted
• SQL Server 2017
• EF Core 3.1 client

Answer 1

SQL Server Profiler reveals the mechanism that the client uses to infer which columns are encrypted.

In this scenario I update the active flag for user 42.

First, the client sends this query:

exec sp_describe_parameter_encryption N'SET NOCOUNT ON;
UPDATE [Users] SET [Active] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;

',N'@p1 int,@p0 bit'

This returns two result sets with meta data on the encryption:

The first has these columns:

• column_encryption_key_ordinal
• database_id
• column_encryption_key_id
• column_encryption_key_version
• column_encryption_key_metadata_version
• column_encryption_key_encrypted_value
• column_master_key_store_provider_name
• column_master_key_path
• column_encryption_key_encryption_algorithm_name

The second has these columns:

• parameter_ordinal
• parameter_name
• column_encryption_algorithm
• column_encryption_type
• column_encryption_key_ordinal
• column_encryption_normalization_rule_version

It is followed by the actual update query:

exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Users] SET [Active] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;

',N'@p1 int,@p0 bit',@p1=42,@p0=1

In my case, there is no actual encryption on this column, so the results of sp_describe_parameter_encryption are not very interesting, but the mechanism is clear.

SELECT queries are not preceded by such a meta query.