DTC – Cannot Enlist Transaction | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTC – Cannot Enlist Transaction

Hi, I’ve got two boxes: ServerA is a standalone Windows 2000 Std Server with SQL 2000 std SP3
ServerB is a clustered (act/pas) Windows 2003 Ent with SQL 2000 Ent SP3 Whenever I try to perform a distributed transaction between them (either way) I get this error: Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ ITransactionJoin::JoinTransaction returned 0x8004d00a]. There is a lot of stuff on the web about this error. This is what I’ve tried so far: http://support.microsoft.com/kb/301600/
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql Yet I still get the error. Here is some further information: DTC is enabled on Server B (in add/remove programs).
Name resolution seems fine (inc the MSDTC network name on ServerB.
There is no firewall between them – though they are seperated by a WAN.
DTCPing succeeds.
On ServerA the DTC service runs as local system
On ServerB the DTC service runs as NT AuthorityNetworkService
Both machines can telnet to each other on port 135
I’ve tested with XACT_ABORT on and off – same thing happens. I’m at a total loss. I’ve seen this error many times before and always solved it, it’s usually name resolution. BTW they are on seperate untrusted domains – though I don’t see why this would effect it. Any ideas? Thanks is advance
Can you check Lightweight Pooling Mode is disabled on the SQL Server. KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;248694&sd=tech for relevancy.; Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks Satya. Yes it is is disabled on both server. Though I’m not sure I understand the KB article anyway!! I forgot to mention that ServerA is running Windows 2000 SP4 and ServerB is 2003 SP1 if it makes any difference?? Thanks
Lots of info in these forums:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8903 On Windows 2003 you also need to perform the following:
Thanks Argyle – it would seem you are the MSDTC guru. ServerB was missing the registry key you pointed out. Added it and restarted the services but still nothing. I ran the DTCTest tool. The output is: Enlisting Connection in Transaction
SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server Driver]
Distributed transaction error’
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cur
sor state
Typical Errors in DTC Output When
a. Firewall Has Ports Closed
b. Bad WINS/DNS entries
c. Misconfigured network
d. Misconfigured SQL Server machine that has multiple netcards.
Aborting DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer. Is it worth re-installing MSDTC? If so do you know how to – remembering this is on a cluster. Thanks
I was confident that Argyle will have a look into this thread, referhttp://www.windowsitpro.com/Windows/Article/ArticleID/46690/46690.html about resintalling DTC on Win2K3. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I can’t see the document – I can sign in but it still doesn’t show the complete thing – I fear my subscription needs renewing. Anyway. I just had another thought: I inherited this cluster a few days ago. When I first noticed the DTC problem I noticed that DTC had been setup incorrectly. Instead of having its own IP and Name it was dependant on the Cluster IP and Cluster Name. Is it possible that this has screwed some registry settings somewhere?? Also the TurnOffRPCSecurity key was there but it was set to 0. I have since changed it but no joy.
If you are not a heavy MSDTC user having the MSDTC service in the cluster group is ok. If would not reinstall just yet. Did you perform all steps in this link:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3451 Add/Remove programs -> Enable network DTC access on both nodes and edit the registry setting on the node currently owning the MSDTC servcies. You also need to restart the MSDTC service (via cluadmin) for it to take affect. I would do a test failover if possible to verify that the registry security setting is replicated correctly during failover as well. Might be a bit different on a cluster so that this specific parameter is not involved in the failover replication of the registry, then you need to change it on both nodes instead. Have you verified that name resolution works between all involved servers on the same network (i.e. frontend or backend). This includes all names you have involved, both physical and virtual names need to be able to resolv to the same IP from all machines.
Thanks Argyle. I have been through that artcile. DTC is enabled the registry keys are correct on both nodes and I have perfect name resolution both ways (for all names including the nodes and virtual names). One thing I did notice. After I changed the registry key I started to get the "Could not enlist" message as soon as I ran the query. Where as before it took about 40 seconds to "time-out" and occur. There is something I forgot to mention before. Both the nodes in the cluster are domain controllers! Don’t ask why – I inherited this mess! On boot I get these errors in the Application Log: MS DTC could not correctly process a DC Promotion/Demotion event. MS DTC will continue to function and will use the existing security settings. Error Specifics: %1 MS DTC could not correctly process a DC Promotion/Demotion event. MS DTC will continue to function and will use the existing security settings. Error Specifics: d:srvrtmcomcomplusdtcdtcadmeuiname.cpp:9280, Pid: 2776
No Callstack,
CmdLine: C:WINDOWSsystem32msdtc.exe I think these are domain related rathar than SQL. Simon
There might be some domain security policies being applied preventing MSDTC to function. Have you verified that MSDTC is functioning on the stand alone win 2000 server as well? If nothing helps try reinstall: