Distributed Transaction error/issue with .NET 7

[vsfeedback]

This issue has been moved from a ticket on Developer Community.

---

[severity:It's more difficult to complete my work] [regression] [worked-in:worked in .Net Framework]
When we moved from framework to .NET (previously .NET core), we lost the distributed transaction capability. NET 7 was supposed to address this issue for Window platforms, but we are not able to get it to work. I did a simple test with just two queries to two different SQL databases. Once the Open command is issued on the second database, it will not establish a connection and eventually times out.

I have tested it with both the System.Data.SqlClient and Microsoft.Data.SqlClient drivers and get the same error. The only thing coming back in the error message is "Distributed". It seems to have cut off the rest of the error message so I can't tell exactly what it is saying, just that it is related to distributed transaction almost certainly. I managed to get a screen shot which does show the error message though.

Our network team has confirmed there are no firewalls blocking access and MSDTC seems to be set correctly on both machines. The current configuration works for distributed transactions in the .Net Framework. Also, if I remove the TransactionScope transaction from my test code, the connections work fine - so I know I can reach the databases.

This from a Windows 10 machine. MSDTS has been opened to allow connections. TransactionManager.ImplicitDistributedTransactions has been set to True.

---

Original Comments

Feedback Bot on 12/29/2022, 03:13 AM:

(private comment, text removed)

Feedback Bot on 1/4/2023, 03:01 AM:

(private comment, text removed)

Feedback Bot on 1/10/2023, 07:53 AM:

(private comment, text removed)

---

Original Solutions

(no solutions)

[ghost]

Tagging subscribers to this area: @roji
See info in area-owners.md if you want to be subscribed.

Issue Details

---

This issue has been moved from a ticket on Developer Community.

---

[severity:It's more difficult to complete my work] [regression] [worked-in:worked in .Net Framework]
When we moved from framework to .NET (previously .NET core), we lost the distributed transaction capability. NET 7 was supposed to address this issue for Window platforms, but we are not able to get it to work. I did a simple test with just two queries to two different SQL databases. Once the Open command is issued on the second database, it will not establish a connection and eventually times out.

I have tested it with both the System.Data.SqlClient and Microsoft.Data.SqlClient drivers and get the same error. The only thing coming back in the error message is "Distributed". It seems to have cut off the rest of the error message so I can't tell exactly what it is saying, just that it is related to distributed transaction almost certainly. I managed to get a screen shot which does show the error message though.

Our network team has confirmed there are no firewalls blocking access and MSDTC seems to be set correctly on both machines. The current configuration works for distributed transactions in the .Net Framework. Also, if I remove the TransactionScope transaction from my test code, the connections work fine - so I know I can reach the databases.

This from a Windows 10 machine. MSDTS has been opened to allow connections. TransactionManager.ImplicitDistributedTransactions has been set to True.

---

Original Comments

Feedback Bot on 12/29/2022, 03:13 AM:

(private comment, text removed)

Feedback Bot on 1/4/2023, 03:01 AM:

(private comment, text removed)

Feedback Bot on 1/10/2023, 07:53 AM:

(private comment, text removed)

---

Original Solutions

(no solutions)

Author:	vsfeedback
Assignees:	-
Labels:	area-System.Transactions
Milestone:	-

[roji]

This sounds like it could be dotnet/SqlClient#1800, although IIRC the bug is only in Microsoft.Data.SqlClient and System.Data.SqlClient should work fine.

I suggested waiting for Microsoft.Data.SqlClient 5.1.0 (should be very soon) and then trying with that. Other than that, I'm going to need a minimal, runnable code sample which produces the problem in order to investigate.

[donr484]

I have never been able to get distributed transactions to work in .NET 6 or 7 either, even though the same code works fine in .NET Framework 4.8.1.

I really would like explicit distributed transactions using CommittableTransaction and DependentTransaction to work, but I've also tried implicit distributed transactions using TransactionScope.

I'm using VB.NET in Visual Studio 2022 ver 17.5.2 targeting .NET 7 and SQL Server 2022. I'm using Microsoft.Data.SqlClient 5.1.0, and I also have the latest OLE and ODBC drivers installed on Windows 11 Enterprise 64bit 22H2 build 22621.1413.

Here is simple console app code for an explicit distributed transaction (with the strSqlCon declaration omitted). An exception occurs every time a second sql connection enlists.

		Dim cTrans As New CommittableTransaction(
			options:=
				New TransactionOptions With {
					.IsolationLevel =
						System.Transactions.IsolationLevel.Serializable,
					.Timeout =
						New TimeSpan(
							hours:=
								0,
							minutes:=
								1,
							seconds:=
								0
						)
				}
		)
		
		Console.WriteLine("committable transaction created")
		
		'-----

		Dim sqlCon1 As New SqlConnection(
			connectionString:=
				strSqlCon
		)

		sqlCon1.Open()
		
		Console.WriteLine("sqlcon 1 open")

		Dim dTrans1 As DependentTransaction =
			cTrans.DependentClone(
				cloneOption:=
					DependentCloneOption.BlockCommitUntilComplete
			)
			
		Console.WriteLine("dependent transaction 1 created")

		sqlCon1.EnlistTransaction(
			transaction:=
				dTrans1
		)
		
		Console.WriteLine("sqlcon 1 enlisted in dependent transaction 1")

		'-----

		Dim sqlCon2 As New SqlConnection(
			connectionString:=
				strSqlCon
		)

		sqlCon2.Open()
		
		Console.WriteLine("sqlcon 2 open")

		Dim dTrans2 As DependentTransaction =
			cTrans.DependentClone(
				cloneOption:=
					DependentCloneOption.BlockCommitUntilComplete
			)
			
		Console.WriteLine("dependent transaction 2 created")

		sqlCon2.EnlistTransaction(
			transaction:=
				dTrans2
		)
		
		Console.WriteLine("sqlcon 2 enlisted in dependent transaction 2")
		
		'-----

		'do stuff with the sql connections...
		
		'-----
		
		dTrans1.Complete()
		dTrans1.Dispose()					
		Console.WriteLine("dependent transaction 1 complete")	
																						
		sqlCon1.Close()											
		sqlCon1.Dispose()
		Console.WriteLine("sqlcon 1 closed")	

		dTrans2.Complete()										
		dTrans2.Dispose()											
		Console.WriteLine("dependent transaction 2 complete")
															
		sqlCon2.Close()											
		sqlCon2.Dispose()
		Console.WriteLine("sqlcon 2 closed")	

		cTrans.Commit()
		cTrans.Dispose()
		Console.WriteLine("committable transaction committed")	

The same thing happens with an implicit distributed transaction, as soon as the second sql connection is opened.

		Using dtScope As New TransactionScope

			Console.WriteLine("dtscope created")	

			Dim sqlCon1 As New SqlConnection(
				connectionString:=
					strSqlCon
			)

			sqlCon1.Open()
		
			Console.WriteLine("sqlcon 1 open")
		
			Dim sqlCon2 As New SqlConnection(
				connectionString:=
					strSqlCon
			)

			sqlCon2.Open()
		
			Console.WriteLine("sqlcon 2 open")
		
			'-----

			'do stuff with the sql connections...
		
			'-----
																	
			sqlCon1.Close()											
			sqlCon1.Dispose()
			Console.WriteLine("sqlcon 1 closed")	
										
			sqlCon2.Close()											
			sqlCon2.Dispose()
			Console.WriteLine("sqlcon 2 closed")	
			
			'-----
			dtScope.Complete()
			Console.WriteLine("dtscope complete")	

		End Using

		Console.WriteLine("dtscope committed")

I can see the transaction being aborted in Component Services under DTC statistics in .NET 7, but it will be committed in .NET Framework 4.8.1.

[donr484]

The exception I get with explicit transactions after the second enlist in .NET 7 is this:

Exception thrown: 'System.NotSupportedException' in System.Transactions.Local.dll
An unhandled exception of type 'System.NotSupportedException' occurred in System.Transactions.Local.dll
Implicit distributed transactions have not been enabled. If you're intentionally starting a distributed transaction, set TransactionManager.ImplicitDistributedTransactions to true.

The exception I get after the second sql connection opens with implicit transactions in .NET 7 is this:

Exception thrown: 'System.NotSupportedException' in Microsoft.Data.SqlClient.dll
An unhandled exception of type 'System.NotSupportedException' occurred in Microsoft.Data.SqlClient.dll
Implicit distributed transactions have not been enabled. If you're intentionally starting a distributed transaction, set TransactionManager.ImplicitDistributedTransactions to true.

Note I've tried every combination of "Enlist=[true|false];" and "Transaction Binding=[Implicit|Explicit] Unbind;" in the sql connection string.

I've tried setting implicit transactions on in the SQL Server connection options.

DTC is configured and firewall exceptions are in place.

The exact same code works fine with .NET Framework 4.8.1.

[roji]

The .NET 7 distributed transaction support requires that you explicitly opt into it; this is different from .NET Framework, where it was always available. To opt into distributed transactions, add the following at the very start of your program, before starting any transactions:

TransactionManager.ImplicitDistributedTransactions = true;

This is indeed something we need to document better.

Some further responses to your comments:

I have never been able to get distributed transactions to work in .NET 6 or 7 either, even though the same code works fine in .NET Framework 4.8.1.

DIstributed transactions weren't supported in .NET 6.0. Windows-only support was introduced in .NET 7.0 only (#715).

I really would like explicit distributed transactions using CommittableTransaction and DependentTransaction to work, but I've also tried implicit distributed transactions using TransactionScope.

You should be able to use both explicit and implicit distributed transactions. There's no difference between them in terms of support, they're just different API styles.

I also have the latest OLE and ODBC drivers installed on Windows 11 Enterprise 64bit 22H2 build 22621.1413

FWIW neither OleDB nor ODBC are used when using SqlClient, so these shouldn't be necessary.

[roji]

I'm going to go ahead and close this issue as the original poster didn't provide a repro, and the 2nd question was answered. However, feel free to continue posting questions here, I'll be happy to answer.

[donr484]

TransactionManager.ImplicitDistributedTransactions = true

That was the missing line. Thanks!

I would have never figured that out from any of the online documentation, especially since I am trying to use explicit distributed transactions.