7

I'm attempting to access a SQL Server which is exposed through an IP NAT mapping. All the ports are open. I don't know the details of the NAT, if it's relevant, since that's somewhere else in the company hidden in a pile of red tape.

Here's what I figured out. When you attempt to access a named instance of SQL Server, the client asks what port the named instance is running on. If I RDP into the SQL Server I can use netstat to find out the port of that instance and can successfully connect through the firewall. However, connecting via the instance name doesn't work. My guess is that the server is responding at some point with it's internal IP address and the client is using that.

Does anyone know if this is true and if there's a way around it?

shA.t
  • 16,580
  • 5
  • 54
  • 111
bassburner
  • 620
  • 1
  • 6
  • 15
  • 1
    Instance to port number translation is done by SQL Browser service which is listening on port 1434/udp. Check if your NAT (in DMZ mode) is publishing all udp ports too. – wqw Mar 14 '12 at 19:10
  • In my environment it helped to turn off Windows Firewall on SQL Server machine. – scar80 Nov 05 '14 at 11:15

2 Answers2

7

The instance listening port protocol discovery is subject to the SQL Server Browser Service. This uses UDP on 1434. With a NAT forwarding of UDP 1434 your client should be able to interact with the SQL Server Browser Service (if the SQL Server Browser's UDP response packet can reach back the client, a big if), but even a successful interaction will put your client in a tight spot: now that it knows the SQL Server dynamic listen port, how does it reach it? The NAT would have to dynamically forward the port picked by SQL Server, or it would have to forward all ports.

What I recommend is to have your SQL Server listen on a per-configured, statically assigned, port. See How to configure an instance of SQL Server to listen on a specific TCP port or dynamic port. Have your NAT forward that port. Then in your client use this port explicitly in the connection string. Do not use 1433, the standard port, since I assume that ahead of the NAT is the public internet and 1433 is subject to constant and frequent scans from all sort of bots and vile clusters.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    +1, and 1433 is slowly fading away because it's used for a *default* instance (no instance name), and Microsoft is highly recommending never to use a default instance (for security reasons). Therefore, whenever you have a named instance, 1433 will never be relevant. – Jerry Dodge Aug 24 '12 at 13:32
3

Configure the named instance to run on a static port using SQL Server Configuration Manager. In configuration manager, SQL Server Network Configuration -> Protocols for <named instance> -> TCP/IP -> Properties.

enter image description here

Then supply the hostname and port for the named instance in the connection string. The hostname and port number are specified in the following format (assuming hostname is Test and listen port is 1492):

... Server='Test,1492'; ...

Paul Hunt
  • 3,395
  • 2
  • 24
  • 39
Bryan
  • 17,112
  • 7
  • 57
  • 80