3

We are trying to add Always encrypted in one of our application and we would like to make it work with our CI flow.

We are using DACPAC deploy with Azure Devops. Everything worked until now but we are facing a challenge with always encrypted...

Each SQL environment needs to have a different key that is in Azure Key Vault. The way we found to automate this is by using a SSDT Variables (ref)

CREATE COLUMN MASTER KEY  [ResponseKey] WITH (
     KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
     KEY_PATH = N'[$(OurVariableName)]' );

When the compare between our DB and the DACPAC is done, the script always contains the Drop + Create of the key even if the value of the key is the same. We tried outputting the generated script and our key value from Azure Key vault is in the variable section of the script. It is as if, the compare doesn't work with variables.

Has anyone made this work?

Also, when we add a new encrypted column, the generated script always fail, stating that there is data already in the table. This seems the opposite of this link

Yves Debigare
  • 113
  • 1
  • 9
  • Based on my known, the value of KEY_PATH is azure key vault key id, why not use that in your statement? (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-column-master-key-transact-sql?view=sql-server-ver15#examples) For your second issue, could you update more about there is data already in the table? Wether the data you want to create already exists there? And did you use the statement like CREATE COLUMN ENCRYPTION KEY key_name WITH VALUES? – Mengdi Liang Jan 07 '20 at 10:17
  • Yes, the value of KEY_PATH is the key vault id. This value change depending of the environment I deploy to. When I deploy to dev, I want my dev Vault to be used. Given that the DACPAC is the same for every environment, I must use variables to inject the right value when deploying. For my second issue, the relevant part from the generated script is this (sorry it's in french...) : – Yves Debigare Jan 07 '20 at 14:30
  • IF EXISTS (SELECT TOP 1 1 FROM [dbo].[FormResponse]) RAISERROR (N'Des lignes ont été détectées dans la table. La mise à jour du schéma pour modifier le chiffrement de colonne s''arrête, car elle nécessite la migration de données vers le client et leur rechargement sur le serveur.', 16, 127) WITH NOWAIT; – Yves Debigare Jan 07 '20 at 14:36
  • CREATE TABLE [dbo].[FormResponse] ( [Response] NVARCHAR (MAX) COLLATE Latin1_General_BIN2 ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = [ReponseColumnKey], ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256', ENCRYPTION_TYPE = DETERMINISTIC ) NULL, ... ) – Yves Debigare Jan 07 '20 at 14:36
  • Just one confusing, I can get what you concern and why you want variable in that statement. Just >>when deploy to dev, you want dev vault to be used, do you mean use different key vault for different stages? Am I right? – Mengdi Liang Jan 07 '20 at 15:40
  • 1
    Exactly... Deploy to Dev => Use dev vault, Deploy to Prod => Use prod vault. This principle would be the same with a CREATE COLUMN ENCRYPTION KEY statement (but it would not be a URL) – Yves Debigare Jan 07 '20 at 15:57
  • Furthermore, variables don't even work with CREATE COLUMN ENCRYPTION KEY see https://social.msdn.microsoft.com/Forums/sqlserver/en-US/19a04156-e156-4a6c-816f-ea4130a2c230/is-there-a-way-to-use-a-variable-to-set-the-encryptedvalue-on-the-column-encryption-key?forum=sqlsecurity – Yves Debigare Jan 09 '20 at 19:33

1 Answers1

2

After a few days of research, it seems it isn't possible to use variables to change the values of keys for deployment of our DACPAC in each of our environments. (Or we have not found how!).

The workaround we are using is to deploy the keys and the encrypted column by script in each of our environments (with diferrent keys in each envionment), then we modify our DACPAC to match what has been done manually.

We have added two options to our XML Publishing profile : 

 <ExcludeColumnEncryptionKeys>True</ExcludeColumnEncryptionKeys>
 <ExcludeColumnMasterKeys>True</ExcludeColumnMasterKeys>

When we deploy in our CI flow, the keys are not compared and no change is done to the structure even if they are different.

It would be nice to be able to use variable instead of doing things manually but that's the way it seems to be!

CI + Always encrypted don't go together!

Yves Debigare
  • 113
  • 1
  • 9
  • I've just started looking into this as well. The current only thought my team has come up with (and have not yet tested) is doing a programmatic file modification on the server before the DACPAC deployment step, and not relying on it to be automatically done by variable – Captain Prinny Sep 16 '20 at 13:36
  • Aw man - I was stuck on this too, glad it wasent me going crazy. – Nicholas Mayne Oct 02 '20 at 10:06