从解密的列更新始终加密的列

Question

我想用始终加密来加密现有的数据库列。我的项目是一个 ASP.NET 项目，首先使用代码，数据库是SQL Server。数据库已有数据。我创建了一个迁移来实现我的目标。

首先，我尝试使用以下方法更改列类型。

ALTER TABLE [dbo].[TestDecrypted] ALTER COLUMN [FloatCol] [float] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL

我收到以下错误。

Operand type clash: float is incompatible with float encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'TestEncrypt')

然后我决定创建另一列并迁移数据。

ALTER TABLE [dbo].[TestDecrypted] ADD [FloatCol2] [float] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
UPDATE [dbo].[TestDecrypted] SET [FloatCol2] = [FloatCol]

我得到了同样的错误。

看完这个后，我注意到可以插入如下数据

DECLARE @floatCol FLOAT = 1.1
UPDATE [dbo].[TestDecrypted] SET [FloatCol2] = @floatCol

但是，如果我尝试从现有列中获取值，则会失败。

DECLARE @floatCol FLOAT = (SELECT TOP 1 FloatCol FROM TestDecrypted)
UPDATE [dbo].[TestDecrypted] SET FloatCol2 = @floatCol

错误如下。

Encryption scheme mismatch for columns/variables '@floatCol'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '4' expects it to be (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'TestEncrypt'). 

有谁知道我怎样才能实现我的目标？

---

更新 1

@Nikhil-Vithlani-Microsoft提出了一些有趣的建议。

• SSMS 中的始终加密向导- 我想通过代码优先迁移来实现我的目标，所以这个想法不适合。
• SqlBulkCopy- 它在迁移中不起作用，因为新列只有在运行所有"Up"方法后才会存在。因此，我们不能在此方法中以这种方式将数据插入到此列中。

无论如何，他的建议驱使我进行了另一次尝试：获取解密的值并用它们更新加密列。

var values = new Dictionary<Guid, double>();
var connectionString = ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString;
using (var sourceConnection = new SqlConnection(connectionString))
{
var myCommand = new SqlCommand("SELECT * FROM dbo.TestDecrypted", sourceConnection);
sourceConnection.Open();
using (var reader = myCommand.ExecuteReader())
{
while (reader.Read())
{
values.Add((Guid)reader["Id"], (double)reader["FloatCol"]);
}
}
}
Sql("ALTER TABLE [dbo].[TestDecrypted] ADD [FloatCol2] [float] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL");
foreach (var valuePair in values)
{
// The error occurs here
Sql($@"DECLARE @value FLOAT = {valuePair.Value}
UPDATE [dbo].[TestDecrypted] SET [FloatCol2] = @value WHERE Id = '{valuePair.Key}'");
}

实际上，我没有尝试创建另一列并迁移数据，如上面的示例中所述。我只在 SSMS 上尝试过。 现在我得到了一个不同的错误。

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

我尝试在不加密新列的情况下执行此操作，并且它工作正常。

知道为什么会发生此错误吗？

Answer 1

您必须在实体框架之外执行始终加密的相关迁移。这个博客应该有帮助 https://blogs.msdn.microsoft.com/sqlsecurity/2015/08/27/using-always-encrypted-with-entity-framework-6/

如果要加密现有列，可以使用 SSMS 中的始终加密向导，或使用介绍如何迁移现有数据的文章。

另请注意，支持通过 C#(.NET 4.6.1+ 客户端)应用进行批量插入。

您可以在 c# 中使用 SqlBulkCopy 执行此操作，特别是使用 SqlBulkCopy.WriteToServer(IDataReader) 方法。

• 创建一个新表 (encryptedTable)，其架构与纯文本表 (unencryptedTable) 的架构相同，但对所需列启用了加密。
• 請從未加密的表中選擇 * 以在 SqlDataReader 中載入資料，然後使用 SqlBulkCopy 使用 SqlBulkCopy.WriteToServer(IDataReader) 方法將資料載入 encryptedTable

例如明文表

CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1), 
[SSN] [char](11) NOT NULL)

加密表

CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1), 
[SSN] [char](11) COLLATE Latin1_General_BIN2 
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, 
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL)

至于为什么你的方法不起作用， 对始终加密使用参数化时，声明语句的右侧 (RHS) 必须是文本。因为驱动程序将识别文字并为您加密。因此，以下内容将不起作用，因为RHS是sql表达式，无法由驱动程序加密

DECLARE @floatCol FLOAT = (SELECT TOP 1 FloatCol FROM TestDecrypted)
UPDATE [dbo].[TestDecrypted] SET FloatCol2 = @floatCol

更新：
以下代码将不起作用，因为始终加密的参数化仅适用于 SSMS

foreach (var valuePair in values)
{
// The error occurs here
Sql($@"DECLARE @value FLOAT = {valuePair.Value}
UPDATE [dbo].[TestDecrypted] SET [FloatCol2] = @value WHERE Id = '{valuePair.Key}'");
}  

但是，如果您按如下方式重写代码，那应该可以工作

foreach (var valuePair in values)
{
SqlCommand cmd = _sqlconn.CreateCommand();
cmd.CommandText = @"UPDATE [dbo].[TestDecrypted] SET [FloatCol2] = @FloatVar WHERE Id = '{valuePair.Key}'");";
SqlParameter paramFloat = cmd.CreateParameter();
paramFloat.ParameterName = @"@FloatVar";
paramFloat.DbType = SqlDbType.Float;
paramFloat.Direction = ParameterDirection.Input;
paramFloat.Value = floatValue;
cmd.Parameters.Add(paramFloat);
cmd.ExecuteNonQuery();

}

希望对您有所帮助，如果您有其他问题，请在评论中留下。