GAPTHEGURU

Geek with special skills

MSDTC Troubleshooting

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.

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.
  • 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.”

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

2 Comments »

  1. i love a parade

    Comment by Stacie | 05/05/2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: