Sqlclient: SqlBulkCopy throws an InvalidOperationException when working with Always Encrypted columns

[chadjefferies]

Describe the bug

SqlBulkCopy throws an InvalidOperationException when working with Always Encrypted columns

System.InvalidOperationException
  HResult=0x80131509
  Message=The given value of type String from the data source cannot be converted to type varbinary of the specified target column.
  Source=Microsoft.Data.SqlClient
  StackTrace:
   at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
   at Microsoft.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)

Inner Exception 1:
InvalidCastException: Failed to convert parameter value from a String to a Byte[].

Inner Exception 2:
InvalidCastException: Invalid cast from 'System.String' to 'System.Byte[]'.

To reproduce

I followed this guide to create a Clinic database with a Patients table that used my Windows certificate store for the keys. The resulting Create Table syntax is below. You can see it creates a SSN column using Deterministic encryption and a BirthDate column using Randomized encryption:

CREATE TABLE [dbo].[Patients](
    [PatientId] [int] IDENTITY(1,1) NOT NULL,
    [SSN] [char](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [MiddleName] [nvarchar](50) NULL,
    [StreetAddress] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [ZipCode] [char](5) NULL,
    [State] [char](2) NULL,
    [BirthDate] [date] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [PatientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I wrote the following code to perform a bulk insert of data using the SqlBulkCopy into the Patients table
```c#
var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn
{
ColumnName = "PatientId",
DataType = typeof(int),
AutoIncrement = true,
});
dataTable.Columns.Add("SSN", typeof(string));
dataTable.Columns.Add("FirstName", typeof(string));
dataTable.Columns.Add("LastName", typeof(string));
dataTable.Columns.Add("MiddleName", typeof(string));
dataTable.Columns.Add("StreetAddress", typeof(string));
dataTable.Columns.Add("City", typeof(string));
dataTable.Columns.Add("ZipCode", typeof(string));
dataTable.Columns.Add("State", typeof(string));
dataTable.Columns.Add("BirthDate", typeof(DateTime));

        for (int i = 0; i < 500; i++)
        {
            var dataRow = dataTable.NewRow();

            dataRow["SSN"] = "000-00-0000";
            dataRow["FirstName"] = "Joan";
            dataRow["MiddleName"] = "of";
            dataRow["LastName"] = "Arc";
            dataRow["City"] = "Domremy";
            dataRow["BirthDate"] = DateTime.Parse("05/30/1412");
            dataTable.Rows.Add(dataRow);
        }

        dataTable.AcceptChanges();

        using (var connection = new SqlConnection("Server=serverName;Initial Catalog=Clinic;Integrated Security=true;Column Encryption Setting=Enabled;"))
        {
            using (var bulkCopy = new SqlBulkCopy(connection)
            {
                EnableStreaming = true,
                BatchSize = 500,
                DestinationTableName = "Patients"
            })
            {
                connection.Open();
                bulkCopy.WriteToServer(dataTable);
            }
        }

### Expected behavior
The code above works as expected when I copy it into a net472 project using the System.Data.SqlClient reference. But fails when run under netcore2.2 using the Microsoft.Data.SqlClient. Below are the csproj files from both projects:

```xml
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.2</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="1.0.19128.1-Preview" />
  </ItemGroup>

</Project>

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net472</TargetFramework>
  </PropertyGroup>

</Project>

Further technical details

Microsoft.Data.SqlClient version: 1.0.19128.1-Preview
.NET target: Core 2.2
SQL Server version: SQL Server 2017 Developer 14.0.3103.1/Windows Server 2016 Datacenter (10.0)
Operating system: Windows 10 1809 17763.316

[AfsanehR]

@chadjefferies Thanks for opening this issue and providing complete steps. I am able to repro this using Microsoft.Data.SqlClient.dll targeting netcoreapp. Will investigate more and get back to you.

[peterbae]

Hi @chadjefferies, the fix has been merged and it will be available in the next release of the driver.