I’ve got VS2010 installed as an application inside XP mode which is fantastic except that I really rather wanted to use the instance of SQL 2008 installed on my Windows 7 box rather than the instance of SQLExpress that VS2010 can install with itself. And, should you need to do this yourself, this is how you do it…

In brief

  1. Install the Loopback Network Adapter on your Windows 7 machine
  2. Set the Loopback Adapter to have a static IP address which the VPC network adapter will use as its internet gateway
  3. Configure the XP Mode Virtual Machine to use the loopback adapter as its gateway.
  4. You should now be able to ping your host machine from your guest OS.
  5. Open SQL Server connections via TCP/IP on host machine [updated 24/12]
  6. Open incoming connections on port 1433/1434 for windows firewall
  7. You should now be able to telnet to port 1433 on the host OS from XP mode

More detailed instructions for each of these steps after the break.

[more]

Install Loopback Adapter

  1. Be logged on as Admin or run Computer Management Admin Tool as Administrator
  2. Choose Device Manager (can't directly run DM as admin from System Control Panel)
  3. Right click system icon. Select 'Add Legacy Hardware'

    Loopback1

  4. Click Next
  5. Choose 'Install the hardware I manually select from a list'. Click Next
  6. Choose Network Adapters. Click Next
  7. Choose Microsoft as the Manufacturer and Microsoft Loopback Adapter. Click Next

    Loopback2

  8. Click Finish. The Loopback Adapter is now in your list of devices.

    Loopback3

Set the Loopback Adapter to have a static IP address which the VPC network adapter will use as its internet gateway

  1. Open Control Panel > Network and Sharing Center
  2. Click Change Adapter Settings on the left hand side
  3. You'll see a new Local Area Connection has appeared with no network access. Switch to details view and you'll see this is for the loopback adapter.

    LoopbackAddress1

  4. Right click this new connection and click Properties.

    LoopbackAddress2

  5. (optional) Uncheck 'Internet Protocol Version 6' unless you absolutely need it
  6. Select 'Internet Protocol Version 4' and check properties
  7. Check 'Use the following IP address' and give the adapter a static IP address such as 192.168.3.1. Add the same address as the default gateway. The subnet will automatically fill in as 255.255.255.0.
  8. Click OK twice

Configure the XP Mode Virtual Machine to use the loopback adapter

  1. Run the XP mode machine.
  2. Choose Tools > Settings
  3. Select Networking from the left hand pane.
  4. Set an adapter to 'Microsoft Loopback Adapter'

    XPModeSettings

  5. Now choose Start > Control Panel > Network Connections
  6. Right click Local Area Connection and select Properties
  7. Choose Internet Protocol and click Properties
  8. Check 'Use the following IP address' and give the adapter the static IP address 192.168.3.2. Add the address you gave to the loopback adapter to the default gateway, e.g. 192.168.3.1. The subnet will automatically fill in as 255.255.255.0.
  9. Click OK and then close.

You should now be able to ping your host machine from your guest OS.

Open SQL Server connections via TCP/IP on host machine

  1. Choose Start > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager. You'll need to run it as an administrator.
  2. Choose "SQL Server Services" and stop the service for your SQL Server instance. If you don’t run
    SQL Server Configuration Manager as admin, you won’t be able to stop the services but you won’t be given a UAC prompt…

    SqlServerConfiguration1

  3. Choose "SQL Server Network Configuration" > Protocols for SQL2K8.
  4. Enable TCP/IP (and Named Pipes if needed)

    SqlServerConfiguration2

  5. Double click the TCP/IP entry. You’ll be presented with a list of IP addresses that SQL server is listening for connections on. Change one of the IPv6 addresses to read 192.168.3.1, set it as both active and enabled.

    SQLIPConfiguration1

  6. Now scroll down this dialog to see the section marked IPALL. Add 1433 as the TCP port all connections should come in on.

    SQLIPConfiguration2

  7. Click OK after you’ve made those changes.
  8. Return to "SQL Server Services" and restart the service for your SQL Server instance
  9. Also start the SQL Server Browser service which lets incoming clients see your SQL server instances.

Open incoming connections on port 1433/1434 for windows firewall

  1. Choose Control Panel > Windows Firewall > Advanced Settings
  2. Select Inbound Rules on the left hand panel and then New Rule from the right hand side
  3. Select Port, then Next

    FirewallWizard1

  4. Set TCP port 1433 and then Next

    FirewallWizard2

  5. Select "Allow the connection" and then Next
  6. Choose the type of network it should be allowed on. Click next.
  7. Set name to SQL Server, then Finish.
  8. Repeat for UDP port 1434. Call is SQL Server Browser
  9. Finally, select ‘Allow a program or feature through Windows Firewall’.
  10. Click ‘Allow another program’.
  11. Click ‘Browse’ and find sqlservr.exe. It is typically in C:\Program Files\Microsoft SQL Server\100\<instancename>\MSSQL\Binn. Then click Open and then Add.
  12. Set the newly created entry for ‘SQL Server Windows NT’ to open for both Private and Public networks.

    FirewallWizard3

  13. Click OK.

You should now be able to telnet to port 1433 on the host OS from XP mode

If you can’t, then start diagnosing the issue by checking whether or not it is a firewall issue.

  1. Choose Control Panel > Windows Firewall.
  2. You’ll be able to discover whether the firewall considers the network created by the loopback adapter as Public or Private.
  3. Click ‘Turn Windows Firewall on or off’
  4. Turn off the firewall for the appropriate public or private zone and click OK.

    FirewallTroubleshooting1

  5. Try and telnet to the host OS on port 1433 from the XP Mode virtual machine. If it connects, it’s a firewall issue, if not, then go back over the loopback installation steps.
  6. Switch the firewall back on for the zone.

If it is the firewall, try the firewall troubleshooter.

  1. Choose Control Panel > Windows Firewall.
  2. Click ‘Troubleshoot my network’
  3. Click ‘Incoming Connections’ and then click Next
  4. When asked what you want to troubleshoot, click ‘Something Else’ and then Next.

    FirewallTroubleshooting2

  5. Browse to find the sqlservr.exe program found earlier and click Next. Follow the prompts from the wizard.

One final note. VS2010 inside XP Mode only seems to recognize the instance of SQL Server on the host OS by the name of the host machine. In my case, BLACKBOX rather than BLACKBOX\SQL2K8 as I was expecting. Odd, but hey it works. Thankfully.