MS DTC Settings for Disturbuted Transaction

MSDTC Troubleshooting

Fri, 01/11/2008 - 17:03 |  sqlguychuck

Average: Select ratingPoorOkayGoodGreatAwesome

Poor

Okay

Good

Great

Awesome

Your rating: None Average: 4.6 (31 votes)

I often run into MSDTC issues, so I have created this post to accumulate all the useful help I have found on the subject as it pertains to SQL Server users.
If you are a COM Developer, and a COM+ object attempts to update a Microsoft SQL Server database on a remote computer by using an MS DTC transaction, the transaction fails if network transactions are disabled. Just simply install it via Windows Add Remove Programs | Windows System Components | Application Server | Enable network DTC access check box. Microsoft also has an older testing tool for application to SQL testing called dtctester.exe - download is on Microsoft site.
The SQLCAT team has some great performance tuning guidelines for App to DB MSDTC transactions. A must read!
MS DTCPing.exe tool can be run on both servers at same time. This article helped me solve issues: http://support.microsoft.com/kb/306843/en-us The key issues to check for are:

• Do you have netbios or dns resolution from each box? e.g. can box1 ping box2 and vice versa? If not, just add a HOSTS entry for quick name resolution.
• Does your linked server connection properties | Server Options have RPC set to true?
• Enable MS DTC over network: In Start Run, enter dcomcnfg, or in Admin Tools Start menu, Component Services, MSDTC security configuration dialog from component services, expand component services until you see My Computer, right click and select properties | MSDTC tab | Security Configuration button | select Network DTC Access check box | select Allow Remote Clients | select Allow inbound/outbound (based on your needs) | change "Mutual Authentication Required" to "Incoming Caller Authentication Required" or "No Authentication Required." NOTE: Windows 2003 SP2 install will change Authentication to Mutual and most likely break your MSDTC connections. Security ramifications noted in this technet article. Leave DTC Logon as NT Authority\NetworkService.
• If above still not working, check registry to make sure MSDTC security is turned off: Under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC, add dword value key TurnOffRpcSecurity. Assign its value to 1.
• Is firewall blocking port 135 or higher level ports? Don't know? See ms kb (link at top of page also) for forcing specific ports to be used between the two servers and using DTCPing to test (see below for lessons learned from DTCPing).
• For cluster installations, you need to follow this MS KB: http://support.microsoft.com/kb/301600 Make sure your DTC name is resolvable in DNS! Also general cluster troubleshooting document has MSDTC section: http://msdn.microsoft.com/en-us/library/aa949696(BTS.10).aspx
• Look for EventID 4101 - The local MS DTC detected that the MS DTC on DB02 has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem. Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information. Note: In Windows 2008 use Administrator command prompt, service must be stopped and you must reboot the server.
• MS Technet article on this subject for Windows 2008 and Vista.

Quick tests in Query Analyzer:
begin distributed tran
select * from [remoteservername].master.sys.sysprocesses
commit tran
Side Note: You don't need the SET xact_abort ON and DBCC TRACEON (3604, 7300) settings if running against SQL server, just other providers.
Errors you may encounter:

• OLE DB provider "SQLNCLI" for linked server "analytics" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "analytics" was unable to begin a distributed transaction.
  • Solve this issue with Name resolution Step 1. above.

• OLE DB provider "SQLNCLI" for linked server "analytics" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
• Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "analytics" was unable to begin a distributed transaction.
• "The partner transaction manager has disabled its support for remote/network transactions Exception from HRESULT: 0x8004D025".
• " Executed as user: MICROSOFT\sqlservice. The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction. [SQLSTATE 42000] (Error 1206)  OLE DB provider "SQLNCLI" for linked server "Analytics" returned message "No transaction is active.". [SQLSTATE 01000] (Error 7412).  The step failed."
  • Solve these issues in the MSDTC security configuration noted in Step 3. above.

I have recently seen this on SQL 2005 sp2 windows 2003 sp2 servers: (Was solved with above methods.)

OLE DB provider "SQLNCLI" for linked server "blvdbstg" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 1

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "blvdbstg" was unable to begin a distributed transaction.

The transaction active in this session has been committed or aborted by another session.

Location:              wrkspace.cpp:440

Expression:         !PSess ()->FHasWorkspaceRef ()

SPID:                     69

Process ID:          1908

 

Msg 3985, Level 16, State 2, Line 1

An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.

 

Msg 3624, Level 20, State 1, Line 1

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

 

DTCPing log report snippets for issues found and how I resolved them:

• RPC pinging exception (There are no more endpoints available from the endpoint mapper.)

This means RPC ports are being blocked by a firewall. See MS KB306843 article for help in narrowing down the port range and you will have to figure out how to get firewall rules updated.

• Remote host name can only be NetBIOS name

Linked servers with fully qualifed names (FQDN) don't work with DTC (most likely in environments with no WINS resolution), change to use netbios name.

Error message when you run the Distribution Agent in SQL Server 2008: "The distribution agent failed to create temporary files"
The distribution agent failed to create temporary files in C:\Program Files\Microsoft SQL Server\100\COM directory. System returned errorcode 5.
Give permission full control for the SQL Service Account to the above folder. http://support.microsoft.com/kb/956032