Unable to begin a distributed transaction

Question

I m trying to run SQL against a linked server  but I get the errors below   BEGIN DISTRIBUTED TRANSACTION SELECT TOP 1   FROM Sessions   OLE DB provider  quot SQLNCLI quot  for linked server  quot ASILIVE quot  returned message  quot No transaction is active  quot    Msg 7391  Level 16  State 2  Line 3 The operation could not be performed because OLE DB provider  quot SQLNCLI quot  for linked server  quot ASILIVE quot  was unable to begin a distributed transaction   There are two errors returned by the provider  Error  1  Number   80040E14 Source  Microsoft OLE DB Provider for SQL Server Description  OLE DB provider  quot SQLNCLI quot  for linked server  quot ASILIVE quot  returned message  quot No transaction is active  quot   HelpFile   HelpContext   00000000 SQLState  01000 NativeError  7412  Error  2 Number   80040E14 Source  Microsoft OLE DB Provider for SQL Server Description  The operation could not be performed because OLE DB provider  quot SQLNCLI quot  for linked server  quot ASILIVE quot  was unable to begin a distributed transaction  HelpFile   HelpContext   00000000 SQLState  42000 NativeError  7391  How do I get Microsoft to favor functionality over security  Or  at least  how can I get two SQL Severs to talk to each other  Related questions  The operation could not be performed because OLE DB provider    SQLNCLI10         linked server name is  null   Distributed transaction error   using Oracle provider  Unable to enlist in a distributed transaction with NHibernate  using Hibernate  Error using distributed transaction in SQL Server 2008 R2  SQL Server 2008 R2  no answer  Distributed Transaction Error Only Through Code  caused by connection pooling  Error performing distributed transaction coordinator in linked server  SQL Server 2008  no answer  Distributed transaction error   no accepted answer  only answer doesn t help  How to Insert into remote table using Linked server withint Transaction   accepted answer doesn t resolve    What I have done is irrelevant  but I ll post it anyway   Ensure Distributed Transaction Coordinator service is running on both machies     Disable all MSDTC security on both machines     Turn on random options on the linked server      Cursed and swore   Smashed things   Checked that a SELECT can use the linked server      SELECT   FROM ASILive CustomerManagementSystem dbo Users                763 row s  affected    Checked that client server can ping the remote server       C  Documents and Settings avatar gt ping asicmstest contoso com       Pinging asicmstest contoso com  10 0 0 40  with 32 bytes of data        Reply from 10 0 0 40  bytes 32 time lt 1ms TTL 128      Reply from 10 0 0 40  bytes 32 time lt 1ms TTL 128      Reply from 10 0 0 40  bytes 32 time lt 1ms TTL 128      Reply from 10 0 0 40  bytes 32 time lt 1ms TTL 128       Ping statistics for 10 0 0 40           Packets  Sent   4  Received   4  Lost   0  0  loss        Approximate round trip times in milli-seconds           Minimum   0ms  Maximum   0ms  Average   0ms   Checked that the remote server can commnicate back  by name  to the initiating server       C  Documents and Settings avatar gt ping asitestserver contoso com       Pinging asitestserver contoso com  10 0 0 22  with 32 bytes of data        Reply from 10 0 0 22  bytes 32 time lt 1ms TTL 128      Reply from 10 0 0 22  bytes 32 time lt 1ms TTL 128      Reply from 10 0 0 22  bytes 32 time lt 1ms TTL 128      Reply from 10 0 0 22  bytes 32 time lt 1ms TTL 128       Ping statistics for 10 0 0 22           Packets  Sent   4  Received   4  Lost   0  0  loss        Approximate round trip times in milli-seconds           Minimum   0ms  Maximum   0ms  Average   0ms   Checked that   SERVERNAME matches the server name on both servers     SELECT   SERVERNAME  SERVERPROPERTY  MachineName       -------------  -------------    ASITESTSERVER  ASITESTSERVER  and    SELECT   SERVERNAME  SERVERPROPERTY  MachineName       ----------  ----------    ASIGROBTEST  ASIGROBTEST   Screamed  Issued SET XACT ABORT ON before issuing my query  SET XACT ABORT ON GO BEGIN DISTRIBUTED TRANSACTION SELECT TOP 1   FROM Sessions   Granted Everyone Full Control to  HKEY LOCAL MACHINE Software Microsoft MSSQLServer  on both servers

User · Answer

I was able to resolve this issue  as others mentioned in comments  by disabling  quot Enable Promotion of Distributed Transactions for RPC quot   i e  setting it to False    As requested by  WonderWorker  you can do this via SQL script  EXEC master dbo sp serveroption       server   N  mylinkedserver          optname   N remote proc transaction promotion         optvalue   N false

User · Answer

Apart from the security settings  I had to open some ports on both servers for the transaction to run  I had to open port 59640 but according to the following suggestion  port 135 has to be open  http   support microsoft com kb 839279

User · Answer

My last adventure with MSDTC and this error today turned out to be a DNS issue   You re on the right track asking if the machines are on the same domain  EBarr   Terrific list for this issue  by the way     My situation   I needed a server in a child domain to be able to run distributed transactions against a server in the parent domain through a firewall   I ve used linked servers quite a bit over the years  so I had all the usual settings in SQL for a linked server and in MSDTC that Ian documented so nicely above   I set up MSDTC with a range of TCP ports  5000-5200  to use on both servers  and arranged for a firewall hole between the boxes for ports 1433 and 5000-5200   That should have worked   The linked server tested OK and I could query the remote SQL server via the linked server nicely  but I couldn t get it to allow a distributed transaction   I could even see a connection on the QA server from the DEV server  but something wasn t making the trip back    I could PING the DEV server from QA using a FQDN like    PING DEVSQL dev domain com  I could not PING the DEV server with just the machine name    PING DEVSQL  The DEVSQL server was supposed to be a member of both domains  but the name wasn t resolving in the parent domain s DNS    something had happened to the machine account for DEVSQL in the parent domain   Once we added DEVSQL to the DNS for the parent domain  and  PING DEVSQL  worked from the remote QA server  this issue was resolved for us    I hope this helps

User · Answer

For me  it relate to Firewall setting  Go to your firewall setting  allow DTC Service and it worked

User · Answer

OK  so services are started  there is an ethernet path between them  name resolution works  linked servers work  and you disabled transaction authentication     My gut says firewall issue  but a few things come to mind      Are the machines in the same domain   yeah  shouldn t matter with disabled authentication  Are firewalls running on the the machines   DTC can be a bit of pain for firewalls as it uses a range of ports  see http   support microsoft com kb 306843  For the time being  I would disable firewalls for the sake of identifying the problem What does DTC ping say   http   www microsoft com download en details aspx id 2868 What account is the SQL Service running as

User · Answer

I was getting the same error and i managed to solve it by configuring the MSDTC properly on the source server to allow outbound and allowed the DTC through the windows firewall   Allow the Distributed Transaction Coordinator  tick domain   private and public options

User · Answer

Found it  MSDTC on the remote server was a clone of the local server    From the Windows Application Events Log      Event Type    Error   Event Source  MSDTC   Event Category    CM   Event ID  4101   Date  9 19 2011   Time      1 32 59 PM   User      N A   Computer  ASITESTSERVER   Description        The local MS DTC detected that   the MS DTC on ASICMSTEST 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       For more information  see Help and Support Center at   http   go microsoft com fwlink events asp    Running  msdtc -uninstall msdtc -install   and then stopping and restarting SQL Server service fixed it

User · Answer

If the servers are clustered and there is a clustered DTC you have to disable security on the clustered DTC not the local DTC

User · Answer

If your Destination server is on another cloud or data-center then need to add host-entry of MSDTC service Destination Server  in your source server   Try this one if problem doesn t resolved  After enable the MSDTC settings

[sql-server] Unable to begin a distributed transaction

Examples related to sql-server

Examples related to sql-server-2005

Examples related to msdtc

Examples related to distributed-transactions