• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 776
  • Last Modified:

MS SQL server, behind F5, not responding to SNAT address

Our lead network engineer believes that he has seen this issue in the past.  He remembers that the issue was actually a Microsoft SQL issue and that a SQL server configuration change solved the problem.  However, he doesn't remember what the fix was...

All servers are running Windows Server 2008R2 with SP1.  The MS SQL server is running SQL 2008 with SP3-CU2

We have built a new 3-tier DMZ infrastructure using an F5 LTM.  The tree tiers are Presentation Tier, Application Tier, and Data Tier.  Each tier has three VLAN's; VIP, SNAT, and REAL.  The VIP VLAN is used for the IP address that a user or device would hit to access a load-balanced pool of servers (or, sometimes, a single server).  The REAL VLAN is used for the actual IP address that is assigned to the server.  SNAT's are used so we don't require custom route statements on servers.

We have an application server that is in the application tier of the DMZ.  The application server needs to access a Microsoft SQL server that is on our inside network.  We created a DATA VIP (10.144.246.143) and DATA SNAT (10.144.247.143) for the SQL server who's REAL IP is 10.1.13.143.  

The application server uses its REAL IP address (10.144.245.100) to connect to the DATA VIP of the Microsoft SQL server (10.144.246.143).  The SNAT translates the DATA VIP IP (10.144.246.143) to DATA SNAT IP address (10.144.247.143) and sends the request to the SQL server at 10.1.13.143.  Everything works up to this point.  The traffic gets to the SQL server.

The Microsoft SQL server receives a request from 10.144.247.143 (the DATA SNAT IP).  The Microsoft SQL server does its thing and tries to return the data.  However, it tries to send the response to the REAL IP address of the application server instead of the DATA SNAT IP that it actually received the request from.  So, the SQL server received a request from the application server that, because of the SNAT, "looks" like it came from 10.144.247.143.  However, instead of responding to 10.144.247.143, it tries to respond to 10.144.245.100 (the REAL IP of the application server) - which is not allowed by the firewall (and should not be allowed).

The servers we're testing with do not have DNS servers or WINS servers defined on their NIC's.  They are using HOSTS files for name resolution.  Configuring the application server to connect to the MS SQL server by IP or by hostname (which uses the HOSTS file) does not alter the problem.

We do not see any "Deny" information on the firewall until the SQL server tries to access the application server's REAL IP directly.  

I can PING the SQL server from the application server.  We see the ICMP traffic reach the SQL server.  We see the SQL server respond, send it to the SNAT, and back to the application server.  I can reverse this and ping the application server from the SQL server.  

I can Telnet from the application server to the SQL server on ports TCP/1433 and TCP/135 successfully.

RDP (remote desktop) works from the app server to the SQL server and the SQL server to the app server.

This seems to be an issue with the way SQL handles the inbound connection.

I thought that, perhaps, if we disabled the "use NetBIOS" functionality on the SQL server, it may solve the issue.  However, I'm not a SQL server guy.

I wanted to put this in front of everyone and get input before proceeding.

Thanks,
David
0
escreen
Asked:
escreen
1 Solution
 
unfragmentedCommented:
Since you're thinking its a 'SQL handling' problem, step back and verify firstly that you are getting a successful tcp 3-way handshake.  If you're not getting this, then you have a traffic flow problem that you need to solve first.

Prove this with a packet capture on your app server.  If you're familiar with testing an open port with telnet, you could use telnet to 'simulate' your app accessing the DB VIP and confirm the tcp handshake completes.

If the TCP handshake does not occur due to replies going direct to the app server, then you have a flow symmetry problem to solve.  SNAT is one way of solving this - there are others.

Lastly - most enterprise DB's have their own clustering mechanisms, and don't need a hardware loadbalancer on the front.  Be careful you don't introduce excessive complexity unnecessarily.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now