Q: Has anyone managed to get MSDTC transactions working between a local machine and a database that resides on an Azure Virtual Machine?
I read this: which suggests MSDTC is not supported on 'SQL Azure' - but is SQL Azure the 'SQL Databases' option, not the 'Virtual Machines' option which may happen to have a SQL database on there? It is unclear: http://social.msdn.microsoft.com/Forums/windowsazure/en-US/894b6087-35ab-4f4f-aa19-f81ee56dc6bf/msdtc-with-the-new-virtual-machines?forum=WAVirtualMachinesforWindows
I found the following article: http://sanganakauthority.blogspot.co.uk/2014/02/definitive-steps-to-configure-msdtc-on.html - but this is related to Azure VMs on the same virtual network.
There are enough articles out there which give me hope that IT IS possible, as they seem to imply it. E.g. http://thoughtsofmarcus.blogspot.co.uk/2013/03/msdtc-error-after-cloning-virtual.html, http://www.jamescrowley.co.uk/2012/06/23/msdtc-gotchas-with-virtual-machines/
I have tried the following, from reviewing numerous articles:
- Checked MSDTC service is Started, and set to Automatic.
- Checked Local DTC settings in Component Services on both machines: http://technet.microsoft.com/en-us/library/cc731495.aspx
- Restricted the port range 5000-5050 for MSDTC in Component Services: http://support.microsoft.com/kb/300083.
- Set/verified the registry entries for the above as described in this article: http://support.microsoft.com/kb/306843
- Added inbound and outbound rules for port 135 and a range of 5000-5050 on the firewall on both machines (and tried disabling the firewall completely)
- Uninstalled/reinstalled MSDTC on the Azure VM, checking the CID is unique: http://msdn.microsoft.com/en-us/library/aa561924.aspx
- Added endpoints to the Azure VM in the management portal, 135 and 5000-5050, using this helpful article: http://fabriccontroller.net/blog/posts/adding-multiple-endpoints-to-your-windows-azure-virtual-machines-by-using-a-csv-file/
- Added host names to the hosts file (even though ping doesn't work on Azure anyway). http://blogs.msdn.com/b/distributedservices/archive/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool.aspx
- Tried to add ICMP to the firewall to let me ping the Azure VM but this didn't work, didn't expect it to though... (http://feedback.windowsazure.com/forums/217313-networking-dns-traffic-manager-vpn-vnet/suggestions/3346609-icmp-support-for-azure-websites-roles-cloud-serv).
- Ran dtctester.exe, output was an error:
Error: SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cursor state. - Attempted to run dtcping.exe, received the message
RPC pinging exception -->1753(There are no more endpoints available from the endpoint mapper.)- the ports and the firewall settings should be fine. - Set/verified the
TurnOffRpcSecurityregistry entry (clutching at straws now): http://support.microsoft.com/kb/839187
Any ideas?
SOLUTION
Initial solution was to set up a Cloud Service with the Web Site on it connecting to the database on a separate Virtual Machine, both residing on the same Azure Virtual Network. You have to add the Virtual Network first before you create the Virtual Machines.
Then I was getting annoyed by the Cloud Service resetting itself, I had originally published it from Visual Studio but wanted to tweak it and those tweaks were being undone. So then I switched it to a Virtual Machine (so 2 VMs in total).
Then I was breaking my subscription credit limit, so I ended up with 2 Virtual Machines, one with Web Site, one with Database, both on separate Azure subscriptions on separate Virtual Networks, but with a Virtual Gateway between them. This is a bit convoluted but works perfectly, this guide is brilliant for that: http://blogs.technet.com/b/aviraj/archive/2014/05/16/microsoft-azure-configure-cross-subscription-vnet-to-vnet-connectivity-in-azure.aspx
