Home / server / Questions / 359470
In Process
Robert Koritnik
Asked: 2012-02-13 23:33:30 +0800 CST

Connecting to SQL server instance on VM via host-only network

  • 772

I've setup a virtual development machine with Windows Web Server 2008 R2 and installed SQL Server 2008 R2 Express on it as well. I didn't install any SQL tools on it, because I have them on the host machine.

Virtual machine is running on VMWare Workstation 8.

VM has 2 NICs

  • Bridged so others (testers) can access web application in development
  • Host-only so I can have faster communication with this machine on my host

I'm not running SQL Browser and am connecting to it directly using machine\instance,port name. This way I can connect to it even though SQL is not discoverable.

I've opened SQL Engine's network port on the firewall.

Facts

I can access my SQL using bridged IP address, but not host-only IP address. I've checked Protocols for SQL Express and enabled TCP/IP and checked IP Addresses tab which shows 5 IPs:

  • 2 for bridged network (v6 and v4)
  • 2 for localhost (v6 and v4)
  • one v6 IP address (fe80::100:7f:fffe%12) that is not related to either bridged or host-only network.

This somehow explains it why I can't access my SQL using host-only network. Even if I change these settings and change bridged IP to host-only one, I still can't access my SQL.

it seems as if host-only network is not visible to SQL. Although it's working.

Another fact is that if I ping my VM from my host I get a response on bridged network but not on host-only one.

Question

I suspect this must be somehow related to VM's host-only network settings. Or maybe not. Hence this question. The question is of course How can I setup SQL server on my VM so that I will be able to access it via my host machine and from localhost (because web application is running on the same machine)? But I don't want SQL server to accessible via bridged network so nobody else will be able to access it.

virtualization vmware-workstation sql-server-express sql-server-2008-r2

1 Answers

  1. Resolution

    Ok I've managed to resolve this problem by configuring virtual network and configuring SQL Server.

    Virtual network configuration

    My Host-only configuration was set to automatic but using VMWare this network doesn't have any gateway applied to it, so I added a default Gateway to be the same as DNS (in my case this was 192.168.100.254).

    This made it possible for Windows networking to recognise the network as not public but rather private. This means that certain security levels are applied to it differently.

    I can now ping either machine one way or the other (from host to VM guest and vice versa). Both pings have a response which they previously didn't since network was labelled as public.

    SQL Server configuration

    Configuring protocols for SQL Express instance went and configured TCP/IP settings and on the first tab (Protocol) set Listen All to No which means that I have to configure individual IPs on the second tba (IP Addresses) where I've Enabled both localhost IPs (v4 and v6) and also changed the v4 IP address so it reflected virtual host-only network's IP address:

    • set IP Address to host-only network address
    • set Enabled to Yes

    Then cleared all these three IP address TCP Dynamic ports and set static one TCP Port to 1433 that I want to use.

    Voila, it works

    Now I went back to host and tried connecting SSMS to my VM using the bridged IP address first. It fails. Then as a second try I used host-only IP address and in did I was able to connect to my SQL Server instance. Success.

    This completely locked down my SQL Server instance to local machine and my development host.

    • 3