Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted? - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted?

sql-server sql-server-2016 add tag

RoastBeast (imported from SE)

In SSMS we are attempting to bulk insert from a csv file into a table that has a column encrypted using SQL Server 2016's Always Encrypted feature.

This is the command we're using:

    INSERT INTO membersE
    SELECT *
    FROM OPENROWSET(
    	BULK 'c:\members.csv', 
    	FORMATFILE = 'c:\membersEFormat.xml',
    	FIRSTROW = 2
    	) m

This returns the typical error you get when attempting to insert into an encrypted column:

>Msg 206, Level 16, State 2, Line 6  
Operand type clash: varbinary is incompatible with varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_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'

We understand that you can't insert into an encrypted column via SSMS and that you need to use a .NET 4.6.1+ client, but we'd like to know if bulk insert operations are not possible as well?

Top Answer

Nikhil Vithlani Microsoft (imported from SE)

Bulk Insert operations in the manner you are describing are not supported for encrypted column via SSMS.

Please refer to [Encrypting Existing Data with Always Encrypted](https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/) by Jakub Szymaszek to migrate your existing data to Always Encrypted

Also, please note that doing bulk inserts through a C# (.NET 4.6.1+ client) app is supported.

You can do this in C# using `SqlBulkCopy` specifically using `SqlBulkCopy.WriteToServer(IDataReader)` Method. I am assuming you are trying to load data from csv file to a table with encrypted column (say encryptedTable). I would do the following:

 - Create a new table (say unencryptedTable, for security purposes, you might consider creating this table in a local sql server instance) with the same schema without any column encryption.
 - Load the csv data into unencryptedTable, using the method that you described in the question.
 - Do `select * from unencryptedTable` to load the data in a SqlDataReader then use SqlBulkCopy to load it to the encryptedTable using `SqlBulkCopy.WriteToServer(IDataReader)` Method.

1 Answer