System.Transactions MSDTC Issue

<< Note to self: Firefox Windows Authentication | Home | New Alienware and Vista >>

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.

Print posted @ Wednesday, December 06, 2006 3:52 PM

Feedback

# re: System.Transactions MSDTC Issue

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.

12/6/2006 5:27 PM |

# re: System.Transactions MSDTC Issue

SQL 2005, no firewalls.

12/6/2006 5:46 PM |

# re: System.Transactions MSDTC Issue

Is this a web application or windows forms?

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

12/6/2006 6:53 PM |

# re: System.Transactions MSDTC Issue

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.

12/6/2006 7:06 PM |

# re: System.Transactions MSDTC Issue

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

12/6/2006 7:12 PM |

# re: System.Transactions MSDTC Issue

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.

12/6/2006 7:16 PM |

# re: System.Transactions MSDTC Issue

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?

12/6/2006 7:21 PM |

# re: System.Transactions MSDTC Issue

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

12/6/2006 7:37 PM |

# re: System.Transactions MSDTC Issue

SOLVED:

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

12/6/2006 7:48 PM |

# re: System.Transactions MSDTC Issue

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

12/6/2006 10:09 PM |

# re: System.Transactions MSDTC Issue

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.

12/6/2006 11:28 PM |

# re: System.Transactions MSDTC Issue

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.

12/6/2006 11:38 PM |

# re: System.Transactions MSDTC Issue

Updated post

12/6/2006 11:56 PM |

# re: System.Transactions MSDTC Issue

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: kjellsj.blogspot.com/.../systemtransactions-nesting-scopes.html

12/8/2006 9:03 AM |

# re: System.Transactions MSDTC Issue

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.

12/8/2006 3:31 PM |

# re: System.Transactions MSDTC Issue

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.

12/9/2006 8:20 AM |

# re: System.Transactions MSDTC Issue

We ran into the same issue at my work. I found this article: blogs.msdn.com/.../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.

12/20/2006 10:08 AM |

# re: System.Transactions MSDTC Issue

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.

1/13/2007 7:56 AM |

# re: System.Transactions MSDTC Issue

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

1/22/2007 3:59 PM |

# re: System.Transactions MSDTC Issue

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

2/14/2007 3:01 AM |

# re: System.Transactions MSDTC Issue

Yup, same problem here too.

What a complete mess.

Well done Microsoft, another outstanding effort!

2/15/2007 4:02 PM |

# re: System.Transactions MSDTC Issue

Thanks every body

MS is great

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

4/19/2007 7:30 AM |

# re: System.Transactions MSDTC Issue

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

5/10/2007 10:02 AM |

# re: System.Transactions MSDTC Issue

Oops. :)

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

5/10/2007 10:03 AM |

Eric Wise