1

I have an issue with ISA Server and SQL Server...

In SQL Server Management Studio there can be done a query for server instances to network servers. This is done by broadcasting to SQL Browser service through the subnet and servers those run browser service respond with their IP adresses and instance names. The problem with that is routers drop all broadcast packets by default and my SQL Server runs on different subnet from my actual LAN subnet, behind ISA Server:

graph

So, ISA captures broadcast packet created by my terminal and drops it as expected... I'm seeking for relaying those packets created in 24.0 leg to 8.0 leg, like DHCP relaying...

I checked ISA for some kind of relaying broadcast messages but there're none that I encountered. We can do DHCP relaying by installing relay agent in RRAS, and creating access rules on ISA, like:

dhcp rules

What I'm wondering is if there is a way to do a relay broadcast messages targeting sql browser service like dhcp above? Do I have to write a listener application to work on ISA computer (SQL Browser Relay Agent), or is there such app does that? What do you recommend?

Hasan Manzak
  • 123
  • 1
  • 7

2 Answers2

2

As far as I know the SQL Browser service listens for incoming connections from client applications to UDP port 1434 of the server. As far as I know this is a unicast UDP message, not a broadcast UDP message. You should be able to get this working by allowing inbound UDP port 1454 to the SQL server in the ISA rule set.

joeqwerty
  • 108,377
  • 6
  • 80
  • 171
  • As far as I experimented, yes browser service listens on UDP 1434 but clients which dont have a specific IP address of server HAVE TO broadcast to get server's IP first and then they can communicate over UDP 1434. – Hasan Manzak Nov 09 '11 at 04:04
  • 1
    That makes sense, but that broadcast doesn't have anything to do with the SQL Browser service, it has to do with name resolution. If you give the clients a way to resolve the SQL server host name without resorting to broadcasting then you'll solve the problem. Are the clients and the server part of the same company/entity? – joeqwerty Nov 09 '11 at 12:13
  • Clients can make a DNS query for SQL server. I also configured firewall rules to connect the server. Clients can connect database engine through either FQDN or IP address directly, no problem with SQL Server connectivity. I'm trying to achieve the scenerio of having multiple SQL Server boxes behind ISA. This can be done all in name resolution of course, but this kicks the ability of management studio app searching for network servers out of action which I think it is not a best practice and ineffective generally... Yes, clients and servers are part of same entity, but in different subnets. – Hasan Manzak Nov 09 '11 at 13:31
  • 1
    In order to connect to all instances of SQL server you'll need firewall rules that allow UDP port 1434 (SQL browser) to all instances and TCP port rules for each port that your SQL server instances are running on in order for the ISA to allow the traffic. For instance, if one SQL instance is running on TCP port 1364 then you'll need an ISA rule that allows inbound traffic to that instance for TCP port 1364 as well as a rule that allows inbound traffic to that instance for UDP port 1434. Continued... – joeqwerty Nov 09 '11 at 14:08
  • 1
    In my opinion this kind of defeats the purpose of running SQL on a non default port (TCP 1433) and makes firewall configuration more complex as referenced in this article: http://support.microsoft.com/kb/287932 – joeqwerty Nov 09 '11 at 14:08
  • Yeah, I know that practice and applying it, too. But I think we're still not in the same page here. All of your pointings are about specific client communicating with specific sql browser. My point is 'to determine' sql browser. As I wrote this is done by broadcasting. When I check firewall log, specific client first generates a UDP 1434 broadcast message. It just queries the subnet for any browser running. When all terminals get that broadcast message, ones without browser service running drop it, as broadcast's implemention, ISA drops it as well. Continued... – Hasan Manzak Nov 09 '11 at 15:17
  • So allowing browser through doesnt help right now... I already allowed for browser service... Edit: BUT!! This came to mind right now, I haven't tested installing browser service on ISA itself... Gotta try this.. – Hasan Manzak Nov 09 '11 at 15:18
  • 2
    The client does not broadcast to find the SQL instance to connect to. It broadcasts to resolve the name of the host that SQL is running on, this is unrelated to SQL server, it's related to name resolution. The client has to know what server name to connect to in the first place. The client sends a UDP message to the server on port 1434 and the SQL Browser service returns a list of instances and port numbers. See here: http://msdn.microsoft.com/en-us/library/ms165724(v=sql.90).aspx – joeqwerty Nov 09 '11 at 15:51
0

I'm pretty sure that SQL Browser isn't a broadcast, but just connects over UDP 1433. Assuming that you configure your firewall to allow that traffic to go through, you should be good to go.

Ben Thul
  • 2,969
  • 16
  • 23
  • As far as I experimented, yes browser service listens on UDP 1434 but clients which dont have a specific IP address of server HAVE TO broadcast to get server's IP first and then they can communicate over UDP 1434. – Hasan Manzak Nov 09 '11 at 04:05