Recently I configured an instance of Microsoft SQL Server Express 2008 R2 at the house for external access on the internet for testing purposes, and after having to google around more than I had thought I'd need to do, I want to present a how-to on configuring Express for access from the Internet.
These instructions should work for most versions of Microsoft SQL Server Express beginning with 2005, and may work with older versions too, but I've not tested them.
An unnamed instance of SQL Server will generally use TCP/UDP port 1433 for communication, but named instances behave differently. They do not use port 1433, but a randomly assigned port at installation time (I have not researched this, but it does not generally seem to change during service restarts or reboots.) This is also the case for full versions of SQL Server that have the instance named at installation. Default instance always uses 1433.
The thing is, SQL Express is almost always a named instance of SQLEXPRESS if default settings are used. This means that when we reference the server in connection strings and such in our external connections and applications, we must reference it as servername\SQLEXPRESS or, if connecting by IP address, xxx.xxx.xxx.xxx\SQLEXPRESS.
First, we will need to find out what this port number is:
- Open SQL Server Configuration Manager.
- In left pane, expand SQL Server Network Configuration.
- Click "Protocols for SQLEXPRESS"
- In right pane, right click TCP/IP and select "Properties". (If TCP/IP is not an enabled protocol, enable it, as this protocol is required for external access in this manner.)
- In the window that appears, select the IP Addresses tab. This tab will usually contain 3 sections (at least): IP1, IP2, and IPAll.
- In section IPAll, look at field "TCP Dynamic Ports". Take note of the port configured here. (see below)
- Close out of SQL Server Configuration Manager.
Location of TCP Dynamic Ports
SQL Server, when using named instances requires that the SQL Server Browser windows service be running so that connection to the named instance of SQL server can be located and found for connection.
SQL Server Browser is not usually set to start up by default in SQL Server Express, and the service may even be disabled. Go into your windows services (If you want to launch it like a boss, do Windows Key + R on keyboard and type "services.msc" and hit enter), locate SQL Server Browser in the services list, enable it and set to automatically start, then start it.
The browser service binds to local port 1434 by default, and this does not change. Without the browser, even when we pass our TCP Dynamic Port for SQL Server instance SQLEXPRESS through the firewall on the router, when we try to connect from the internet (via a dynamic web page, SQL Server Management Studio, or a custom Windows application), we will still be unable to connect, because we need the browser service to point us to our named instances.
So, at this time, you need to add these ports to your external firewall's NAT, pointing them at the Windows Server hosting SQL Express in your internal network (which really ought to have a static IP address if it does not already):
- TCP/UDP Port 1434 (SQL Server Browser)
- TCP Dynamic Port of SQLEXPRESS named instance that we located earlier
This post cannot go into detail on every router possiblity, but each NAT instruction in your firewall will resemble something like this theoretically, it's just like adding access for a computer game or anything else:
[Internet] TCP/UDP 1434 <------> [Computer IP] TCP/UDP 1434
Once all these steps have been completed, test connectivity via SQL Management Studio in an external location, and you should be able to access it via your external IP address of your home router (ipaddress\SQLEXPRESS) and your SQL credentials (sa or whatever you've configured.)
It would probably be ideal to get a dynamic DNS address, as it is very likely you have a dynamic IP from your ISP (if you don't know that you have a static, you don't have one.) Visit http://no-ip.com
and follow their instructions to obtain one.