Problem with MSDTC

[Gabrielgb]

Ive been working on this error for the past week without much success.

Context : I have a bunch of server running Sql server 2005 under Windows 2003(enterprise,standard and x64) and 2 new servers running Sql server 2008 under Windows 2008 Standard. I use MS DTC with sql server and clr assemblies to transfer and modify some data to a datawarehouse.

Our 2 new windows 2008 servers have the same hardware configuration and basic software configuration. One of them wich we will call gisServer is running ArcGis and Sql Server. The other one wich we will call prodServer is running Sql server only.

Problem : On my prodServer MSDTC is not working properly but on the other one its fine.

Heres what ive done with what im thinking in order of date starting When i discovered the problem.

1. Im getting a bunch of msdtc errors under sql server. SSMS returns the following error : MSDTC on server 'prodServer' is unavailable.
2. In the event viewer I always get the 2 same entries when i try a transaction :

• Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
• The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.

3. I checked in the component service and MSDTC is started. I also checked his dependency : COM+ Event System (optional) , COM+ System Application , DCOM Server Process Launcher , Distributed Transaction Coordinator , Message Queuing , and Remote Procedure Call (RPC) are also started.
4. I checked with the network analyst and we only have a firewall for requests going to Internet. In our internal network there are no firewall .
5. I also checked if there are network policy and if windows firewall was started. No network policy and no windows firewall enabled.
6. Current MSDTC configuration

• Network DTC Access : on
• Allow Remote Clients : on
• Allow Remote Administration : on
• Allow Inbound : on
• Allow Outbout : on
• No Authentification Required
• Enable XA Transactions : on
• Logon Account : NT AUTHORITY\NetworkService

7. Tried flusing the logs and traces.
8. Tried reinstalling msdtc. No difference.
9. I tried enabling msdtc via Server Manager by installing Application Role(wich provide support for msdtc)
10. Tried dtctester here's the output


MSTDC on server is unavailable

• Firewall has ports closed
• bad WINS/DNS
• Misconfigured network
• Misconfigured SQL Server machine that has multiple netcards.

12. I have enabled Netbios over tcp ip.
13. I Noticed MSDTC service stopped by itself during troubleshooting and i can't find any information on what caused it.
14. I got few interesting log entry :

• MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system prodServer.
• Invalid command line arguments. (for MSDTC in the logs)

(i havent tried to start MSDTC from command line and it only does it from time to time...
15. MS DTC Tracing infrastructure : the attempt to flush the existing trace data failed. Internal Information : none available.
16. I RE RE RE RE RE RECHECKED that the service and its dependency were started.
17. Made a clr trigger on sql server to see if i can cause more interesting errors that could lead me to understanding the problem. here's what im getting(wich is no more interesing)

• System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.Transactions.TransactionPromotionException: MSDTC on server 'prodServer' is unavailable. ---> System.Data.SqlClient.SqlException: MSDTC on server 'prodServer' is unavailable.

18. I have checked about every help on internet and still can't find the answer.
19. In MSDTC traces i only see that transaction get aborded but no more information.
20. In component service, msdtc statistics are always at 0 even though i try generating transactions. It dosen't event registers them as aborded(but the traces does ???)
21. I also checked with process explorer and i can see msdtc running and i see its footprint going up when i generate trasactions.
22. Using debugging logs i get these events :

• The WS-AT protocol service successfully completed startup and recovery.
• Protocol ID: c05b9cad-ab24-4bb3-9440-3548fa7b4b1b
• Protocol Name: WS-AtomicTransaction 1.1
• Process Name: msdtc
• Process ID: 9204

HERES THE CONFIGURATION :
Microsoft Windows [Version 6.0.6001]

Host Name: prodServer
OS Name: Microsoft® Windows Server® 2008 Standard without Hyper-V
OS Version: 6.0.6001 Service Pack 1 Build 6001
OS Manufacturer: Microsoft Corporation
OS Configuration: Member Server
OS Build Type: Multiprocessor Free
Registered Owner: Windows User
Registered Organization:
Product ID: ********************
Original Install Date: 03/03/2009, 9:44:46 PM
System Boot Time: 25/06/2009, 9:12:22 AM
System Manufacturer: HP
System Model: ProLiant DL380 G5
System Type: x64-based PC
Processor(s): 2 Processor(s) Installed.
[01]: Intel64 Family 6 Model 23 Stepping 10 GenuineIn
tel ~2833 Mhz
[02]: Intel64 Family 6 Model 23 Stepping 10 GenuineIn
tel ~2833 Mhz
BIOS Version: *****
Windows Directory: C:\Windows
System Directory: C:\Windows\system32
Boot Device: \Device\HarddiskVolume1
System Locale: en-ca;English (Canada)
Input Locale: en-us;English (United States)
Time Zone: (GMT-05:00) Eastern Time (US & Canada)
Total Physical Memory: 12,285 MB
Available Physical Memory: 427 MB
Page File: Max Size: 24,781 MB
Page File: Available: 13,463 MB
Page File: In Use: 11,318 MB
Page File Location(s): C:\pagefile.sys
Domain: ********
Logon Server: \\******
Hotfix(s): 44 Hotfix(s) Installed.
[01]: {47740627-D81D-4A45-A215-03B075A18EC7}
[02]: {5F7F6FFF-395D-480E-8450-64F385D82C5F}
[03]: {5E10E16E-CF65-479B-8E64-E240E621F5D2}
[04]: {B0D673F4-01F4-44E6-8944-4F22A558D042}
[05]: {F9852BE7-7A55-413C-BBD0-C9B72790E983}
[06]: {012BE245-FE02-466B-9835-209FE4FB7698}
[07]: {65AFCB03-3111-47E2-B1ED-198D6B0BC96E}
[08]: KB940518
[09]: KB938464
[10]: KB942288
[11]: KB948609
[12]: KB948610
[13]: KB949189
[14]: KB950050
[15]: KB950760
[16]: KB950762
[17]: KB950974
[18]: KB951066
[19]: KB951698
[20]: KB951978
[21]: KB952287
[22]: KB953733
[23]: KB954211
[24]: KB954459
[25]: KB955020
[26]: KB955069
[27]: KB955302
[28]: KB955839
[29]: KB956802
[30]: KB956841
[31]: KB957097
[32]: KB957200
[33]: KB957321
[34]: KB957388
[35]: KB958481
[36]: KB958483
[37]: KB958623
[38]: KB958624
[39]: KB958644
[40]: KB958687
[41]: KB959130
[42]: KB960715
[43]: KB961260
[44]: KB967190
Network Card(s): 4 NIC(s) Installed.
[01]: HP NC373T PCIe Multifunction Gig Server Adapter

Connection Name: Local Area Connection
DHCP Enabled: No
IP address(es)
[01]: **********
[02]: *****************
[02]: HP NC373T PCIe Multifunction Gig Server Adapter

Connection Name: Local Area Connection 2
DHCP Enabled: No
IP address(es)
[01]: **********
[02]: ********************
[03]: HP NC373i Multifunction Gigabit Server Adapter
Connection Name: Local Area Connection 3
Status: Media disconnected
[04]: HP NC373i Multifunction Gigabit Server Adapter
Connection Name: Local Area Connection 4
DHCP Enabled: No
IP address(es)
[01]: ***********

I blanked out some information for security but you should have every important information

NOTE : This is not a problem with SQL SERVER my problem is with MSDTC. I cannot use any msdtc ressource (either with sql server or without).

Regards
Gabriel Gb

Continue reading...