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