How to configure SQL Server 2012 for remote network connections

SQL Server 2012, especially when using a named instance, changes the way us old SQL Server veterans manage connectivity to the server.  We are so used to relying on port 1433 for SQL Server that setting up a new SQL Server 2012 database server might give us some fits.

I have one software engineering team that just did this recently.  Our dev and test environments are in Windows Azure, and then our production environment is in a specialized data center on the east coast of the U.S.

To make a long story short, we are using Azure’s point-to-site VPN so that none of the servers can have any contact from the outside world.  The only way to get to them is to VPN in.  Then, the servers respond to pings, and it’s as if they are on the LAN.  It works great – except connecting SQL Management Studio to a new Windows Server 2012 box running SQL Server 2012 wouldn’t work.  All the old tricks of using SQL Configuration Manager and enabling TCP/IP yielded no fruit.  And opening port 1433 on the firewall yielded no fruit as well.

Just for completeness, we were using a named instance, not a default instance.  A quick look at NetStat revealed that SQL Server wasn’t listening on port 1433 at all.

Notice how port 1434 is listening, however.  This is the SQL Server Browser service.  A search through the documentation reveals that Microsoft has made a bit of a change to the way SQL Server operates regarding ports.  These named instances now use dynamic ports – I think for security.  This TechNet article explains that to connect, the client will send a UDP packet to port 1434 first to resolve the dynamic port.  Then it will connect as normal.

The appropriate Windows Firewall rule to establish is a program rule, not a port rule.

By adding SQL Server’s exe as a program rule in the firewall, it now works.

It’s been several versions since something like this has changed in SQL Server, so hopefully this article will help someone else using SQL Server 2012 for the first time.