Can't connect to named SQL Server 2008 R2 instance remotely

Question

I have a Windows Server 2008 R2 machine with SQL Server 2008 R2 Enterprise installed. The SQL Server is setup as a named instance, so its full name is "ATLANTA3\FOO" ("Atlanta3" being the machine name, and "Foo" being the instance name).

I have set up a Windows Firewall with Advanced Security rule to allow all incoming connections to sqlservr.exe, and this is what is set in Configuration Manager:

Shared Memory: Enabled
TCP/IP: Enabled
     Protocol:
         Enabled: Yes
         Keep Alive: 30000
         Listen All: Yes
     IP Addresses:
         (Most of these are IPv6 addresses assigned to tunnel adapters and are ignored)
         IP2:
             Active: Yes
             Enabled: Yes
             IP Address 192.168.0.17 // this is the internal LAN adapter
             TCP Dynamic Ports: 0
             TCP Port: (blank)
         IP4:
             Active: Yes
             Enabled: Yes
             IP Address 89.xx.xx.xx // this is the Internet-exposed adapter. I can ping this from home.
             TCP Dynamic Ports: 0
             TCP Port: (blank)
         IPAll:
             TCP Dynamic Ports: 49280

I use TCPView to see the port that the instance is listening on (49280) and I can open a telnet connection perfectly fine.

However I can't seem to connect to the instance from any remote machine, on both the internal 192.168.0.x LAN as well as from the Internet. I get this error from SSMS:

TITLE: Connect to Server

Cannot connect to ATLANTA3\FOO.

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

I've followed all of the instructions in the TechNet and MSDN articles for allowing SQL Server through the firewall. This sounds like a problem with SQL Server Browser, but I can't find any information on getting SSB to play nice with the firewall, assuming it ever needed to.

I can't disable the firewall outright, because when I do the server disconnects itself from all networks and isn't responsive remotely and I have to get the datacenter guys to manually reboot the machine for me (not good).

Answer 1

Is UDP 1434 open on your firewall? That's the port that the SQL Browser talks on. Think of the SQL Browser as DNS for SQL instance names. It resolves instance names to port numbers.