SQL Server Express 2014: Network Access

Question

I have SQL Server Express running on one (virtual) machine running Windows 8.1, but I cannot connect to it from another.

I have followed the instructions on https://msdn.microsoft.com/en-us/library/ms191294.aspx, which is pretty similar to countless other instructions, restarted the server, restarted the machine, and, for good measure, made another coffee.

I know I can connect to the other machine using another protocol. I ran HFS on the machine (a nifty little HTTP server), and could easily connect from another machine.https://msdn.microsoft.com/en-us/library/cc646023.aspx to allow access through the firewall, but even with the firewall totally turned off, it doesn’t work.

My settings in Configuration Manager are:

Protocols for SQLEXPRESS | TCP/IP
    Protocol: Enabled Yes; Keep Alive: 30000; Listen All: Yes
    IP Addresses | IP2: Active Yes; Enabled Yes; IP Address …; 
                        Dynamic Ports 0; TCP Port [empty]

How can I access this server from the network?

Thanks

Answer 1

SQL Express installs by default with a named instance of SQLEXPRESS. See https://technet.microsoft.com/en-us/library/ms143744(v=sql.90).aspx as it applies to SQL Express 2005. The settings you give in your question imply your instance uses dynamic ports. Running SQL Browser and connecting using the named instance as previously suggested should in theory help. If not, read on...

To restate: you're using a Mac to host a virtual machine where your SQL Express instance lives. You're connecting to this virtual machine from the Mac host. HTTP connections are okay, but not SQL Server connections.

There is a possibility that the Mac's network security allows connections over port 80 but not other ports. That may be worth investigating, especially if you're trying to connect to the VM from an entirely different computer; the Mac's firewall may be preventing the connection in that case. Depending on what you're using to run the VM (virtualbox? vmware?), it's possible that network settings there are interfering with what you want to do as well.

Determining your SQL Server's port

If you're fortunate enough to have SQL Server Configuration Manager on your SQL Server box, you can run that, choose your instance, expand SQL Server Network Configuration, then click Protocols for instancename. Double-click TCP/IP (it IS enabled, right?) Choose the IP Addresses tab and scroll all the way to the bottom where you'll see IPAll. On your way there, notice if TCP Dynamic ports is zero for the various IPn entries and TCP Port is blank. Once you get to IPAll, notice the value for TCP Dynamic ports - this is what port SQL Express listens on if you're using dynamic ports. For each IPn item where TCP Dynamic ports is zero, your instance will listen on the value specified in IPAll.TCP Dynamic Ports. Otherwise, it listens on the TCP Port value for that particular item (or IPAll if blank).

Assuming you have all the defaults and SQL Browser is running, connecting to 192.168.2.1\sqlexpress should get you to where you need to go. If not, try 192.168.2.1,dynamicportnumber instead. Yes, that's a comma separating the IP address and port number. So if your SQL instance uses port 49390 like the screen capture above, it would be 192.168.2.1,49390.

No SQL Server Configuration Manager? Open a command prompt as administrator and use netstat -b -a -p TCP to get a list of open ports and attached processes:

The -b switch tells netstat to give you the associated process, -a says list listening ports, and -p TCP limits the list to just TCP otherwise you'd get a lot of extra items to sift through.

Note this particular box has an instance listening on two different ports: 1433 and 49390.

If none of this works (which would be sad because this is a lot of typing) I recommend you run a good port scan against the IP address where your SQL instance lives. NMAP works on Mac, Linux and Windows: https://nmap.org/

Also, install SQL Server Management Studio Express (https://technet.microsoft.com/en-us/library/ms365247(v=sql.105).aspx) on the SQL VM and try connecting to the database engine using that. If you're on the box and it can't connect to itself, the problem is on the virtual machine itself - that will help narrow things down considerably.

Answer 2

On SQL Server configuration manager you have to enable TCP/IP And make sure that in properties IP Addresses 1433 is set as TCP Port on everywhere. Because 1433 is default TCP port for SQL Server, and make sure that your Firewall doesn't block this port.