'How to propagate Always encrypted column encryption to Test, Acceptance and Production?

We are using Always encrypted in a .Net core application.

The Sql Server database is maintained with EF-core migrations.

I was wondering how to propagate the column encryptions for the selected columns over to Test, Acceptance and Production. I didn't find any information on this.

It would be nice if this were taken care of in migrations, so automatic deployment will include newly added columns to be encrypted immediately. As I understand it, encrypting existing data is not straighforward. So, I would rather not have time elapse between running the migration and enabling encryption on the columns, at least not up-time on the application.

Can key names be reused, with different keys on each client and it that safe? If so, I think that using migrationBuilder.Sql() might help me here.

So far, however, I did not succeed.

Maybe, this should not be done this way at all.

CREATE TABLE [dbo].Encrypt(
    [id] [int],
    [sensitive] [nvarchar](max)
) 

followed by

Alter table Encrypt alter column [sensitive] [nvarchar](max) 
COLLATE Latin1_General_BIN2 
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [CEK_Auto1], 
    ENCRYPTION_TYPE = Deterministic, 
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
)

gives an error:

Cannot alter column 'sensitive'. The statement attempts to encrypt, decrypt or re-encrypt the column in-place using a secure enclave, but the current and/or the target column encryption key for the column is not enclave-enabled. column and one or more of the following column properties: collation (to a different code page), data type. Such changes cannot be combined in a single statement. Try using multiple statements.

when run from SQL Server Management Studio.

What is the way to go here?

If it amounts to down-time between running a migration and running a SQL-script, so be it.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source