DTC problem with SQL 2005 on Server 2003

  • I'm getting a strange error trying to run a distributed transaction between 2

    SQL 2005 machines.

    The error reads

    'OLE DB provider "SQLNCLI" for linked server "RemoteServer" 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 "RemoteServer" was unable to begin a distributed transaction"

    The query that I'm trying to run is very simple. The table A exists on both

    machines

    BEGIN TRANSACTION

     INSERT INTO TestDB.dbo.A (B) VALUES ('Test Local')

     INSERT INTO RemoteServer.TestDB.dbo.A (B) VALUES ('Test Remote')

    COMMIT TRANSACTION

    DTC is running on both machines and I've configured it to allow network

    access, remote clients, remote administration, Inbound and Outbound

    communications (Component Services->My Computer->DTC)

    The linked server uses a specific security context and allows data access,

    rpc and rpc out.

    Both the inserts run fine by themselves. It's only when run together in a

    transaction that they break.

    Has anyone got a solution, or suggestion please?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Not sure about 2005, but I had two 2003 servers in a workgroup and there was some registry entry I had to make to allow DTC to function across them. I had a different error, something about unable to begin a distributed transaction.

    It was similar to this: http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql, but I know I made a registry change.

  • The registry change Steve mentioned is to disable security for RPC. If MSDTC is configured properly, you do not need to do this. But sometimes it just does not work regarless how you configure the MSDTC. Then this registry change is the last straw.

    Under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC, add dword value key TurnOffRpcSecurity. Assign its value to 1.

    Another thing is for distributed transactions in SQL server, you must turn on XACT_ABORT and ANSI_NULLS 

    SET XACT_ABORT ON (run time)

    SET ANSI_NULLS ON (parse time)

     

  • Turning off the rpc security didn't help. I was getting the error about not been able to enlist in the specified transaction coordinator until I made some changes in the DTC setup.

    I tried XACT_ABORT and ANSI NULL and neither helped.

    I googled this and didn't find anything useful. Think I'm ging to escalate it to PSS.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I am experiencing this as well referencing a stored procedure on a linked server from a trigger. Unfortunately, the suggestions here didn't help me either. Were you ever able to resolve the issue?

  • Is MS DTC configured to run under Network Service on both servers?

    K. Brian Kelley
    @kbriankelley

  • Actually, I found that in the MSDTC security configuration dialog from the component services, if I set the "Transaction Manager Communication" authentication from "Mutual Authentication Required" to "Incoming Caller Authentication Required", it now works!

    In my case I have a W2K3 server with SQL2005 trying to called a proc on a W2K SQL2000 machine from within a trigger. I had been test the linked server for a while and all was working fine until I tried calling an SP from within a SQL 2005 trigger.

    I think this solves my issue. Thanks for the quick response though.

    -Chad

  • Hi,

    Same problem here!

    I'm using two SqlServer 2005 both running on Windows Server 2003 Standard Edition SP1.

    Both doesn't belongs to a Domain. They are configured in they own workgroup.

    Microsoft SQL Server Management Studio    9.00.2047.00

    Microsoft Analysis Services Client Tools  2005.090.2047.00

    Microsoft Data Access Components (MDAC)   2000.086.1830.00 (srv03_sp1_rtm.050324-1447)

    Microsoft MSXML                           2.6 3.0 5.0 6.0

    Microsoft Internet Explorer               6.0.3790.1830

    Microsoft .NET Framework                  2.0.50727.42

    Operating System                          5.2.3790

    MSDTC version                             5.2.3790.1830

    On both servers:

     - Service "Remote Procedure Call" is running using the account "NT AUTHORITY\NetworkService";

     - On Component Services -> Computer -> My Computer Properties -> tab MSDTC -> Security Configuration all box are checked. On the Transaction Manager Communication the chosen is "No Authentication is Required".

     - On Local Computer Policy -> Administrative Templates -> System -> Remote Procedure Call the "Restriction for Unauthenticated RPC clients" is enabled and the restriction to apply is "None".

     - Windows Firewall is disabled

     - Registry key is set to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\TurnOffRpcSecurity is set to "1"

    Schema on server [myServer2]:

    USE [myDB]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[myTable](

      [varchar](5) NOT NULL,

     [name] [varchar](10) NULL,

    PRIMARY KEY CLUSTERED

    (

      ASC

    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    SQLStatement run on server [myServer1]

    begin distributed transaction

    insert into [myServer2].myDB.dbo.myTable values ('ACD','LX')

    commit transaction

    The error message:

    OLE DB provider "SQLNCLI" for linked server "myServer2" 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 "myServer2" was unable to begin a distributed transaction.

    Some one can help me find the problem?

    Thank you in advance

    Joao

  • Thanks...that worked for me too!

  • It could relate to firewall

  • Chad Michael (1/5/2006)


    In my case I have a W2K3 server with SQL2005 trying to called a proc on a W2K SQL2000 machine from within a trigger. I had been test the linked server for a while and all was working fine until I tried calling an SP from within a SQL 2005 trigger.

    -Chad

    I have a similar problem. But setting the MS DTC like that did not help.

  • Yesterday I spent half a day looking for the solution of this problem. In my case this one helped: http://www.eggheadcafe.com/conversation.aspx?messageid=34113657&threadid=34091450

    This is for the case when You have two machines in separate vlans and different domains.

    And here is the solution:

    "After adding on each SQL-Server the opposite in c:\windows\system32\driver\etc\hosts

    connection can be established and remote update works."

  • I have the same exact issue as mentioned by Joao Salgueiro...can anyone suggest a quick fix...i am sure its a small tweak but not able to figure out the issue.

    Another question: Will it be a problem to run linked server between a 32bit and 64 bit sql server. Because regular inserts/deletes work fine but when run within a transaction they fail:

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

    Msg 7391, Level 16, State 2, Procedure CCC_List_ITRIG, Line 16

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

Viewing 13 posts - 1 through 13 (of 13 total)

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