I’ve recently installed SQL Server 2012 Express SP1 under Windows Server 2012 Essentials. I’m using it to host GoldMine CRM databases. There is so much information about network and firewall access, I wanted to write down what worked for me.
Important Background
SQL Server Express is by default installed as a named instance. That’s the “SQLEXPRESS” in the SQL server name, e.g. if your server is named SVR2012E, your default SQL Express instance will be named SVR2012E\SQLEXPRESS.
That’s important not only because you need that name to make network connections, but because named instances by default use dynamic TCP ports for their connections, i.e. the port number can change whenever SQL starts. You can reconfigure it to use a fixed port, but I wanted to see if I could get it to use dynamic ports.
SQL Server Setup
This part seems pretty well documented but I’ll repeat it here for completeness.
1. In SQL Management Studio, right-click on the server name and select Properties. Click on Connections, and in the right pane, check Allow remote connections to this server.
2. In SQL Server Configuration Manager, on the left side, highlight the SQL Server Services node. On the right side, highlight the SQL Server Browser service, right-click, and select Properties. On the Service tab, set Start Mode to Automatic. That will start it automatically whenever the server boots. Go ahead and start it manually now (right-click on SQL Server Browser and select Start).
3. Still in SQL Server Configuration Manager, on the left side, click on the SQL Server Network Configuration node, then highlight the Protocols for SQLEXPRESS node. On the right side, right-click on TCP/IP and click Enable. You can also look at the Properties, but they should already be set to allow dynamic connections.
4. After you enable TCP/IP connections, you’ll be advised that you need to restart the SQL Server for the new setting to take effect. You can do that from the SQL Server Services node by right-clicking on SQL Server (SQLEXPRESS) and selecting Restart.
Windows Firewall Setup
This is the area where things seemed a little ambiguous.
1. This TechNet article says that when using dynamic ports, the inbound firewall on the server needs to allow access to the slqservr.exe program, not a port. So for SQL 2012 Express, create an inbound program exception allowing access to
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\sqlservr.exe
2. What wasn’t clear is that the SQL Server Browser service also needs a firewall exception. Another TechNet article says that the Browser service uses UDP connections to port 1434. Create an inbound port exception for UDP 1434:
At this point, you should be able to connect remotely to your SQL server.
3. Optional: for additional security, go back in to each of your firewall rules and set the Scope to restrict access to the Local subnet (assuming all valid connections come from inside your local network):
And yes, the top half of that dialog, Local IP address, is intentionally set to Any IP address. This is referring to the IP addresses of the server. Think of this as the target IP address for the packets coming in through this rule, whereas the Remote IP addresses are the source IPs. I want this rule to apply no matter what local IP is targeted, but only if the packet comes from a the Local subnet.
Thanks, It’s really useful to everyone..
I’ve done all this and still cannot connect to the SQL server.
Sorry to hear that, David. If you find that something was missing from the article, please post another comment so others can benefit! Personal consulting is another option.
I found the missing but essential info here: the udp inbound rule.
Thanks
Piet
Pingback: SQL Server 2012 Express Over the Network | PipisCrew Official Homepage
After a complicated struggle, this article has been a lifesaver. The section on the firewall has always been a mystery – now it is all so clear and really simple! Thanks Mark – if we ever meet, I owe you a beer
Hi Mark,
I have a SQl Server 2012 installed on a Windows 2012 platform in a VM on my LAN. The VM has a Fixed IP address. Also I have a Fixed Public IP from my ISP. I’ve also forwarded the ports 1433, 1434 on all protocols to the IP on the VM hosting the SQL Server installation.
However, when I try to connect to the SQL Server through internet I’m not able to do so even if I turn off the firewall on the VM. Can you please tell me what I might be missing here?
Ashish, have you checked step 3 under “SQL Server Setup” above? I believe you’ll need TCP/IP to be able to leave the LAN. Also, make sure it works from another computer inside the LAN before trying to get it to work from the Internet.
Thanks Mark,
I have checked all the steps mentioned above. All protocols are enabled and I am able to access the server using the SQL Server Management Studio from another computer within the LAN, just not able to connect to it over the internet.
Now that I think about it, the dynamic ports may be your problem. As I understand it, the Browser tells the client what port to use. Let’s say the Browser tells the client to use port 23413. The firewall can allow that based on the _program_ exception, but your router probably doesn’t dynamically allocate ports that way. I think for Internet access, you may need to go back to the old static-port scenario. You can google for “SQL listen on static port”. Here is one result: https://support.microsoft.com/en-us/kb/823938.
connection works with public firewall off
and private firewall on
does not work with public firewall on
Noel, I didn’t cover this in this article, but Windows has three firewall profiles, Public, Private, and Domain. You’ll need to make your firewall adjustments to whichever profile is active. I do not recommend using Public for SQL connections–in general, you want to block Public access t your SQL databases.