When installing standalone data warehouse MS SQL database (deployment scenario First server installation with external data warehouse database) the Distributed Transaction Coordinator (MSDTC) has to be properly configured between both communicating databases - production database and data warehouse database.

MSDTC on virtual machines

When running databases on virtual machines, the MSDTC may still not work with the following error message (when executing any transnational procedure that uses server link)

OLE DB provider “SQLNCLI” for linked server “XXX” returned message “No transaction is active."

This is most likelly caused by the fact, that virtual machines are created through cloning, resulting in MSDTC on all VM's having the same unique identity, thus preventing the proper communication. To fix this issue, MSDTC needs to be uninstalled and installed again on both servers, following these steps:

  1. On the server hosting the production database. open the cmd console window
  2. Type and execute the below 3 commands
    net stop msdtc
    msdtc -uninstall
    msdtc -install
  3. MSDTC configuration will be most likely lost, follow the steps above
  4. Restart the server
  5. Repeat the same for the server hosting data warehouse database

  1. On the server with production database open Component Services (C:\Windows\System32\msdtc.exe)
  2. Navigate to Computers > My Computer > Distributed Transaction Coordinator > Local DTC
  3. Go to Local DTC > Properties > tab Security and change the configuration as follows:
    • Check options Network DTC Access, Allow Remote Clients, Allow Remote Administration, Allow Inbound, Allow Outbound, Enable XA Transactions
    • Select No Authentication Required option

  4. Restart the MSDTC service explicitly in Services
  5. Restart the MS SQL database server
  6. Repeat steps 1. - 5. on the server with data warehouse database

When the setup of the Distributed Transaction Coordinator is changed, a popup windows appears saying that the service will be restarted. Confirm this restart, but make sure to still restart the MSDTC service at the end as said in step 4.

For more information about MSDTC configuration eg. firewall settings and configuration options, please refer to Troubleshooting Problems with MSDTC.


To allow connections from another server, check that inbound connections are allowed on both database servers.

Check option: Microsoft SQL Server Management Studio > database name > Properties > Connections > Allow remote connection to this server