Configuring MS SQL for Server link
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:
On the server hosting the production database. open the cmd console window
Type and execute the below 3 commands
net stop msdtc
msdtc -uninstall
msdtc -installMSDTC configuration will be most likely lost, follow the steps above
Restart the server
Repeat the same for the server hosting data warehouse database
MSDTC configuration step-by-step
On the server with production database open Component Services (C:\Windows\System32\msdtc.exe)
Navigate to Computers > My Computer > Distributed Transaction Coordinator > Local DTC
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
Restart the MSDTC service explicitly in Services
Restart the MS SQL database server
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.
MS SQL server configuration
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
