Unable To Insert Into An Always Encrypted Table Using Powershell

I'm not able to insert into an always encrypted table using PowerShell, the code I'm using is: $serverName = 'ServerName' $databaseName = 'SecureDB' $connStr = 'Server = ' + $serve

Solution 1:

Since Invoke-sqlcmd doesn't support insert statement against encrypted columns as mentioned here, the solution I found is to insert using SqlCommand.ExecuteNonQuery Method () https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

$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, @)"
$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()