Sql-server – Do SQLCLR objects support Distributed Transactions on regular / external connections

The most common usage of regular / external database connections in SQLCLR objects, it would seem, is to do SELECT-only queries (i.e. no changes: DML, DDL, DCL, D*%$#!L, etc). It is also fairly common to add the Connection String option Enlist=false given various restrictions imposed when Enlist=true (the default when the Enlist keyword is not specified; Enlist means whether or not to join an existing transaction).

If there is a reason to make changes via that regular / external connection, then having Enlist=false causes those changes to be independent and not part of an active transaction started in the context of the calling query. Meaning, using Enlist=false has the following behavior:

BEGIN TRAN;
-- start with no rows in [LocalTable]
INSERT INTO SchemaName.LocalTable (Column) VALUES (N'ValueA');
EXEC SchemaName.SqlClrInsertIntoLocalTable @TextToInsert=N'ValueB';

ROLLBACK;
SELECT * FROM SchemaName.LocalTable;
-- returns 1 row containing 'ValueB'

Ok. So, if we did want the changes made in SqlClrInsertIntoLocalTable to be bound by the explicit transaction started in the calling context (i.e. the BEGIN TRAN; statement), then we would either specify Enlist=true or not specify Enlist in the first place as true is the default value.

The first attempt might get the following error:

MSDTC on server 'ServerName\InstanceName' is unavailable.

Ok, so go into Control Panel \ System and Security \ Administrative Tools \ Services and click start on "Distributed Transaction Coordinator".

Try the SQLCLR Stored Procedure again and this time get the following error:

The transaction has aborted.

So, is it possible to bind an external connection to an existing transaction? Do distributed transactions even work with SqlConnection? Creating a simple Console App using TransactionScope to wrap two separate connections shows that it does indeed work. Going back to the SQLCLR object, adding TransactionScope does not work.

Of course, there are three possible values for TransactionScopeOption. Using:

• Suppress: works just like Enlist=false, specifically preventing any binding of the separate connection.
• RequiresNew: no apparent effect; works just like Suppress.
• Required: gets the lovely "The transaction has aborted" error.

(I will post my test code to PasteBin.com later, once I clean it up)

So, am I missing something / doing something wrong? I had always just assumed that doing distributed transactions in SQLCLR objects was possible, provided MSDTC was running and Enlist=true and using TransactionScope. But I cannot get it to work. Is this a limitation of working with SQLCLR objects? It certainly seems like it, but I could be missing a required step.

The Assembly is marked as WITH PERMISSION_SET = UNSAFE, but I have also tried with EXTERNAL_ACCESS. I am using UNSAFE at the moment because some transactional stuff appears to require Full Trust, though nothing that I am currently using. I had originally just set the DB to TRUSTWORTHY ON and not even signed the Assembly. However, I have now signed the Assembly, created the Asymmetric Key and Key-based Login, and granted that Login the UNSAFE ASSEMBLY permission. I then re-ran the tests just to make sure that the issue wasn't due to a lack of signing (which does help in at least one case I am aware of). I still get the "The transaction has aborted" error.

I have tried with both BEGIN DISTRIBUTED TRAN and BEGIN TRAN.

UPDATE 1

I found one resource, Troubleshooting MSDTC Permission Issues When a Distributed Transaction Starts, that indicates some problems with MSDTC are related to permissions. While that post is mainly focused on scenarios involving clustering, there are still some parts that seem like they could be relevant. Item #5 is specific to MSDTC. I ran sc sdshow msdtc and it showed that all relevant groups have the correct CC permission (among others).

As it relates to the permission, I am running the tests on SQL Server Express 2014 LocalDB, hence the security context of both the local and remote requests is that of my Windows Login. And because LocalDB runs as a background process instead of as a Service, there should be none of the "odd" behavior that sometimes results from Impersonation. And, the "external" connection is still the local machine, so I am only dealing with a single Transaction Manager/DTC.

The stack traces for the errors are as follows:

• TransactionScope is not being used:

  Msg 6522, Level 16, State 1, Procedure TestProc, Line 165
  A .NET Framework error occurred during execution of user-defined routine or aggregate "TestProc": 
  System.Transactions.TransactionAbortedException: The transaction has aborted.
  System.Transactions.TransactionAbortedException: 
     at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(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.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.SqlClient.SqlConnection.TryOpenInner(Task...
  

• TransactionScope is being used (ScopeOption = Required):

  Msg 6522, Level 16, State 1, Procedure TestProc, Line 194
  A .NET Framework error occurred during execution of user-defined routine or aggregate "TestProc": 
  System.Transactions.TransactionAbortedException: The transaction has aborted.
  System.Transactions.TransactionAbortedException: 
     at System.Transactions.TransactionStateAborted.CreateAbortingClone(InternalTransaction tx)
     at System.Transactions.DependentTransaction..ctor(IsolationLevel isoLevel, InternalTransaction internalTransaction, Boolean blocking)
     at System.Transactions.Transaction.DependentClone(DependentCloneOption cloneOption)
     at System.Transactions.TransactionScope.SetCurrent(Transaction newCurrent)
     at System.Transactions.TransactionScope.PushScope()
     at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption, TimeSpan scopeTimeout, TransactionScopeAsyncFlowOption asyncFlowOption)
     at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption, TimeSpan scopeTimeout)
     at ContextConnectionTests.TestProc(SqlString Scope, SqlString ConnectionString)
  

MSDTC is definitely registering the attempts at distributed transactions and that they are "aborted". I can see by opening Control Panel\System and Security\Administrative Tools\Component Services and then going to Component Services \ Computers \ My Computer \ Distribute Transaction Coordinator\Local DTC\Transcation Statics.

I tried viewing the MSDTC log file using the directions found here: Viewing Trace Data, but was not able to get it working. I have the required programs, but then I just get error messages about how it can't open any of the files for wrinting.

UPDATE 2

I did finally get this working, but in a very limited context. I have a VM running SQL Server 2008 on XP and it was able to run the same SQLCLR Assembly to do a Distributed Transaction to connect to the host OS (Windows 8) running SQL Server 2012.

I also had to temporarily disable Windows Firewall on both since I couldn't find the specific program name and/or ports to allow. I wouldn't recommend this for anything but testing and will eventually have to figure those details out.

I was not, however, able to get it working if the Transaction originated on the SQL Server 2012 side. I will try to find time soon to update my test code to post (originally it only had a single ConnectionString parameter as I was testing on the same instance, but then found a note in some MSDN documentation saying that DTC wouldn't work on the same instance, hence I need to clean up my very hurried modifications ;-).