Today I had to set up the WCF-SQL adapter, to write data to a database used for BI purposes. We have our BizTalk machine located at a hoster, the BI database is located on a server on-premise at the client, and a VPN connection connects the servers. The servers are in different domains.
Generating the schemas went without problems, however once we wanted to do some actual work on the database we got the following 2 errors in the event log:
MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system BIBIZ.
The adapter failed to transmit message going to send port “Communicate with BI” with URL “mssql://bibiz.acme.nl//bibizDatabase?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.Transactions.TransactionManagerCommunicationException: Communication with the underlying transaction manager has failed. —> System.Runtime.InteropServices.COMException: The MSDTC transaction manager was unable to push the transaction to the destination transaction manager due to communication problems. Possible causes are: a firewall is present and it doesn’t have an exception for the MSDTC process, the two machines cannot find each other by their NetBIOS names, or the support for network transactions is not enabled for one of the two transaction managers. (Exception from HRESULT: 0x8004D02A)
It turned out, that to use MSDTC across domains you have to set some options. I will list the actions to be taken here, both for my own reference, and to help anyone else running into this problem.– Since MSDTC looks up the computer by NetBIOS, we had to add the computers to their lmhosts files. So in the file on the BizTalk server we had to add the BI database server, and the other way around. The lmhosts file can be found at C:\Windows\System32\drivers\etc, and should be created if it does not exist yet. Add an entry like this (this is an example on the BizTalk server):
10.1.20.20 bibiz #PRE
To reload the new entry in the lmhosts file give the following command on a command prompt:
– Next, we have to enable NetBIOS over TCP/IP. To do this, we went to Network Connections in the control panel, open the properties for our VPN, open the TCP/IPv4 properties, and click the Advanced button. Now go to the WINS tab, and under NetBIOS setting, choose Enable NetBIOS over TCP/IP.
– Next we had to open up the ports in both the hardware firewall (at the client and at the hoster). The ports that should be opened are:
- RPC: 135 (TCP) bidirectional
- Dynamic RPC: 49152 – 65535 (TCP) bidirectional
– Also, if you have turned on the Windows firewall on your server(s), this should be opened up for these ports as well. Open a administrator command prompt, and use the following command:
"%SYSTEMROOT%\system32\netsh.exe" advfirewall firewall add rule name="Dynamic RPC" dir=in action=allow localport=135,49152-65535 protocol=tcp
– Finally, we have to turn of DTC authentication, as this is not possible cross domain. Open up Component Services (dcomcnfg.exe) and open up the properties for you Local DTC.
Here, go to the security tab, and set the options as follows. Note we have selected No Authentication Required.
After we had set our servers up this way, everything now works, and we can use the WCF-SQL adapter to work on the items in the BI database.