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

[HeminMSFT]

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 in SSDT.

Steps:

1. Create parameterized powershell script for DML on Always Encrypted columns
2. Call that powershell script directly in Pre/Post Deployment script
3. Publish

Sample Powershell script for DML: TestPS.ps1

DB: TestDB

Always Encrypted Columns: fname, lname

$sqlConn = New-Object System.Data.SqlClient.SqlConnection

$sqlConn.ConnectionString = "Server=localhost;Integrated Security=true; Initial Catalog=TestDB; Column Encryption Setting=enabled;"

$sqlConn.Open()

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand

$sqlcmd.Connection = $sqlConn

$sqlcmd.CommandText = "INSERT INTO dbo.t1 ([i],[t],[fname],[lname]) VALUES (@Param1, @Param2, @Param3, @Param4)"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param1",[Data.SQLDBType]::int))) 

$sqlcmd.Parameters["@Param1"].Value = "100"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param2",[Data.SQLDBType]::int)))

$sqlcmd.Parameters["@Param2"].Value = "50"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param3",[Data.SQLDBType]::VarChar,10)))

$sqlcmd.Parameters["@Param3"].Value = "PSTest"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param4",[Data.SQLDBType]::VarChar,10)))

$sqlcmd.Parameters["@Param4"].Value = "PSTest"

$sqlcmd.ExecuteNonQuery();

$sqlConn.Close()

Sample Post Deployment Script to call TestPS.ps1

xp_cmdshell 'powershell -Command "C:\TestPS.ps1"'