We are back to my gut feeling! Windows Firewall is enabled by default unless you have some corporate policy disabling it. Usually, it is enabled. When you install SQL Server, it does not place a firewall rule by default to let it receive outbound connections.
SQL Server can be a default instance or a named instance. The default instance uses TCP 1433 for its listening port. A named instance is more complicated. The TCP port for a named instance will be dynamic by default. Also, we have to be aware of SQL Browser.
SQL Browser is a windows service that helps to translate the named part of the instance to a TCP Port or pipe. In our example, the server we are connecting to is the following.
SQL Browser will take STARWARS and return back the port. Then the client will connect to the TCP port for the instance. SQL Browser listens on UDP 1434. I mentioned a pro tip earlier that error 26 means we couldn’t talk to SQL Browser. It could also mean that something is just wrong with SQL Browser.
In all the years I’ve worked on connectivity issues, that only happened one time! Every other time, it was something blocking UDP 1434.
Which brings us to the Windows firewall. Depending on your network topology, it could be some device in between the client and server and not the Windows firewall. Start with the Windows firewall, though. If that doesn’t pan out, then find out what else is in between them.
You will need to add specific port rules for the Windows firewall. We know that we need to add UDP 1434 for SQL Browser but you also need to add the TCP port that the named instance is listening on. To do that, you need to find what that port is. You can use SQL Server Configuration Manager to discover it.
Once you have that port, you can add it as well.