NullReferenceException when using SqlDataAdapter with Always Encrypted and UpdateBatchSize > 1

[ColoradoOrion]

Describe the bug

A Null Reference Exception is thrown when all of the following are true:

• The project uses Microsoft.Data.SqlClient
• The Connection String enables Always Encrypted
• The SQL operations use a SqlDataAdapter
• The UpdateBatchSize is set to a value greater than one
• A multi-row update is sent (perhaps not a requirement for replication)

The exception gets thrown regardless of whether the command involves encrypted columns or not. In this example, it doesn't appear that the operation gets as far as hitting the database at all.

Exception message:

System.NullReferenceException
  HResult=0x80004003
  Message=Object reference not set to an instance of an object.
  Source=Microsoft.Data.SqlClient
  StackTrace:
   at Microsoft.Data.SqlClient.SqlCommand.TryFetchInputParameterEncryptionInfo(Int32 timeout, Boolean isAsync, Boolean asyncWrite, Boolean& inputParameterEncryptionNeeded, Task& task, ReadOnlyDictionary`2& describeParameterEncryptionRpcOriginalRpcMap, Boolean isRetry)

Stack trace:

Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.TryFetchInputParameterEncryptionInfo(int timeout, bool isAsync, bool asyncWrite, out bool inputParameterEncryptionNeeded, out System.Threading.Tasks.Task task, out System.Collections.ObjectModel.ReadOnlyDictionary<Microsoft.Data.SqlClient._SqlRPC, Microsoft.Data.SqlClient._SqlRPC> describeParameterEncryptionRpcOriginalRpcMap, bool isRetry)
Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.PrepareForTransparentEncryption(System.Data.CommandBehavior cmdBehavior, bool returnStream, bool isAsync, int timeout, System.Threading.Tasks.TaskCompletionSource<object> completion, out System.Threading.Tasks.Task returnTask, bool asyncWrite, out bool usedCache, bool inRetry)
Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior cmdBehavior, Microsoft.Data.SqlClient.RunBehavior runBehavior, bool returnStream, System.Threading.Tasks.TaskCompletionSource<object> completion, int timeout, out System.Threading.Tasks.Task task, out bool usedCache, bool asyncWrite, bool inRetry, string method)
Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(System.Threading.Tasks.TaskCompletionSource<object> completion, bool sendToPipe, int timeout, out bool usedCache, bool asyncWrite, bool inRetry, string methodName)
Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlCommandSet.ExecuteNonQuery()
Microsoft.Data.SqlClient.dll!Microsoft.Data.SqlClient.SqlDataAdapter.ExecuteBatch()
System.Data.Common.dll!System.Data.Common.DbDataAdapter.UpdateBatchExecute(System.Data.Common.DbDataAdapter.BatchCommandInfo[] batchCommands, int commandCount, System.Data.Common.RowUpdatedEventArgs rowUpdatedEvent)
System.Data.Common.dll!System.Data.Common.DbDataAdapter.Update(System.Data.DataRow[] dataRows, System.Data.Common.DataTableMapping tableMapping)
System.Data.Common.dll!System.Data.Common.DbDataAdapter.UpdateFromDataTable(System.Data.DataTable dataTable, System.Data.Common.DataTableMapping tableMapping)
System.Data.Common.dll!System.Data.Common.DbDataAdapter.Update(System.Data.DataTable dataTable)
AlwaysEncryptedBatchSizeBug.dll!Program.Update.AnonymousMethod__0() Line 64
	at C:\Users\AndyHarrington\source\repos\AlwaysEncryptBatchSizeBug\Program.cs(64)
System.Private.CoreLib.dll!System.Threading.Tasks.Task<int>.InnerInvoke()
System.Private.CoreLib.dll!System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(System.Threading.Thread threadPoolThread, System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state)
System.Private.CoreLib.dll!System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref System.Threading.Tasks.Task currentTaskSlot, System.Threading.Thread threadPoolThread)
System.Private.CoreLib.dll!System.Threading.ThreadPoolWorkQueue.Dispatch()
System.Private.CoreLib.dll!System.Threading.PortableThreadPool.WorkerThread.WorkerThreadStart()

To reproduce

1. Create a .Net 8.0 project
2. Include the Program.cs source file below
3. Include the latest Microsoft.Data.SqlClient
4. Include System.Data.SqlClient (which is commented out atm)
5. Adjust the connectionString to one appropriate for your environment
6. Optionally create the DB objects from the SQL script below
7. Run the program

• You'll see the NullReferenceException on line 64

work-arounds

• If you set UpdateBatchSize to 1 (line 106), it will work
• If you comment out using Microsoft.Data.SqlClient (line 1) and enable System.Data.SqlClient (line 2), it will work. i.e. this worked in the old library.

using Microsoft.Data.SqlClient;
// using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.Reflection;
using System.Reflection;

class Program
{
	private static readonly List<string> _columns = new() { "SSN1", "SSN2" };
	private const string s_DatabaseName = "AlwaysEncryptedAndBatch";

	static async Task Main(string[] args)
	{
		try
		{
			var assemblyName = Assembly.GetAssembly(typeof(SqlConnection))?.GetName().Name;

			var encryptionAttribute = assemblyName == "System.Data.SqlClient"
				? "Encrypt=true"
				: "Column Encryption Setting=Enabled";

			var connectionString = $"Server=localhost;Database={s_DatabaseName};Integrated Security=true;{encryptionAttribute};TrustServerCertificate=True";
			var connection = new SqlConnection(connectionString);
			var adapter = CreateDataAdapter(connection);

			// This connection doesn't even have to be open for the problem to occur
			// We'll leave this in to avoid red herrings
			if (connection.State != ConnectionState.Open)
			{
				await connection.OpenAsync();
			}

			var dataTable = Populate();

			await Update(dataTable, adapter);

			Console.WriteLine("Complete");
		}
		catch (Exception ex)
		{
			Console.WriteLine($"General Error: {ex.Message}");
		}
	}

	// Update the DataTable
	private static async Task Update(DataTable dataTable, SqlDataAdapter adapter)
	{
		// Modify SSN1 and SSN2 values in the DataTable
		int rowCount = 0;
		foreach (DataRow row in dataTable.Rows)
		{
			rowCount++;

			row["SSN1"] = $"{rowCount:000}-11-{DateTime.Now:HHmm}";
			row["SSN2"] = $"{rowCount:000}-22-{DateTime.Now:HHmm}";
		}

		// Update the database using the adapter
		int updatedRows = 0;

		// NULL REFERENCE EXCEPTION occurs HERE
		// when UpdateBatchSize is set to a value greater than 1
		updatedRows = await Task.Run(() => adapter.Update(dataTable));
	}

	private static SqlDataAdapter CreateDataAdapter(SqlConnection connection)
	{
		var insertCommand = new SqlCommand("InsertBuyerSeller", connection)
		{
			CommandType = CommandType.StoredProcedure
		};

		insertCommand.Parameters.AddRange(new SqlParameter[]
		{
			new SqlParameter("@BuyerSellerID", SqlDbType.Int) { SourceColumn = "BuyerSellerID" },
			new SqlParameter("@SSN1", SqlDbType.VarChar, 255) { SourceColumn = "SSN1" },
			new SqlParameter("@SSN2", SqlDbType.VarChar, 255) { SourceColumn = "SSN2" },
		});

		insertCommand.UpdatedRowSource = UpdateRowSource.None;

		// In order for this program to work in it's entirety,
		// the UpdateBuyerSeller stored procedure needs to exist in the database.
		// We don't get that far in this example
		var updateCommand = new SqlCommand("UpdateBuyerSeller", connection)
		{
			CommandType = CommandType.StoredProcedure
		};

		// Add all required parameters for UpdateBuyerSeller stored procedure
		updateCommand.Parameters.AddRange(new SqlParameter[]
		{
			new SqlParameter("@BuyerSellerID", SqlDbType.Int) { SourceColumn = "BuyerSellerID" },
			new SqlParameter("@SSN1", SqlDbType.VarChar, 255) { SourceColumn = "SSN1" },
			new SqlParameter("@SSN2", SqlDbType.VarChar, 255) { SourceColumn = "SSN2" },
		});


		updateCommand.UpdatedRowSource = UpdateRowSource.None;
		
		var adapter = new SqlDataAdapter
		{
			InsertCommand = insertCommand,
			UpdateCommand = updateCommand,
			UpdateBatchSize = 10 // <-- PROBLEM CAUSED HERE. If this is set to 1 (or not set at all), everything works fine
		};

		return adapter;
	}

	private static DataTable Populate()
	{
		var dataTable = new DataTable();
		dataTable.Columns
			.AddRange(
				new DataColumn[]
				{
					new DataColumn("BuyerSellerID", typeof(int)),
					new DataColumn("SSN1", typeof(string)),
					new DataColumn("SSN2", typeof(string)),
				});

		dataTable.TableName = "BuyerSeller";
		dataTable.Rows.Add(1, "123-45-6789", "987-65-4321");
		dataTable.Rows.Add(2, "234-56-7890", "876-54-3210");
		dataTable.Rows.Add(3, "345-67-8901", "765-43-2109");
		dataTable.Rows.Add(4, "456-78-9012", "654-32-1098");
		return dataTable;
	}
}

If you want to create dummy procs to run this end-to-end

/*
Create a database called "AlwaysEncryptedAndBatch"
Run this script to create two stored procedures in the database
*/

Use AlwaysEncryptedAndBatch
GO

Create Procedure InsertBuyerSeller
@BuyerSellerID int,
@SSN1 varchar(20),
@SSN2 varchar(20)

AS

BEGIN
	Select 1
END
GO

Grant exec On InsertBuyerSeller To Public
GO

Create Procedure UpdateBuyerSeller
@BuyerSellerID int,
@SSN1 varchar(20),
@SSN2 varchar(20)

AS

BEGIN
	Select 1
END
GO

Grant exec On UpdateBuyerSeller To Public
GO

Expected behavior

• The stored procedures should be called successfully without a NullReferenceException, even when Always Encrypted is enabled and UpdateBatchSize is greater than 1.

Further technical details

Microsoft.Data.SqlClient version: (major versions of 5 and 6)
.NET target: (e.g. .NET Framework 4.8, Net 8.0)
SQL Server version: (e.g. SQL Server 2019+)
Operating system: (e.g. Windows 11)

Additional context
We've been using the SqlDataAdapter for a while now. We recently (past year) set the UpdateBatchSize to 10 to help with areas with lots of update calls. We are in the process of adding Always Encrypted. We currently use the System.Data.SqlClient. The recommendation seems to be moving to Microsoft.Data.SqlClient, but we can't use it in its current state.

When looking at the stack trace, it appears that the problem occurs when _batchRPCMode is true (UpdateBatchSize > 1, probably). _RPCList[i].systemParams is null and there is no null check.

// SqlCommand

if (_batchRPCMode)
{
	Dictionary<_SqlRPC, _SqlRPC> dictionary = new Dictionary<_SqlRPC, _SqlRPC>();
	for (int i = 0; i < _RPCList.Count; i++)
	{
		if (_RPCList[i].systemParams.Length > 1)
		{
			_RPCList[i].needsFetchParameterEncryptionMetadata = true;
			_SqlRPC describeParameterEncryptionRequest = new _SqlRPC();
			PrepareDescribeParameterEncryptionRequest(_RPCList[i], ref describeParameterEncryptionRequest, (i == 0) ? array : null);
			dictionary.Add(describeParameterEncryptionRequest, _RPCList[i]);
		}
	}
	describeParameterEncryptionRpcOriginalRpcMap = new ReadOnlyDictionary<_SqlRPC, _SqlRPC>(dictionary);
	if (describeParameterEncryptionRpcOriginalRpcMap.Count == 0)
	{
		return null;
	}
	inputParameterEncryptionNeeded = true;
	_sqlRPCParameterEncryptionReqArray = new _SqlRPC[describeParameterEncryptionRpcOriginalRpcMap.Count];
	describeParameterEncryptionRpcOriginalRpcMap.Keys.CopyTo(_sqlRPCParameterEncryptionReqArray, 0);
}

AlwaysEncryptedBatchSizeBug.zip