Configuring VM with SQL Express to be accessed using SSMS from host

Question

I'm trying to configure my VM with SQL Server 2008 R2 Express so that I will only be able to remotely access SQL using SQL Server Management Studio from host machine.

VM configuration

1. running Windows Web Server 2008 R2
2. There are two NICs:
   • Bridged so it connects to Internet
   • Host only so I can communicate faster over virtual network
3. SQL Engine and Browser services are both running
4. I have enabled TCP/IP protocol for SQL Express but haven't configured any IP Addresses (separate tab)
5. I've added incoming firewall rules for:
   • 1433 TCP port
   • 1434 UDP port
6. If I look into network connections I see both my NICs connected to networks. I can set bridged network to Workplace but I can't change network type of the host-only network. So it's set to Public by default. I'm not sure whether this has anything to do with it but that's how it is.

HOST configuration

The only thing I've done on my host is that I've put a name to my VM IP. And I've done so with the Host-only IP address. I've added an entry in my HOSTS file.

Problem

Then I open SSMS on my host machine and if I select Browse for more in the Connect to Server dialog window I can actually see my VM's SQL instance. But when I try to connect to it, this is what I get when I try to connect in the following three ways:

• machine name (my HOSTS file will do the IP remapping)

• bridged network IP address

• host-only IP address

Question 1: What else can I configure so that I will be able to access my VM's SQL instance? If possible I would also like to configure it, so that I can only access it via Hots-only network and not through the bridged one. But that would just be an added bonus if I could configure it that way.

Question 2: Do I actually need SQL Browser to run because as far as I know it only takes care of the SQL instance being discoverable. If I know SQL instance is there and I know it uses port 1433, can't I simply connect using this Server name:

DATAVM\SQLEXPRESS,1433

Edit

This is netstat's result:

I also ran portqry on the host to check for my SQL instance on my VM. If I try bridged IP address I actually get SQL Browser's response:

Starting portqry.exe -n 192.168.1.143 -e 1434 -p UDP ...

Querying target system called:
 192.168.1.143

Attempting to resolve IP address to a name...

IP address resolved to Datavm

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName DATAVM
InstanceName SQLEXPRESS
IsClustered No
Version 10.50.1600.1
tcp 49159

As I can see in this result my SQL instance is actually listening on TCP port 49159.

So I went in SQL configuration and changed this port to 1433.

Question 3: Is it normal that port wasn't set to 1433 by default? Should I change it back to 49159 and change my inbound firewall rule to pass through this port or shuld I now leave it on 1433?

By changing port to 1433 I can connect to VM from my host machine SSMS. But I can only do this using bridged IP address.

As I understand TCP/IP settings in SQL protocol the IP Addresses should list all machine's IP addresses. I can see 5 of them:

1. bridged NIC V6 address
2. bridged NIC V4 address
3. localhost V6 (::1)
4. localhost V4 (127.0.0.1)
5. some ? V6 address that's not related to bridged nor host-only network (fe80::100:7f:fffe%12)

I'm assuming this is also the reason why I can't connect using host-only network... But how do I configure my VM so that I will be able (and possibly disable the bridged one)?

Answer 1

Solution

As I dig deeper in this it seemed more and more that this is more of a networking problem than SQL Server configuration. And it was.

I've solved the networking problem and you can read about the solution on ServerFault. This will help you configure virtual Host-only network so it won't be as locked down as Windows sets it by default.

But there's some other thing. And that are **IP Addresses that one can see in TCP/IP Protocol in SQL Server instance configuration.

The issue here is that this IP list is only populated when you install your SQL Server instance. If you make any changes to your network adapters those changes won't be reflected in SQL Server configuration. This is a known issue since 2006 and yet not yet resolved.

In my case I added an additional NIC that is host-only and because my SQL Instance has already been installed, those IP Addresses don't show up in configuration. As it turns out, you can manually add those new or changed settings using registry editor.

Navigate to:

HKEY_LOCAL_MACHINE
  SOFTWARE
    Microsoft
      Microsoft SQL Server
        MSSQL10_50.[InstanceName]
          MSSQLServer
            SuperSocketNetLib
              Tcp

You will find all of the IP Addresses that you can configure there. Since I had to add two more addresses I exported IP2 and then edited the saved *.reg file:

1. Changed the first line and changed IP2 to IPn (where n is current max + 1)
2. Changed IP Address and added v6 IP address of the host-only network
3. Copied and pasted all these lines
4. Incremented IPn by 1
5. Changed IP Address and added v4 IP address of the host-only network

Saved the file and imported it into registry editor. I can then see two more IP Addresses in registry editor.

Opened up SQL Server configuration and yes I now have two additional OP Addresses in the TCP/IP protocol SQL instance configuration that I can individually configure.

That's it. Works.

Answer 2

SQL Browser is necessary for named instances if I'm remembering correctly. Your named instance listens at a different port (which you discovered) and SQL Browser will reveal that port. You can also configure your instance to listen on a specific port using the SQL network configuration.

I have found host-to-vm network communication a bit frustrating at times as well.

Have you tried turning off your firewall on the VM just to test? That's usually step 1 in pin-pointing connectivity issues. 90%+ of connectivity issues are firewall issues.