in Search

Do you Twitter? Follow us @CodeBetter

Eric Wise

Business & .NET

System.Transactions MSDTC Issue

Ready to pull my hair out over this one.

Decided to use System.Transactions in my latest project, so being I'm doing my development on my local machine with a dev SQL Server 2005 install on a 2k3 machine I need to turn on MSDTC stuff...

So running the code that uses system.transactions on the server works fine, if I run the same code from my workstation calling the server I get

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

Ok, no problem, I open the tool, allow network DTC Access, I allow outbound and inbound, no authentication required. I verify that the same settings are on the server. It's using the NT AUTHORITY\NetworkServices Account. Still no dice.

I then download the dtcTester tool and run it from the command prompt on my machine connecting to the same database as my application, I get the following output:

________________________________________________________________________

Creating Temp Table for Testing: #dtc7555
Warning: No Columns in Result Set From Executing: 'create table #dtc7555 (ival i
nt)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Executing SQL Statement in DTC Transaction
Inserting into Temp...insert into #dtc7555 values (1)
Warning: No Columns in Result Set From Executing: 'insert into #dtc7555 values (
1) '
Verifying Insert into Temp...select * from #dtc7555 (should be 1): 1
Press enter to commit transaction.

Commiting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
Disconnecting from Database and Cleaning up Handles

______________________________________________________________________

So basically it works in the command prompt, but I get that error message when my local IIS tries to run a MSDTC command. Looking at the stats on the server show that running the command prompt does indeed enlist and succeed. So it's just ASP .NET doing this that is freaking out.

Suggestions?

EDIT

Here's what I've verified since posting.

1. MSDTC does work if I deploy the app to another windows 2003 server. It only fails on Windows XP SP2 workstations.

2. I can put enlist=false in the connection string on the workstation to prevent the transaction code, which at least lets me work on my workstation, but this is not a long term solution since I don't have transactions at that point.

3. Oddly enough... my auto increment primary keys are jumping forward when I have failed tests (woohoo 35+ failed tests today) which suggests that the MSDTC call isn't failing until the scope commit? I need to fire up the debugger tomorrow to verify this.

4. We have some support tickets with Microsoft, I'm going to get to the bottom of this.

Published Dec 06 2006, 10:52 AM by Eric Wise

Filed under: C#, SQL, Rants, ASP .NET

Comments

The Geek said:

This might be a firewall issue... try disabling the firewall if you have one on the client.

In SQL2000, there used to be a name resolution problem with similar symptoms.

# December 6, 2006 12:27 PM

Eric Wise said:

SQL 2005, no firewalls.

# December 6, 2006 12:46 PM

bob said:

Is this a web application or windows forms?

Any messages in the event logs ( security event log mostly ) that are helpful?

# December 6, 2006 1:53 PM

Eric Wise said:

Web application. And no, the only error message the damn thing will spit out is the one in the post about network being disabled, which it isn't.

# December 6, 2006 2:06 PM

Eric Wise said:

Oh, and we use the DAAB in the enterprise library, I'm wondering if that is the culprit.

# December 6, 2006 2:12 PM

fxk said:

Have you checked the security in SQL2005 for 'NT AUTHORITY\NETWORK SERVICE'?

If your local use is in BUILTIN\Administrators you are automatically and sysadmin in SQL2005. NETWORK SERVICE is granted only a few privileges to read master and msdb. They don't have the ddladmin role and therefore can't change schema information.

you could change the user you app is running under (preferred) or explicitly give NETWORK SERVICE the ability to change db schemas.

# December 6, 2006 2:16 PM

bob said:

hmmm, since it's a web application, you running it from a command prompt is different from IIS running it.

You might check the permissions for the account that IIS is running as, or add that account to local admins as a test. Just remember to remove it later on.

Is there someplace in component services to explicitly allow specific users?

# December 6, 2006 2:21 PM

Eric Wise said:

No dice, it doesn't seem to have any problems there. It's impersonated windows authentication.

# December 6, 2006 2:37 PM

Eric Wise said:

SOLVED:

Put enlist=false in the connection string. I'm so pissed I spent hours trying to get something that mundane.

# December 6, 2006 2:48 PM

bob said:

Whenever I end up wasting time like this, I always look on the bright side:

At least you'll never forget this particular solution ever again.... :)

# December 6, 2006 5:09 PM

Tomas Restrepo said:

Actually, Eric, I don't think using enlist=false actually solves anything at all; it just tells the sqlclient to ignore the context transaction (including the DTC transaction) and let the database work independent of it. So, you just threw away the transaction you created with System.Transactions.

Probably not quite what you wanted.

# December 6, 2006 6:28 PM

Eric Wise said:

I'm ok with that for the time being. In production/dev it runs on the same server so it doesn't us MSDTC. I just want to be able to execute the code on my workstation during testing. I'll continue to figure out why the hell this doesn't work as advertised... maybe after I rebuild my machine later this month.

# December 6, 2006 6:38 PM

Eric Wise said:

Updated post

# December 6, 2006 6:56 PM

KjellSJ said:

There is no need to use MSDTC against a SQL Server 2005 database. You would be better of staying LTM with System.Transactions, using DbConnectionScope as needed: http://kjellsj.blogspot.com/2006/04/systemtransactions-nesting-scopes.html

# December 8, 2006 4:03 AM

John Papa said:

Good comment on DbConnectionScope . Using the DbConnectionScope class is nice if you want to avoid invoking the MS DTC when you truly are staying local. Most architectures have classes with method like Customer.GetEntities (or wahtever the names might be) that fill a business object or a DataSet.

One way to retrofit the code to use DbConnectionScope with these methods would be to use a "using (DbConnectionScope ds)" inside of every "using(TransactionScope ts)" and to replace the code that sets the SqlConnection for a SqlCommand with the DbConnectionScope.Current.GetOpenConnection technique described in the blog post by Alazel Acheson.

The consequence of this technique is 2-fold: (1) the transaction can stay local/lightweight and (2) the connection remains open longer. This will help your app keep truly distributed transactions distributed and local ones local. However, keep in mind that you will not be taking advantage of connection pooling to manage the connections nearly as efficiently as if you closed and reopened your connections. Its a choice.

But ... Eric is having issues using DTC from his development PC. So this does not olve his issue if he ever intends on having any distributed transactions ... in that case he still neds to figure out why his dev PC is balking at him.

# December 8, 2006 10:31 AM

KjellSJ said:

Is it certain that the web-app runs with IIS and not Cassini (named WebDev.WebServer.EXE in VS2005) ? We have not been able to get DTC stuff working when hosted by Cassini. The solution, I don't know, we just switched to using IIS.

# December 9, 2006 3:20 AM

Jeroen-bart Engelen said:

We ran into the same issue at my work. I found this article: http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx

Basically, not only do you need to enable DTC on the SQL server, but also on the client from where the transactions orginate.

I got it to work after enabling DTC on the client.

# December 20, 2006 5:08 AM

vpaul said:

I've ran into this problem as well. One way I found the fixed the problem (at for me) is to change the anonymous account in IIS with a user who has Admin rights. This of course isn't best practice from a security perspective, but works.

# January 13, 2007 2:56 AM

Kakali said:

Hi!, I have enabled MSDTC in both application server as well as sql server. I have checked Network DTC Access, allow remore clients, allow remote administration, in Transaction Manager Communication i have cheked allow inbound, allow outbound and mutual authentication required and DTC Logon Account is of NT AUTHORITY\NetworkService. Still i m getting the error "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool."

Any idea how to enable it for newwork?

Please help.

Thanks in advanced

# January 22, 2007 10:59 AM

Harvey said:

Hi all,

Yes, this is a common issue with msdtc especially when you're running debugging in visual studio.

I had the same problem, and whenever the application and database are in separate servers, this problem hits. I think it has something to do with the account rights used by visual studio, because when i Ctrl+F5 or F5 it, it DOESN'T work, but when i put it into IIS, it works PERFECTLY....

# February 13, 2007 10:01 PM

LFN said:

Yup, same problem here too.

What a complete mess.

Well done Microsoft, another outstanding effort!

# February 15, 2007 11:02 AM

Arnab said:

Thanks every body

MS is great

any way i have also put that in IIS and works fone

# April 19, 2007 3:30 AM

Mathias said:

If using several TableAdapters inside a transaction, I found this post helpful.

# May 10, 2007 6:02 AM

Mathias said:

Oops. :)

forums.microsoft.com/.../ShowPost.aspx

# May 10, 2007 6:03 AM

cadull said:

Thanks for the hints.

I've managed to debug with DTC in Visual Studio 2005 SP1 on XP SP2 by setting the "NTLM Authentication" option (WebService Project Property Pages -> Start Options).

All 3 computers involved have DTC security configured with

- Network DTC Access

- Allow Inbound

- Allow Outbound

- Mutual Authentication Required

- Account = NT AUTHORITY\NetworkService

# June 10, 2008 10:29 PM

Milind Dhobe said:

Thank you Eric.

Your solution enlist=false has helped me. I was so pissed off too because of this problem and most of other posts on internet are vague and irrelevant.

Thanks once again.

Enjoy.

Cheers.

I am a happy man now.

# June 28, 2008 11:22 AM