Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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) enter image description here

  • bridged network IP address enter image description here

  • host-only IP address enter image description here

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: enter image description here

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)?

share|improve this question
    
Please check whether there is being listened on the port netstat -a and you also might want to configure the IP addresses (step four) to see whether that improves something. –  Tom Wijsman Feb 11 '12 at 17:07
    
@TomWijsman: Check my edited question where I attached netstat's result and also some partial solution where I was able to connect. but not the way I wanted to... Please read above. –  Robert Koritnik Feb 11 '12 at 17:58
    
Glad that you actually progressed, I'm not any good at VM network settings but just wanted you to add details that could get you (or others) towards a solution. Good luck! :) –  Tom Wijsman Feb 11 '12 at 18:04

2 Answers 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.

share|improve this answer
    
Also, add the SQL processes to firewall exceptions and not specific ports. That way it doesn't matter which ports the application is using. You're telling the firewall "this app is cool, I would trust it with my children". –  MikeJansen Feb 12 '12 at 19:35
    
You might want to edit your answer with this information rather than leaving it as a comment. –  soandos Feb 12 '12 at 20:13
    
@MikeJansen: Well it seems that especially if your server is open it's better to not run SQL Browser because it's not needed after all. You can always connect using port info as machineName\sqlInstanceName,portNumber. It just works. And since an app is accessing SQL database (and you the developer), port is a known variable. Running SQL Browser makes that variable publicly known. And instead of adding the EXE to firewall I just added port. It's arguable which one is better. –  Robert Koritnik Feb 13 '12 at 6:36
    
@MikeJansen: and yes... I did try turning off Firewall. It just seems I' can't connect via virtual host-only network... I think that should be a separate question now because it's just partially related to original question. –  Robert Koritnik Feb 13 '12 at 6:59
    
@MikeJansen: FYI: I've managed to configure it myself now even though I've asked a question on ServerFault as well. This is the link to solution there. –  Robert Koritnik Feb 13 '12 at 9:22
up vote 0 down vote accepted

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 IP*n* (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 IP*n* 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.