No SSMS, estamos tentando inserir em massa de um arquivo csv em uma tabela que possui uma coluna criptografada usando o recurso Always Encrypted do SQL Server 2016.
Este é o comando que estamos usando:
INSERT INTO membersE
SELECT *
FROM OPENROWSET(
BULK 'c:\members.csv',
FORMATFILE = 'c:\membersEFormat.xml',
FIRSTROW = 2
) m
Isso retorna o erro típico que você obtém ao tentar inserir em uma coluna criptografada:
Msg 206, Level 16, State 2, Line 6 Conflito de
tipo de operando: varbinary é incompatível com varchar(50) criptografado com (encryption_type = 'DETERMINISTIC',cryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'DATABASE') collation_name = 'Latin1_General_BIN2'
Entendemos que você não pode inserir em uma coluna criptografada via SSMS e que precisa usar um cliente .NET 4.6.1+, mas gostaríamos de saber se as operações de inserção em massa também não são possíveis?
CÓDIGO DE AMOSTRA QUE FUNCIONOU PARA MIM
(para satisfazer a solicitação do Windows10)
SqlCommand cmd;
SqlConnection conn;
SqlBulkCopy copy;
SqlDataAdapter da;
DataTable dt;
using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString))
{
conn.Open();
using (cmd = new SqlCommand("SELECT * FROM members", conn))
using (copy = new SqlBulkCopy(conn))
using (da = new SqlDataAdapter(cmd))
using (dt = new DataTable())
{
cmd.CommandTimeout = 600;
da.Fill(dt);
cmd.CommandText = "TRUNCATE TABLE membersE";
cmd.ExecuteNonQuery();
copy.DestinationTableName = "membersE";
copy.WriteToServer(dt);
}
}
As operações de inserção em massa da maneira que você está descrevendo não têm suporte para colunas criptografadas via SSMS.
Consulte este artigo ( https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/ ) para migrar seus dados existentes para o Always Encrypted
Além disso, observe que há suporte para fazer inserções em massa por meio de um aplicativo C# (cliente .NET 4.6.1+).
Você pode fazer isso em c# usando SqlBulkCopy especificamente usando
SqlBulkCopy.WriteToServer(IDataReader)
Method. Estou assumindo que você está tentando carregar dados do arquivo csv para uma tabela com coluna criptografada (digamos,cryptedTable). eu faria o seguinteselect * from unencryptedTable
para carregar os dados em um SqlDataReader, em seguida, use SqlBulkCopy para carregá-los na tabela criptografada usando oSqlBulkCopy.WriteToServer(IDataReader)
métodoSe você tiver dúvidas adicionais sobre isso, poste perguntas na seção de comentários e tentarei o meu melhor para resolvê-las :)
Um método mais simples que funcionou para nós foi:
INSERT INTO ... SELECT FROM ...
consulta para inserir da nova tabela na tabela existente.