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.