SSDT DacFx Pre/Post Deployment scripts containing DML for Always Encrypted columns


Unlike SSMS 17.xx onwards; SSDT/DACFx currently doesn’t have « Parameterize for Always Encrypted » setting.

This causes DACPAC/DACFx deployments to fail when Pre/Post deployment scripts have DML (INSERT, UPDATE) on Always Encrypted columns.

We know Always Encrypted is driver based encryption technology, and for DML to work on Always Encrypted columns, query needs to be parameterized (SqlClient.SqlParameter).

SSMS has an option to « Parameterize for Always Encrypted », which behind the scenes look for TSQL Variables and convert them to SqlClient.SqlParameter.

 

One way to achieve this with SSDT/DACFx deployments is to use parameterized Powershell script and calling PS1 directly in Pre/Post Deployment Script