无法使用PowerShell插入始终加密的表中 [英] Unable to insert into an always encrypted table using PowerShell

问题描述

我无法使用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 = " + $serverName + "; Database = " + $databaseName + "; 
Integrated Security = True; Column Encryption Setting = Enabled"
$connection = New-Object 
Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]
$query = @"
DECLARE @Param1 VARCHAR(50) = 'Param1Value'
DECLARE @Param2 VARCHAR(500) = 'Param2Value'
DECLARE @Param3 VARCHAR(50) = 'Param3Value'
DECLARE @Param4 VARCHAR(100) = 'Param4Value'

Insert into [dbo].[SecureTable]
values (@Param1,@Param2,@Param3,@Param4)
GO
"@
Invoke-Sqlcmd -Query $query -ConnectionString $connStr

请注意，只有Param2被加密，并且我在SSMS上成功使用了相同的查询.

Note that only Param2 is encrypted and I'm using the same query on SSMS successfully.

错误消息:

Invoke-Sqlcmd:列/变量'@ Param2'的加密方案不匹配.列/变量的加密方案为(encryption_type ='PLAINTEXT')，第'6'行附近的表达式希望它为(encryption_type ='DETERMINISTIC'，encoding_algorithm_name ='AEAD_AES_256_CBC_HMAC_SHA_256'，column_encryption_key_name ='CEK1'，column_encryption_key_database_name ='SecureDB')(或更弱).

Invoke-Sqlcmd : Encryption scheme mismatch for columns/variables '@Param2'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '6' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'SecureDB') (or weaker).

推荐答案

由于Invoke-sqlcmd不支持针对加密列的插入语句，如所述 https://msdn.microsoft.com/zh-CN/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

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()

这篇关于无法使用PowerShell插入始终加密的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！