Accessing SQL Server Instance through NAT

sql-server nat
bassburner picture bassburner · Mar 14, 2012 · Viewed 17.5k times · Source

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?

Answer

Remus Rusanu picture Remus Rusanu · Mar 14, 2012

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.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More