This article is contributed. See the original author and article here.

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″‘


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

%d bloggers like this: