Invoke-SQLCMD Cannot insert or delete from a table that is using Always Encrypted

[codwow]

Whenever trying to do any command besides select on a table that has Always encrypted enabled Invoke-SQLCMD throws an error. I'm able to do insert and deletion through SSMS and verified the query would normally work. I had to use a workaround from 2018 to get around this issue

Work Around $sqlConn = New-Object System.Data.SqlClient.SqlConnection $sqlConn.ConnectionString = "Server=ServerName;Integrated Security=true; Initial Catalog=SecurePasswords; Column Encryption Setting=enabled;" $sqlConn.Open() $sqlcmd = New-Object System.Data.SqlClient.SqlCommand $sqlcmd.Connection = $sqlConn $sqlcmd.CommandText = "INSERT INTO dbo.SecureTable (Column1, Column2, Column3, Column4) VALUES (@Param1, @Param2, @Param3, @Param4)" $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param1",[Data.SQLDBType]::VarChar,50))) $sqlcmd.Parameters["@Param1"].Value = "$Param1" $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param2",[Data.SQLDBType]::VarChar,500))) $sqlcmd.Parameters["@Param2"].Value = "$Param2" $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param3",[Data.SQLDBType]::VarChar,50))) $sqlcmd.Parameters["@Param3"].Value = "$Param3" $sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Param4",[Data.SQLDBType]::VarChar,100))) $sqlcmd.Parameters["@Param4"].Value = "$Param4" $sqlcmd.ExecuteNonQuery(); $sqlConn.Close()

Link: https://stackoverflow.com/questions/48483631/unable-to-insert-into-an-always-encrypted-table-using-powershell

[Matteo-T]

Hi @Pietervanhove - could you take a look or assigned to somebody on your Team to see what might be going on here? It seems something about the interaction of Invoke-Sqlcmd and AE... Thanks!

[Matteo-T]

Hey @DBarmanMS - any update on this one? Maybe try it with the latest version of the module...