Share via

FAQ: How do I configure SQL Server to enable distributed transactions via Linked Server?

  • Article

Question

I need to configure my SQL Server instance for distributed transactions via linked server, however it looks that the default settings do not work.

Answer

 To enable distributed transactions across different servers, you need to configure MS DTC on both servers and some of your SQL Server server options. The following is a list you need to check:

  • DTC service is running
    please run "services.msc" from windows run dialog to check if it is running. If it is stopped, please start it by right-clicking on it and choose "Start".
  • Network DTC access is enabled
    if you are using Windows Server 2003, please refer to this link: https://support.microsoft.com/kb/817064
    if you are using Windows Vista or higher version of Windows, please refer to this link: https://technet.microsoft.com/en-us/library/cc753510%28WS.10%29.aspx
  • DTC service is configured to be allowed to access network in firewall
    in the linking server, please make sure this service could access the network and could receive connection from other server. In the linked server, please make sure this service could receive connection from other server.
  • XACT_ABORT is enabled
    we could enable XACT_ABORT with the following statement:
    SET XACT_ABORT ON
  • "Ad Hoc Distributed Queries" is enabled
    please execute the status of "Ad Hoc Distributed Queries":
    sp_configure 'show advanced options', 1
    reconfigure
    GO
    sp_configure 'Ad Hoc Distributed Queries'
    if the value under run_value is 0, please execute the following script to enable it:
    sp_configure 'show advanced options', 1
    reconfigure
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure