MS SQL server, behind F5, not responding to SNAT address
Posted on 2012-08-31
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.