Connections to the SQL Server were being blocked by the firewall. Looking at their firewall logs, they saw connections blocked on port TCP 49153. Why? Why not TCP 1433?
This was a pretty simple problem to solve, and not uncommon to face right after installing a SQL Server named instance.
Even though there was only one SQL Server instance on the server, the instance was installed as a named instance (servername/instancename), not the default instance (servername). A Windows Server can host many SQL instances, but only one can be the default instance, and there doesn't have to be a default instance.
Default instances use TCP 1433 by default, but this isn't "the default port for SQL Server" necessarily. Named instances can be configured to use TCP 1433, but by default, named instances are configured to use dynamic ports in a range between 49152–65535. Most importantly, a SQL Server using a dynamic port will change port every time the Database Engine service starts up. This is incompatible with port-based firewall rules unless you unwisely choose to specify a huge range of port exceptions for your firewall (not recommended).
(But why do local connections still work, like a local installation of SQL Server Management Studio or an application running on the same server as SQL Server? Because those connections probably aren't using using the TCP protocol but the Shared Memory protocol. Connections to SQL Server from a client running on the same server always try to use Shared Memory first, and are faster for it.)
For connections external to the SQL Server, the SQL Browser (UDP 1434) handles the connection and routes traffic to the correct TCP port for each instance, even if there is only one instance on the server. (The SQL Browser isn't required, if you'd rather always specify a port number in all connection strings, or set up SQL alias(es).)
The solution to the dynamic port problem is to change the SQL Server to use a static port. You can configure a named instance to use TCP 1433, though it may be misleading and confusing if another SQL Server default instance is ever installed on this server. Instead, choose any port in that same range, for example the one most recently dynamically assigned.
How do you configure a SQL Server instance to use a static port instead of a dynamic port? (This change won't take affect until you restart SQL Server service, so I recommend you only make this change during a maintenance window.)
- On the server, open Sql Server Configuration Manager, expand SQL Server Network Configuration, and go to Protocols for InstanceName, click on properties of the TCP/IP protocol.
(As a book author, can I just say how impossible it is not to notice little inconsistencies like the capitalization of "Sql" vs "SQL" in the same screen...) - By default, in the "Protocol" tab, you'll see that Listen All is enabled by default. SQL Server will listen on all network adapters, including the loopback adapter(s). That's not necessary. You can disable unused adapters, and on the "IP Addresses" tab, enable only the network adapters needed.
If you want to keep using Listen All, then on the "IP Addresses" tab, only use the IPAll section to set the static port. If the Listen All is Yes, only the TCP Port and TCP Dynamic Port values under the IPAll section will be honored.
If you want to disable unneeded network adapters, on the "IP Addresses" tab, you'll need to enable each adapter(s) desired, and configure the TCP Port setting for each. For many SQL Servers and most standalone instances, there is only one network adapter needed, though others like the loopback adapter will appear. - Either under IPAll or each individual enable adapter, delete the 0 in the "TCP Dynamic Ports" field. When 0 is provided in this field, SQL Server listens on a dynamic port. You must leave it blank to use a static port.
- Either under IPAll or each individual enable adapter, provide the static port you want to use in the "TCP Port" field.
- Make the change to your port-based firewall to allow inbound traffic on the TCP port you've chosen.
- Restart the affected SQL Server instance.
For example, if you want to use only the primary internal IP address, and configure it to use port 52057 (a random selection in the allowed range), this is what your screens should look like, with Listen All disabled.
If you need then to configure Windows Firewall, it would look something like this in an Inbound rule. Here's an example with a rule to allow two different SQL instances, each configured for a different static port.
