This platform does not support distributed transactions

I have an endpoint which has its queue for example in DB1. It also connects to different databases DB2 and DB3 in the same transaction on the same database server. When I tried to open Sqlconnection from System.Data.SqlClient package with DB2 database, it fails at statement connection.Open() as below and throw an exception mentioned at the bottom.

using (var connection = new SqlConnection(“XXX”)
{
connection.Open();
}

I am using .Net core 3.1,NserviceBus 7.2.3 and PetaPoco.Compiled 6.0.452

I thought DTC is required when we share transaction between two machines, but here databases and application are on the same machine (my laptop with windows 10). Why it is giving me error like this. Is it DTC problem or something else? How to handle this scenario in production where DB1, DB2 and application are in different servers?

System.PlatformNotSupportedException: This platform does not support distributed transactions.
   at System.Transactions.Distributed.DistributedTransactionManager.GetDistributedTransactionFromTransmitterPropagationToken(Byte[] propagationToken)
   at System.Transactions.TransactionInterop.GetDistributedTransactionFromTransmitterPropagationToken(Byte[] propagationToken)
   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.ConvertToDistributedTransaction(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.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   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.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()

we had a similar problem. we found that the connection strings can only differ by DB name. so now we run this at startup

  public static class ConnectionValidator
    {
        public static void Run(IConfiguration configuration)
        {
            var section = configuration.GetSection("ConnectionStrings");

            var sections = section.GetChildren();
            Run(sections.ToDictionary(x => x.Key, x => x.Value));
        }

        public static void Run(IDictionary<string, string> sections)
        {
            var cleaned = new List<string>();
            foreach (var section in sections)
            {
                var connection = section.Value;
                var builder = new SqlConnectionStringBuilder(connection);
                connection = connection.Replace(builder.InitialCatalog, "");
                cleaned.Add(connection);
            }

            if (cleaned.Distinct().Count() > 1)
            {
                var join = string.Join(Environment.NewLine, sections.Select(x => $"    {x.Key}: {x.Value}"));
                var error = $@"Connection strings may only differ by DB name.
Connections:
{join}";
                var logger = Log.ForContext(typeof(ConnectionValidator));
                logger.Fatal(error);
                throw new Exception(error);
            }
        }
    }

The behavior depends on the transaction level you selected for SQL Server transport and on how you manage your data. I am assuming you are using the default NSB transaction settings (TransactionScope) which means SQL Server transport creates a TransactionScope for you and opens its connection within that scope. The transport closes its connection before the message handler is executed.

Now you open you connection(s) do DB2 and DB3. If you are opening and closing the connection sequentially like this:

using (var conn = OpenConnection("DB2"))
{
   //Do work
}
using (var conn = OpenConnection("DB3"))
{
   //Do work
}

then like @Simon_Cropp said, it all depends on the connection strings involved. If they differ in the parameters provided in them, you will get a DTC escalation.

If you open the connections concurrently like this:

using (var conn = OpenConnection("DB2"))
using (var conn = OpenConnection("DB3"))
{
   //Do work in DB 2
   //Do work in DB 3
}

even if both connection string are identical you will get a DTC escalation.

Szymon

Thanks for the solution. But in my case, both connections are on different database server instances.

Thanks for the detailed explanation. What is solution to this problem having new service is on .net core with queue on one database instance and wants to send message on other endpoint with queue other db instance.

If your sender and receiver endpoints have queues in different DB servers you have two options. Assume endpoints are named Sender and Receiver and use databases DB1 and DB2 respectively.

SQL Server Agent job

You need to create an additional queue named Receiver in the DB1 server. Then you need to create a SQL Server Agent job that takes messages from Receiver queue in DB1 and moves them to the Receiver queue in DB2. In order to do so you need to configure DB1 and DB2 as linked servers and create a stored procedure in DB1 that deletes a row from the local table and inserts it to DB2. In building that stored procedure you can use receive SQL Server transport statement as inspiration.

NServiceBus.Router

NServiceBus router is an open source (not part of Particular Platform) tool that connects different transports or broker instances. It can be used to connect SQL Server transport instances that use different databases. Here’s a sample.

@SzymonPobiega - This worked fine when I used transaction receive only mode in router. In trasactionscope mode it was throwing platform not support exception.

Yeah. Unfortunately distributed transactions are not supported in .NET core (yet? not sure if they are going to be supported).