SQL Server Performance Forum – Threads Archive

MS Distributed Transaction Coordinator (MSDTC)

Greetings, I’m trying to get MSDTC to work between two databases. Right now we’re just testing between our QA and DEV servers. As near as I can tell, I can implement transactions from one server (DEV->QA) but not the other way (QA->DEV). One thing I’ve noticed across several different servers is the MSDTC tab under the "My Computer" properties in Component Management varies a lot. I’m not sure if this could be causing the issue or not, but how do I verify the verison of DTC I’m running, and upgrade as necessary? DEV server
MS Windows Server 2003 Standard Edition
Microsoft SQL Server 2000 – 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: ) Services.msc
Distributed Transaction Coordinator
NT AUTHORITYNetworkService Dcomcnfg.exe
My Computer -> Properties -> MSDTC Tab -> Security Configuration
Network DTC Access is checked
Network Administration is checked
Network Transactions is checked
Network Clients is checked
XA Transactions is checked
Account is NT AUTHORITYNetworkService QA server
MS Windows Server 2003 Standard Edition Service Pack 1
Microsoft SQL Server 2000 – 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) Services.msc
Distributed Transaction Coordinator
NT AUTHORITYNetworkService Dcomcnfg.exe
My Computer -> Properties -> MSDTC Tab -> Security Configuration
Network DTC Access is checked
Client and Administration
Allow Remote Administration is checked
Transaction Manager Communication
Allow Inbound is checked
Allow Outbound is checked
No Authentication Required is selected
XA Transactions is checked
Account is NT AUTHORITYNetworkService When I run the DTCPing test I get the following: Please refer to following log file for details:
C:dtcpingDBQA3212.log
Invoking RPC method on dbdev
RPC test is successful
++++++++++++RPC test completed+++++++++++++++
Please start PING from dbdev to complete the test
Please send following LOG to Microsoft for analysis:
Partner LOG: DBDEV1732.log
My LOG: DBQA3212.log
++++++++++++Start Reverse Bind Test+++++++++++++
Received Bind call from DBDEV
Trying Reverse Bind to DBDEV
Reverse Binding success: DBQA–>DBDEV
++++++++++++Reverse Bind Test ENDED++++++++++
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to DBDEV
Received reverse bind call from DBDEV
Binding success: DBQA–>DBDEV
++++++++++++DTC Binding Test END+++++++++++++ Based on what I see with DTCPing, it appears the communication between DBDEV and DBQA is successful. When I run the DTCTest I from DBQA to DBDEV get the following: DBQA test to DBDEV
C

TCTest>dtctester dbdev xxxxx xxxxxxx
Executed: dtctester
DSN: dbdev
User Name: xxxxxxx
Password: xxxxxxx
tablename= #dtc23696
Creating Temp Table for Testing: #dtc23696
Warning: No Columns in Result Set From Executing: ‘create table #dtc23696 (ivalint)’
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction Error:
SQLSTATE=25S12,Native error=-2147168219,msg='[Microsoft][ODBC SQL Server Driver] Distributed transaction error’
Error:
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cursor state Typical Errors in DTC Output When
a. Firewall Has Ports Closed
-OR-
b. Bad WINS/DNS entries
-OR-
c. Misconfigured network
-OR-
d. Misconfigured SQL Server machine that has multiple netcards. Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer. When I run the DTCTest from DBDEV to DBQA I get the following: DBDEV test to DBQA
C:dtctest>dtctester dbqa xxxxx xxxxxxx
Executed: dtctester
DSN: dbqa
User Name: xxxxx
Password: xxxxxxx
tablename= #dtc25564
Creating Temp Table for Testing: #dtc25564
Warning: No Columns in Result Set From Executing: ‘create table #dtc25564 (ival
int)’
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Executing SQL Statement in DTC Transaction
Inserting into Temp…insert into #dtc25564 values (1)
Warning: No Columns in Result Set From Executing: ‘insert into #dtc25564 values
(1) ‘
Verifying Insert into Temp…select * from #dtc25564 (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 I’ve scanned the forums here and there were several useful threads (locked) which allowed me to get as far as I have. The problem is – although I’m still searching – I seem to be reaching a stage of diminishing returns. I’m hoping that a fresher pair of eyes than mine will be able to come up with a suggestion or too. Regards,
John Wright

1.
MSDTC has to be running on both the SQL Server and the client machine/server (web/app server, workstation etc.) to be able to work. 2.
If the MSDTC service is running on both machines but still doesn’t work then it’s most likely due to a name resolution issue or a firewall. MSDTC needs to be able to do name lookups (in both directions) on the servers involved to be able to function. To check this try and ping the SQL server by name from the web/app server and vice versa ping the web/app server by name from the SQL server. IF it’s a cluster be sure to ping the virtual SQL Server name. Use the DTCping.exe
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306843 Or an even better tool might be:
HOWTO: Use DTCTester Tool
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293799 You will notice when using these tools that even if the client connects to the MSDTC service trough the virtual SQL Server name it will sometimes still resolve the physical node name of the node that currently owns the MSDTC service in the cluster. So make sure that name reolution works for the physical node names as well and not only the virtual names. 3.
If DNS or WINS are miss-configured or traffic is blocked in routers or something then add name and IP address entries in "lmhosts" and "hosts" files on the involved SQL server and web/app server or fix the DNS or WINS. 4.
I run into a MSDTC issue with multiple IPs. Make sure than name resoultion works and resolves to the IP you intend to use for MSDTC. If you for example have a frontend and management IP make sure the name resolves to the frontend ip. This goes for both when the servers (source and destination) ping or resolve eachothers name as well as locally on each server when it resolves it’s own name. You might have to change the binding order of your networks to get around this. Also if you have muliple names on the server, for example in a cluster, all names need to be resolvable (node names, cluster names etc) from all parties involved in MSDTC. 5.
If there is a firewall between the servers look at this article on what ports to open: INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall (Q250367)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q250367 6.
For enlist errors: INFO: Error "-2147168246 (8004d00a)" Failed to Enlist on Calling Object’s Transaction
http://support.microsoft.com/default.aspx?scid=kb;EN-US;191168 7.
If the server is installed with a ghost image or other tools like it you can end up with computers having the same ID on the network used by MSDTC. 8.
If nothing else work remove and reinstall MSDTC. /Argyle

Argyle, Your reply looks familiar – I think I used this to help check off my status before posting. 1. MSDTC is running on both DBDEV and DBQA.
2. DTCPing works between DBDEV and DBQA.
DTCTest failes between DBQA->DBDEV, but not between DBDEV->DBQA. I have verified the following:
2a. Windows Firewall is turned off on DBQA. I added the ports and msdtc.exe just to be safe anyway. DBDEV doesn’t have it installed.
2b. DNS / WINS seems to be setup correctly.
2c. There is only a single NIC on DBDEV. There are two NIC cards on DBQA, but the second has been disabled.
2d. Unable to tell if I have a misconfigured "SQL Server machine".
3. DNS / WINS seems to be setup correctly.
4. No multiple IPs. Both servers are able to ping and resolve their respective names correctly.
5. No active firewall between servers.
6. No enlist error.
7. Not installed with a host image or other tool. Regarding 2d – I’m not sure what to look for exactly. Otherwise I believe I’ve tackled the other items. If you have further clarification / suggestions I would greatly appreciate it. Regards,
John Wright

Yea I have that in a text file. It should be ghost image and not host image, changed my post <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /> On windows 2003 you also need to perform the following: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3451</a> If nothing helps one can try and reinstall MSDTC The difference you have under porprties is most likely due to service pack 1 for win 2003 on one of the servers. Might be some new settings regarding SP1 and MSDTC as well.

Argyle, Outstanding! That link you just provided did the trick. When I checked DBQA it already had the TurnOffRpcSecurity DWORD in the registry, but DBDEV did not. I added the DWORD to DBDEV and re-ran DTCTester between the two machines and both were able to complete successfully! I appreciate your help, and I hope this thread proves useful to other members. Regards,
John Wright

]]>