Add an Always Encrypted Column using SqlSchemaCompare.scmp with Azure Key Vault

39 views Asked by At

I have a sql project which is used for local development and also the dacpac it generates will be used in the CI/CD pipelines. The current plan is to setup a new Encrypted password column and run both simultenously for a while till we can phase out the old Password column as we need to mantain 100% uptime, simply changing the existing password column to be encrypted will break all current live microservices depending on the database.

Now I can run this statement in SQL Server management studio but it will not work when I run SqlSchemaCompare.scmp

CREATE TABLE [Accounts].[Accounts]
(
    [AccountId] INT IDENTITY(1, 1) NOT NULL,
    [Password] NVARCHAR(256) NULL,
    [EncryptedPassword] NVARCHAR(256) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [MyCEK], ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256',
                        ENCRYPTION_TYPE = DETERMINISTIC
                       ) NULL
);

GO

I get this error

Value cannot be null. Parameter name: reportedElement

Now I can find a lot of old questions from 2020 saying that this isn't possible yet to setup Always Encrypted columns using a dacpac and CI/CD but this article from Microsoft seems to indicate that it is possible to update a database using a DacPac and if that eliminates the manual steps then should be good for CI/CD to make the update to the production database.

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-dacpac?view=sql-server-ver15

My CEK

CREATE COLUMN ENCRYPTION KEY [MyCEK]
WITH VALUES
(
     COLUMN_MASTER_KEY = [MyCMK],
     ALGORITHM = N'RSA_OAEP',
     ENCRYPTED_VALUE = ...
);
GO

MY CMK

CREATE COLUMN MASTER KEY [MyCMK]
WITH (
     KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
     KEY_PATH = N'https://myorg.vault.azure.net/keys/masterkey/...
);
GO

Things I have not tried yet is setting up a secure enclave to see if that makes any difference but at this moment in time I'm pretty lost in the dark on how to proceed past this error.

I'm expecting that is should be able to work as a currently do with using the SqlSchemaCompare.scmp to allow the team to maintain their own local developer environments as well as allowing the pipelines to continue to build dacpacs and apply them and run post deployment scripts etc.

One workaround I had tried was to alter the table in the post deployment script to add the updated column, which did work. But then the issue is that on every deployment of the dacpac it would recognise that this column isn't in the create SQL statement so it would remove the column before recreating it again in the post deployment script.

0

There are 0 answers