3

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 ;-).

  • Hmm, and does MSDTC show an aborted transaction? – Sean Gallardy Mar 2 '16 at 1:56
  • What happens when you attempt in a standard connection begin distributed transaction through t-sql? Do you still get the abort? I'm thinking this has nothing to do with sql but with msdtc itself. – Sean Gallardy Mar 2 '16 at 7:53
  • @SeanGallardy Using BEGIN DISTRIBUTED TRAN; and then some inserts and then COMMIT; worked just fine. I will post my test code tomorrow...need to finish another project today.. – Solomon Rutzky Mar 2 '16 at 20:01
  • Do you have any exception and/or transaction handling in your code? What is the output in the errorlog if you turn on trace flag 8501 and 3605? (8501 is for DTC tracing and 3605 allows redirection to the log). – Sean Gallardy Mar 6 '16 at 5:00
  • @SeanGallardy Hey there. Sorry I haven't posted the test code yet. Busier than expected. But I think I can do it tomorrow morning. I did try those 2 trace flags and the only thing in error.log is: "DTC: Xact made distributed, ITransaction* @00000000171C0830". – Solomon Rutzky Mar 6 '16 at 7:18
1

We had the same problem (TransactionAbortedException when creating a new TransactionScope using ScopeOption = Required inside a SQLCLR function or strored proc in SQL Server 2012). The problem was caused by .NET Framework 4.6.1.

We had to uninstall this framework by uninstalling the KB3102467 and restart the server.

Article on uninstalling framework 4.6.1 : How to Uninstall .NET Framework 4.6.1

  • Thanks and interesting. I just checked and I do have Framework 4.6 and 4.6.1, and both were installed prior to me running these tests. Did / do you also have Framework 4.6 installed? If you did, did you uninstall it? I did not update the Question but I did get this running in a limited sense: tested code running on SQL Server 2008 on a VM and it could do the Distributed Tran to the host OS running SQL Server 2012 & 2014. I don't think it worked the other way around. I doubt the VM has Framework 4.6.x since it is XP. I will try to finish cleaning up my test code to post and run without 4.6.1. – Solomon Rutzky Apr 20 '16 at 14:31
  • When uninstalling Framework 4.6.1, Framework 4.6 is uninstalled also. – Boups Apr 20 '16 at 14:46
  • Ah, good to know. I will try to get back to this asap...hadn't meant to step away for so long but got busy on a couple of other things... – Solomon Rutzky Apr 20 '16 at 14:54
0

1.> Right-click your project in Solution Explorer and select Properties.

2.> In the properties window select Database on the left. Then in the resulting pane on the right, set Permission drop-down list as External.

3.> In the Assembly text box type the following acronym for database owner: dbo

4.> In SQL Server Studio, open a query window and type and run:

ALTER DATABASE [Your Database] SET TRUSTWORTHY ON

After all that try your TestProc procedure again.

  • Hi there and thanks for sharing these thoughts. Unfortunately, as indicated in the Question, just above the UPDATE section, I have already tried this combination of TRUSTWORTHY ON and EXTERNAL ACCESS. I believe I tried this first, before moving on to UNSAFE (still with TRUSTWORTHY ON) and then onto signing and TRUSTWORTHY OFF (still as UNSAFE). – Solomon Rutzky Mar 10 '16 at 20:21
  • I noticed you could not open the log file. There have been issues with the Network Service account not having the right access: try icacls c:\windows\system32\msdtc\msdtc.log /grant "networkservice":F – Sting Mar 10 '16 at 20:26
  • The log file error was from me trying to process it, and I don't think it is a permissions issue since I can view the file using type and more, but the tool that is supposed to analyse that file, running in the same CMD window, was giving that error. Still, I will try the icacls command as you are suggesting. – Solomon Rutzky Mar 10 '16 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.