When distributed transactions go boing: Get Vista, XP, and SQL Server 2000 talking to each other

by Ted Jardine Wednesday, May 14, 2008 4:27 PM

From XP SP2 and on, all network communication coming out or getting to DTC (Distributed Transaction Coordinator) is disabled by default. For example, if a COM+ object attempts to update a SQL database on a remote computer using a DTC transaction, the transaction fails. Likewise, if your computer hosts a SQL database that components from remote computers try to access using a DTC transaction, their transactions fail. With Vista, it gets even more tightened down (see "Using MSDTC between Vista clients and Windows 2000 servers" for details).

On my development machine, I'm running Vista. As we still do the bulk of our work hooking into SQL Server 2000, I need to run that in a virtual machine; I haven't bothered making all these part of the same domain. Therefore, attempting to do some transactions (via System.Transaction.TransactionScope) makes everything go bonk if you're dealing with a setup like mine:

System.Transactions.TransactionException: The transaction has already been implicitly or explicitly committed or aborted.

And/Or:

The partner transaction manager has disabled its support for remote/network transactions.

And/Or:

MSDTC on server 'servername' is unavailable.

So here's how to make them all play nicely together so you can get back to getting productive work done:

(Caveat: this is for a development environment not production. Obviously, for production environments, you're going to have machines on the same domain, no sign of XP--or Vista for that matter--and can therefore tighten security up quite a bit...make that should tighten security up quite a lot)

  1. Enable MSDTC on Vista
    1. Run dcomcnfg (Component Services)
    2. Expand the "Component Services" node, then the "My Computer" node, then the "Distributed Transaction Coordinator" node
    3. Right-click on the "Local DTC" node and select "Properties"
    4. On the "Local DTC Properties", select the "Security" tab
    5. Select Network DTC Access, Allow Inbound, Allow Outbound, No Authentication Required, and Enable XA Transaction
      Note: reduced authentication security required because the machines are not on the same domain (in a production environment, they will be)
    6. Enable service auto start if you so wish:
      1. Run dcomcnfg again if necessary
      2. Locate Distributed Transaction Coordinator and right-click and select "Properties"
      3. In "General" tab, set startup type to "Automatic"
  2. Enable MSDTC on XP virtual machine
    1. Run dcomcnfg (Component Services)
    2. Expand the “Component Services” node, then the "My Computer" node
    3. Right-click on "My Computer" and select "Properties"
    4. Select "MSDTC" tab
    5. Click "Security Configuration"
    6. Check/Select "Network DTC Access", "Allow Remote Clients", "Allow Inbound", "Allow Outbound", "No Authentication Required", and "Enable XA Transactions"
    7. Okay your way out after the MSDTC service automatically restarts
    8. Enable service auto start if you so wish:
      1. Open SQL Server Service Manager (SQL Server 2000)
      2. Select Distributed Transaction Coordinator and check "Auto-start service when OS starts."
  3. Firewall
    1. On both machines you must add msdtc.exe to your list of exceptions (C:\Windows\System32\msdtc.exe)
    2. You might need to also open port 135. I didn't, but I've heard you might.
  4. Run your transaction code to ensure that it works. If not, reboot both machines.

I got the above from a bunch of various blogs, forum posts, and KB articles. Two that were the most helpful were:

Connect to SQL Server Instance on Virtual Machine from Host Machine Running Vista

by Ted Jardine Friday, July 27, 2007 5:26 PM

A couple months back when I first switched my primary developer machine to Vista, I wanted to connect to a SQL Server 2000 instance on my WinXP Pro virtual machine (Virtual PC 2007). It took a bit of digging to figure out how to a) even do it with WinXP to WinXP and then b) do it with Vista as the host OS.

Typically, it's an easy thing to set up your virtual machines to talk to each other, and also talk to the host machine, but to get the host machine to be able to talk to the virtual we need to go a little further by configuring a Microsoft Loopback Adapter. Using the Microsoft Loopback Adapter opens our Virtual Machine only to the host. To do this in Vista, do the following:

  1. Install a Loopback Adapter
    1. Open Control Panel
    2. Vista caveat: switch to Classic View to "Add Hardware"
    3. In the "Welcome to the Add Hardware Wizard", click Next.
    4. Select "Install the hardware that I manually select from a list (Advanced)" and click Next.
    5. Scroll down and select "Network adapters" and click Next.
    6. Select under Manufacturer "Microsoft" and then under Network Adapter "Microsoft Loopback Adapter" and click Next.
    7. Click Next and Finish.
  2. You can manually configure your IP Address for your adapter, but you can just let the loopback adapter get an auto configuration IP address from your router's DHCP.
  3. In your Virtual PC Console, choose settings for the applicable virtual machine, and then choose "Networking".
  4. Select the now available "Microsoft Loopback Adapter" (note that your virtual machine will need to be off, not in a saved state, in order to make this change).
  5. Turn on your virtual machine and once it's booted up, verify that the Microsoft Loopback Adapter has installed its routing in your host PC's route table by going to a command prompt in the virtual machine and typing "route print".
  6. In the case of a virtual machine running WinXP, make sure you've allowed file and print sharing in the virtual machine's firewall settings.
  7. Open a command prompt and run ipconfig to find out what your virtual machine's IP address is (note that as we have not configured the Microsoft Loopback Adapter to allow the virtual machine to connect to the outside network, your task bar network icon will warn you that you have limited connectivity – thus the need to run ipconfig from a command line to see if the virtual machine even has an IP address assigned).
  8. Check whether your host machine can now communicate with the client virtual machine by pinging it's IP address by opening a command prompt in Vista and typing "ping 169.254.166.12" where you enter your applicable IP address.
    c:\>ping 169.254.166.12
    Pinging 169.254.166.12 with 32 bytes of data:
    Reply from 169.254.166.12: bytes=32 time=1ms TTL=128
    Reply from 169.254.166.12: bytes=32 time<1ms TTL=128
    Reply from 169.254.166.12: bytes=32 time<1ms TTL=128
    Reply from 169.254.166.12: bytes=32 time<1ms TTL=128
    Ping statistics for 169.254.166.12:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 1ms, Average = 0ms
  9. And voila, you've now successfully networked your host machine to talk with your client virtual machine.

If you still want internet access for your client virtual machine, do the following:

  1. Open your Network and Sharing Center
  2. Click Manage network connections in the left hand navigation bar
  3. Right-click the loopback connection and select properties.
  4. In the sharing tab, enable “Allow other network users to connect through this computer's Internet connection.

This "should" work, but I initially encountered "an error occurred while Internet Connection Sharing was being enabled." However, it resolved soon afterwards all by itself.

The article that was the biggest help in getting the above in place was http://www.governmentsecurity.org/archive/t10129.html

UPDATE: Change to post title.

Adding a second virtual drive to a VM

by Ted Jardine Wednesday, June 06, 2007 12:46 PM

Jeesh. I feel like a dunce. A dunce who just wasted too much time to figure out something I already knew.

The problem: adding a secondary virtual drive to a Win XP virtual machine. No problems adding it via the Virtual PC 2007 console; no problem once booting up the VM having the drive found ("Virtual Drive found and installed"); but big problem in that the new drive is nowhere to be found in Windows Explorer.

Solution:

  1. Right-click My Computer
  2. Manage
  3. Disk Management
  4. Activate the new virtual drive
  5. Give a drive letter and partition
  6. And if you're like me, change the drive letter on your CD-Rom to E: and then switch your secondary hard drive to D:.

To switch the CD-Rom drive letter:

  1. Make sure you maximize the Computer Management window to see all drives listed (as the CD-Rom won’t be listed in the drive volume listing above)
  2. Right-click your CD-Rom and select Change Drive Letter and Paths
  3. Then in the drive volume listing above, select your secondary virtual hard drive and right-click and select Change Drive Letter and Paths.

I guess virtual drives are a little too virtual to instantly become available in explorer ready and waiting for formatting.

What's most painful about this is that all along while googling everywhere trying to come up with the magic search term combination (i.e. "Virtual PC 2007 secondary hard disk" doesn't quite work - I KNOW IT'S A GOOD IDEA TO INSTALL YOUR VM ON A SECONDARY DRIVE!!!), in the back of my mind I'm thinking I should look in Computer and Disk Management...

Btw, If google doesn't work right away and it's Windows related, check out Microsoft's Community Forums.

Month List