MSSQL Accross Subnets

A was faced with the situation that a client could not connect to it's MSSQL named instance in another subnet. There is a firewall in between those subnets. The firewall rule allowed TCP 1433, 1434 and Pings. The client was able to ping the server. So far so good. But the SQL client could not connect to the server. A nmap scan of the server showed that port 1433 or 1434, the classical MSSQL ports, were not open.

As it turns out, the server was set to use dynamic ports. Which is all nice as long as one does not have to manually open ports in a firewall. After the server was manually set to 1433 a telnet <dbserverip> 1433 was able to connect but the client still couldn't.

As it turns out, named instances apparently need to be able to access the server via UDP 1434 in order to access the SQL Server Browser. After enabling that in the firewall it all worked nicely.

As I found out after a bit more digging in the connection to the SQL Server browser is needed if no port is specified in the SQL connection string. And this is, for people like me who only occasionally deal with MSSQL in the form of server,port with a , and not a : as in most cases when setting ports explicitly.