4

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?

SAMPLE CODE THAT WORKED FOR ME
(to satisfy Windows10's request)

        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);                    
            }
        }
2
  • i tried above using the datatable but i got certificate error. can you put some code like how you handle with datatable/reader?
    – Windows10
    May 25 '17 at 18:13
  • 1
    @RoastBeast - be aware that using a data table instead of a data reader requires all of your data to be read into memory at once but the data reader streams it.
    – xr280xr
    Apr 24 at 6:22
2

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

Please refer to this (https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/) article 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

If you have additional questions regarding this, please post questions in the comment section and I will try my best to address them :)

1
  • @Vithlani - your suggestion of using an unencrypted table and then employing SQLBulkCopy to populate the encrypted table worked. I had some issues using a data reader so I substituted with a data table object.Thanks!
    – RoastBeast
    Jan 17 '17 at 17:53
0

A simpler method that worked for us was to:

  1. Import the CSV using either the method you mentioned or through an SSMS Task, but to a new table.
  2. Encrypt the column on the new table so the column data types match between the new and existing tables.
  3. Perform an INSERT INTO ... SELECT FROM ... query to insert from the new table to the existing table.
  4. Drop the new table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.