How to enable Network DTC Access and test Distributed Transactions in SQL Server

Here is an example of a Distributed Transaction in SQL Server that you can use to test if Distributed Transactions work on your Linked server:

BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM [remote_server_name].master.sys.sysprocesses
COMMIT TRANSACTION

If you receive an error similar to this:

OLE DB provider "SQLNCLI10" for linked server "server_name" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "server_name" was unable to begin a distributed transaction.

...you may need to enable network MSDTC access on your Windows Servers:

Choose Administrative Tools from Start menu -> Component Services -> Expand 'Component Services' -> Expand 'Computers' -> Expand 'My Computer' -> Expand 'Distributed Transaction Coordinator' -> Right Click 'Local DTC' and choose 'Properties':

dtc_properties_window

In the 'Security' tab check 'Network DTC Access', 'Allow Remote Clients', 'Allow inbound' or/and 'Allow outbound' (as needed), check 'Mutual Authentication Required', 'Incoming Caller Authentication Required' or 'No Authentication Required' (depending on your needs):

dtc_properties_security_tab

After making the changes test again if Distributed Transactions work on your Linked server:

BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM [remote_server_name].master.sys.sysprocesses
COMMIT TRANSACTION

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">