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.