Troubleshooting MSDTC in SQL Servers between W2003 and W2000

  • Hello guys:

    I have a SQL Server Enterprise Edition SP4 in a Server Windows 2003 SP1 and I have problems with MSDTC when doing distributed transactions with another server Windows 2000 SP4 with SQL Server 2000 SP3a.

    Exactly the error is:

    Server: message 7391, level 16, state 1, procedure TESTDTC_P, line 23

    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].

    What attempt to execute is a very simple test:

    begin tran

    UPDATE [LINKEDSERVER].DB.dbo.TESTDTC

    SET VALUE = 1

    SELECT @@rowcount as [rows_affected]

    commit tran

    I have verified that MSDTC is active in both servers

    both are the same domain,

    I have followed the steps of MS article 817064 (How to inable network DTC Access in Windows Server 2003),

    None of these servers is in cluster,

    dtcping works but dtctest no.

    I have followed article 329332 steps (You receive error 7391 when you run to distributed transaction against to linked server) without success and whenever I have test something I have restarted the machine.

    I have changed the key of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\TurnOffRpcSecurity registry to 1

    somebody can give me a help?

    It is possible that this is related to NT AUTHORITY\NetworkService?. This account is the logon for DTC service in W2003, but doesn’t work in the same service in W2000.

    Very many thanks, or I am very clumsy or the one that solves this to me is a “crack”.

  • Do you have same issue between two Windows 2000 or 2003 servers?

    Did you remove "BUILTIN\Administrators" group from both servers? Try to add it back to see what is going to happen, This may help to "NT AUTHORITY\NetworkService".

  • thanks, but this is not the problem, BUILTIN\Adminsitrators are included in both server as System Administrators

  • Try to grant it to access SQL Server with 'sa' right by running sp_grantlogin 'NT AUTHORITY\NetworkService' and sp_addsrvrolemember. 

  • Hi all,

    I do have the SAME problem. I almost posted it here a couple of days ago and then I found out that out of 3 Windows 2003 servers 2 servers do have this problem and one does not. All 3 Windows 2003 servers are Windows NT 5.2 Buid 3790

    Short Description:

    1. an SQL script connects to a number of servers using OPENROWSET to query backup completion with read-only credentials. This script completes with no errors connecting to both Windows 2000 and Windows 2003 servers

    2. I added just one line to the above script: to insert the result in a local temp table. Local means local to an admin server that is running the script, not to remote server. Now the script is connecting fine to Windows 2000 servers but gives an error message abount not able to be enlisted in the specified transaction coordinator when connecting to 2 of 3 of my Windows 2003 servers

    I am looking into it too. One of the differences that on Windows 2003 server that is connecting fine IIS is NOT installed, but it is installed on other 2 servers where we have this MSDTC error message. SQL Server version does not matter, it is Microsoft SQL Server  2000 - 8.00.760 on one that we can connect and one that we can not and Microsoft SQL Server  2000 - 8.00.859 on one that we can not too.

    Yelena

    Regards,Yelena Varsha

  • Since you mentioned DTCtest was failed, You may try this.

    Verify Communication Between Two or More Computers

    When a transactional COM component on one system accesses a SQL Server database on another system, MS DTC propagates the MS DTC transaction from the first or "primary" system to the secondary system. Transaction propagation can only occur if the MS DTC process on the primary system can communicate with the MS DTC process on the secondary system. Actually, the MS DTC processes establish RPC connections in both directions, so a network configuration problem on either system can prevent the MS DTC processes from successfully communicating with one another. This will cause a "Failed to enlist error" to be returned by ODBC.

    If a communication error occurs, the following MS DTC event may be recorded in the Windows 2000 Application event log.

    MS DTC is unable to communicate with MS DTC on a remote system. MS DTC on the primary system established an RPC binding with MS DTC on the secondary system. However, the secondary system did not create the reverse RPC binding to the primary MS DTC system before the timeout period expired. Please ensure that there is network connectivity between the two systems.

    This error indicates that MS DTC on your system was able to bind to MS DTC on your SQL Server system, but MS DTC on the SQL Server system could not perform the reverse bind to MS DTC on your system.

    If your network configuration is incorrect, every attempt to connect to the remote SQL Server database will result in a "Failed to enlist" error. If you consistently get a "Failed to enlist" error even after MS DTC has been started on both systems, the most likely cause of the failure is a network configuration error.

        To verify network communication between two computers

    1. Use Notepad to create a file called Pingtest.bat, and then paste the following batch file commands in it.
      echo offREM Usage pingtest OtherMachineNameREM Must use a machine name and NOT an IP addressping -n 1 %computername%ping -n 1 %1ipconfig /allecho on

      The batch file uses %computername% to display the local IP address. It then displays the IP address of the remote machine you specify in the command line when executing the batch file. When you invoke the batch file, you must specify the name of the remote machine and not its IP address. Using the machine name forces Ping to resolve the machine name exactly as MS DTC does when it binds to the remote MS DTC machine.

    2. Run Pingtest.bat on your primary system to determine if you can "ping" your secondary system by name. Capture the output from the batch file in a text file. For example, if your primary system is machine "A" and your secondary system is machine "B," do the following:
      Pingtest B > AResults.txt 

    3. Run Pingtest.bat on your secondary system to determine if you can "ping" your primary system by name. Capture the output from the batch file in a text file. For example, if your primary system is machine "A" and your secondary system is machine "B," do the following:
      Pingtest A > BResults.txt 

    4. Examine the two resulting text files to ensure that the two machines can ping one another successfully.

    If you use Hosts or Lmhosts files for TCP/IP name resolution, check these files very carefully to ensure that they contain valid computer name-to-IP address mappings. To do this, check the "Hosts" and the "Lmhosts" files in the %windir%>system32\drivers\etc\ directory on both the Component Services system and the SQL Server system. Frequently, the file on one system will be correct, while that on the other system is incorrect.

    MS DTC runs under the identity of the System account by default. The MS DTC on one computer can communicate with the MS DTC on another computer only if the Guest accounts on both computers are enabled. If you disable the Guest account on either machine or in the Windows 2000 domain to which they belong, you must configure MS DTC to run under some other user name that can communicate with the remote computer.

    http://www.microsoft.com/windows2000/en/advanced/help/default.asp?url=/windows2000/en/advanced/help/addtctsk_7e9f.htm

  • Hi,

    I found the weird thing: I am getting MSDTC error connecting to LOCAL computer!

    I am running the script from Windows 2003 server.

    I just switched the originator server to another 2003 machine and found out that I am getting MSDTS error message about originator server!

    If I am running the script from Server1 then MSDTC can not start transaction with Server1. but can with Server2.  If I am running a script from Server2 MSDTC can not start transaction with Server2. but can with Server1.

    Is not it the time to look at protocols?

    Regards,Yelena Varsha

  • Hi,

    I´ve Had the same problem with my Windows 2003 SQL. Why don´t you take a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;329332.

    As Microsoft says "...The problem occurs because Microsoft Distributed Transaction Coordinator (MS DTC) is not configured for network access. By default, the network access settings of MS DTC are disabled on new installations of SQL Server 2000 on computers running Windows Server 2003, Enterprise Edition. "

    []´s


    Anderson Ferreira

  • Thanks, Anderson, but this is one of the first operations that I have do. You can read this at the first message of the thread.

    More ideas:

    This test doesn't work:

        --(TEST1)

        begin tran

        UPDATE [LINKEDSERVER].DB.dbo.TESTDTC

        SET VALUE = 1

        commit tran

    But this one works fine:

        --(TEST2)

        UPDATE [LINKEDSERVER].DB.dbo.TESTDTC

        SET VALUE = 1

    Can we say that TEST2 code is a "distributed transaction"?

    Can I need in a future write code like TEST1?

  • Hi,

    This article did not help so far. One of the machines did not have this checkbox checked , I checked it, the same error. And in some cases I am not going through the network. I can not use MS DTC on the same computer. If I am running a query on server1 I have an error about server1 but it fine with server2. If I am on server2, I am getting a message about server2 but it is fine with server1. And I am only selecting from the servers and I am updating only the temptable local to the server where the query runs.

    Yelena

    Regards,Yelena Varsha

  • I had the same issue; transaction using Linked server returning:

    [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].

    Servers are all Windows 2003 sp1.  My correction was to change the MSDTC security settings to use ' Incoming Caller Authentication Required'.  I have had no issues since the change.

  • Hi,

    I do have the same issue when using Linked Server too in addition to my Openrowset query.

    Could you post here how did you change those settings? So far I found the article

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/eb45c4ef-73c7-4941-abfb-0ce1adcdfb80.asp

    thas says that Win2003 SP1 or Win XP SP2 required, that we did not have yet

    Platforms: Windows XP Service Pack 2, Windows Server 2003 Service Pack 1

    Thanks,

    Yelena

    Regards,Yelena Varsha

  • I finally installed SP1 for Windowes 2003 and checked the "Incoming Caller Authentication Required' that I can see now. It resulted in more MSDTC error messages like described when running distributed queries towards Windows 2000 servers. Previously 2000 servers were fine, it was only queries towards 2003 servers that had errors. I even set this settings to No Authentication Required, no help.

    I assume that everything works fine for Don because ALL of his servers are running the same version of OS/SP/SQL

    Yesterday I installed CTP evaluation version for SQL 2005 on Windows XP workstation and my query is able to connect from it to ALL servers: Windows 2000, Windows 2003 and Windows 2003 SP1 without any MSDTC errors.

    It is obviously a version compatibility issue, and somewhat configuration issue, not programming or admin's experience

    Yelena

    Regards,Yelena Varsha

  • Success finally!!!

    Set the register keys like says in http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2otech.mspx#ECAA part "No Authentication Required".

    Works between Servers 2003 and between 2003 and 2000

    You then only need execute ths scripts with "set XACT_ABORT ON".

    Thanks to all...

  • FYI:

    To get one of my Windows 2003 Server OEM with SQL 2000 SP3a to work using MSDTC; I had to re-install MSDTC.

    I used http://support.microsoft.com/default.aspx?scid=kb;en-us;891801

    AND http://support.microsoft.com/kb/279786/ as guidelines.

    I Don't suggest doing the above without complete backups and I only did it on a non-clustered server. DO AT YOUR OWN RISK; ONLY after trying everything else.

    Esp. make sure you tried TurnOffRpcSecurity = 1 from http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2otech.mspx

    Tim S

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply