Different error handling between Microsoft.Data.SqlClient and System.Data.SqlClient

[zx48]

Describe the bug

Some Background

We're porting our solution (.Net 4.7.2) over to use Microsoft.Data.SqlClient from System.Data.SqlClient. There are some components we're still using (like EF) that don't support MDS so we're looking at how the two clients can co-exist.

In the past we've tried to avoid requiring distributed transactions where possible but there's a chance we'll now have some if the two clients are used in the same transaction. To track this, our TransactionScopeFactory class in use throughout the solution has had some optional logging added which is sent to Serilog.

During testing of an ASP.Net controller, we found that we were unexpectedly getting escalating transactions. The code itself (which I can't send over) was accessing data in the same table (in two serial queries) and then an AJAX callback was doing similar. Not ideal, and under some load was causing issues for MDS.

The interesting thing however is that SDS and MDS perform differently. SDS was working fine whereas MDS was encountering issues, and these would vary depending on what we did in the TransactionCompleted event handler we added in the TransactionScopeFactory class.

I can't reproduce the exact issue but during the course of my investigations a test I've written shows that SDS and MDS return different errors in the same error situations, and I wanted to flag this up. I feel they're related to my issue but not the same.

The Issue

SDS and MDS return different errors in the same error situations. Is this correct/acceptable? I just wanted to flag this up since it may make porting difficult.

In general, where for SDS I might get "The transaction has aborted" as part of an aggregate exception for MDS I get "The operation is not valid for the state of the transaction". Should they not act in the same way?

Here are two examples of the same test for SDS and then MDS:

Exception message: System.AggregateException: One or more errors occurred.
Stack trace:
System.AggregateException: One or more errors occurred. ---> System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction. ---> System.Data.SqlClient.SqlException: There is already an open DataReader associated with this Command which must be closed first. ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx)
   at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
   at System.Transactions.Transaction.Promote()
   at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
   at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
   at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at SqlClientTestLib.TestSds.GetOpenConnection() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestSds.cs:line 31
   at SqlClientTestLib.TestSds.TestRead(String queryText) in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestSds.cs:line 14
   at SqlClientTestLib.TestBase.RunTest() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestBase.cs:line 22
   at SqlClientTest.Program.<>c__DisplayClass2_0.<Test>b__0() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 104
   at SqlClientTest.Program.<>c__DisplayClass2_0.<Test>b__2() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 118
   at System.Threading.Tasks.Task.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.FastWaitAll(Task[] tasks)
   at System.Threading.Tasks.Parallel.Invoke(ParallelOptions parallelOptions, Action[] actions)
   at System.Threading.Tasks.Parallel.Invoke(Action[] actions)
   at SqlClientTest.Program.Test(StreamWriter log, StreamWriter results, String connectionString, Int32 iterations, TestOptions testOptions) in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 118
---> (Inner Exception #0) System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction. ---> System.Data.SqlClient.SqlException: There is already an open DataReader associated with this Command which must be closed first. ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx)
   at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
   at System.Transactions.Transaction.Promote()
   at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
   at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
   at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at SqlClientTestLib.TestSds.GetOpenConnection() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestSds.cs:line 31
   at SqlClientTestLib.TestSds.TestRead(String queryText) in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestSds.cs:line 14
   at SqlClientTestLib.TestBase.RunTest() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestBase.cs:line 22
   at SqlClientTest.Program.<>c__DisplayClass2_0.<Test>b__0() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 104
   at SqlClientTest.Program.<>c__DisplayClass2_0.<Test>b__2() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 118
   at System.Threading.Tasks.Task.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()<---

Exception message:
Stack trace:
System.AggregateException: One or more errors occurred. ---> System.Transactions.TransactionException: The operation is not valid for the state of the transaction.
   at System.Transactions.TransactionStatePSPEOperation.get_Status(InternalTransaction tx)
   at System.Transactions.TransactionInformation.get_Status()
   at Microsoft.Data.SqlClient.SqlDelegatedTransaction.Promote() in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDelegatedTransaction.cs:line 258
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
   at System.Transactions.Transaction.Promote()
   at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
   at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
   at Microsoft.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 752
   at Microsoft.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 565
   at Microsoft.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 431
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1489
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 2293
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1831
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1704
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 538
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnectionFactory.cs:line 143
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 148
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 0
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionInternal.cs:line 768
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionClosed.cs:line 71
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1946
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1934
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1495
   at Microsoft.Data.SqlClient.SqlConnection.Open() in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1466
   at SqlClientTestLib.TestMds.GetOpenConnection() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestMds.cs:line 30
   at SqlClientTestLib.TestMds.TestRead(String queryText) in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestMds.cs:line 13
   at SqlClientTestLib.TestBase.RunTest() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestBase.cs:line 22
   at SqlClientTest.Program.<>c__DisplayClass2_0.<Test>b__1() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 108
   at SqlClientTest.Program.<>c__DisplayClass2_0.<Test>b__2() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 118
   at System.Threading.Tasks.Task.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.FastWaitAll(Task[] tasks)
   at System.Threading.Tasks.Parallel.Invoke(ParallelOptions parallelOptions, Action[] actions)
   at System.Threading.Tasks.Parallel.Invoke(Action[] actions)
   at SqlClientTest.Program.Test(StreamWriter log, StreamWriter results, String connectionString, Int32 iterations, TestOptions testOptions) in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 118
---> (Inner Exception #0) System.Transactions.TransactionException: The operation is not valid for the state of the transaction.
   at System.Transactions.TransactionStatePSPEOperation.get_Status(InternalTransaction tx)
   at System.Transactions.TransactionInformation.get_Status()
   at Microsoft.Data.SqlClient.SqlDelegatedTransaction.Promote() in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDelegatedTransaction.cs:line 258
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
   at System.Transactions.Transaction.Promote()
   at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
   at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
   at Microsoft.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 752
   at Microsoft.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 565
   at Microsoft.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 431
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1489
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 2293
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1831
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 1704
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 538
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnectionFactory.cs:line 143
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 148
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 0
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionInternal.cs:line 768
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionClosed.cs:line 71
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1946
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1934
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1495
   at Microsoft.Data.SqlClient.SqlConnection.Open() in H:\tsaagent1\_work\21\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1466
   at SqlClientTestLib.TestMds.GetOpenConnection() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestMds.cs:line 30
   at SqlClientTestLib.TestMds.TestRead(String queryText) in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestMds.cs:line 13
   at SqlClientTestLib.TestBase.RunTest() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTestLib\TestBase.cs:line 22
   at SqlClientTest.Program.<>c__DisplayClass2_0.<Test>b__1() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 108
   at SqlClientTest.Program.<>c__DisplayClass2_0.<Test>b__2() in C:\Users\JonesC\source\repos\SqlClientTest\SqlClientTest\Program.cs:line 118
   at System.Threading.Tasks.Task.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()<---

To reproduce

I've attached my test solution and an example of the output.

The test exercises each client with different combinations of settings and logging.

The output from the tests are in two files - SqlClientTest_Log.txt which is a log of the tests and SqlClientTest_Results.csv.txt which is a CSV you can filter in Excel.

Expected behavior

I expect the clients to fail but I think I expected them to fail with the same error. I may be wrong.

Further technical details

Microsoft.Data.SqlClient version: 2.0.1
.NET target: Framework 4.7.2
SQL Server version: SQL Server 2019 Developer
Operating system: Windows 10

SqlClientTest_Log.txt
SqlClientTest_Results.csv.txt
SqlClientTest_Results.zip

[cheenamalhotra]

@cjindustries

If you take a closer look, you'll notice the stacktraces are little different as it fails in Microsoft.Data.SqlClient during status check before trying to promote transaction:

System.AggregateException: One or more errors occurred. ---> System.Transactions.TransactionException: 
The operation is not valid for the state of the transaction.
   at System.Transactions.TransactionStatePSPEOperation.get_Status(InternalTransaction tx)
   at System.Transactions.TransactionInformation.get_Status()
   at Microsoft.Data.SqlClient.SqlDelegatedTransaction.Promote() 

whereas in System.Data.SqlClient, it actually went to Execute Transaction Promotion and then failed:

System.AggregateException: One or more errors occurred. ---> 
System.Transactions.TransactionAbortedException: The transaction has aborted. ---> 
System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction. ---> 
System.Data.SqlClient.SqlException: There is already an open DataReader associated with this Command which must be closed first. ---> 
System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlDelegatedTransaction.Promote()

This difference is due to recent changes we made to handle delegated transactions better in PR #543 which is actually keeping the driver safe and fixes some critical issues by validating transaction state and not making unwanted server calls.

I don't see it as a bug as this is a new library and is in active development cycle, now open source. We try our best to be backwards compatible, but such changes are expected as we fix critical issues and move forward.

[zx48]

@cheenamalhotra thanks for the reponse, and understood; I just wanted to flag it for review.

I must say that the response of MDS in this case is less useful in terms of information that SDS, since SDS gives me an idea of the root cause.