i'm trying to run SQL against a linked server, but i get the errors.
BEGIN DISTRIBUTED TRANSACTION
SELECT TOP 1 * FROM Sessions
OLE DB provider "SQLNCLI" for linked server "ASILIVE" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ASILIVE" 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 "SQLNCLI" for linked server "ASILIVE" returned message "No transaction is active.".
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 "SQLNCLI" for linked server "ASILIVE" 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 the very 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>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<1ms TTL=128 Reply from 10.0.0.40: bytes=32 time<1ms TTL=128 Reply from 10.0.0.40: bytes=32 time<1ms TTL=128 Reply from 10.0.0.40: bytes=32 time<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>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<1ms TTL=128 Reply from 10.0.0.22: bytes=32 time<1ms TTL=128 Reply from 10.0.0.22: bytes=32 time<1ms TTL=128 Reply from 10.0.0.22: bytes=32 time<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.
If the servers are clustered and there is a clustered DTC you have to disable security on the clustered DTC not the local DTC.
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...
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.
Found it, MSDTC on the remote server was a clone of the local server.
From the Windows Application Events Log:
Running
and then stopping and restarting SQL Server service fixed it.
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
For me, it relate to Firewall setting. Go to your firewall setting, allow DTC Service and it worked.