In this article, we will show you how to check the current TCP port on which a named or default MS SQL Server instance is listening (waiting for connection on), how to change an SQL Server connection port to a static/dynamic one, and how the SQL Server Browser service is used by clients to connect to MSSQL.
- The default SQL Server instance (MSSQLSERVER) uses static TCP port 1433. It is the port the clients or SQL Server Management Studio (SSMS) console connect to;
- Named MSSQL and SQL Server Compact instances are configured to use a dynamic TCP port from the RPC range (49152 – 65535).
A dynamic port means that the port number of the MSSQL instance accepts connections on is assigned after the SQL Server service is started. In most cases, even after a server reboot, SQL Server will listen the same dynamic TCP port assigned prior to the restart. But if the port number is busy, SQL Server will run on a new TCP port. An app that connects to MSSQL usually gets the new port number from the SQL Server Browser service without any problem ( we will tell about it later). Dynamic SQL Server ports are convenient when you manage multiple SQL instances on a single host, but they cause a lot of problems when firewalls are implemented in your network.
Configuring MS SQL Server Instance to Listen on a Specific Port
You can reconfigure your SQL server to listen on static or dynamic TCP port. Typically, you may need it when multiple SQL Server instances are running on a single host, or firewalls are used in your network.
To manage listen ports, you will need the SQL Server Configuration Manager. Usually, this snap-in is installed together with an MSSQL instance.
Open SQL Server Configuration Manager and expand the SQL Server Network Configuration section.
In my case, there is only 1 MSSQL instance running on the server, so I will configure listening ports for it. The list of available protocols for an instance contains the following:
- The Shared Memory protocol is used to connect from a local computer (the one an MSSQL instance is installed on). It is not recommended to disable it;
- Named Pipes may be used over the TCP/IP protocol. But its use doesn’t give us much benefit, so we will leave it enabled.
- TCP/IP is where MSSQL network options are configured.
Double-click TCP/IP.
There are only 3 parameters on the Protocol tab::
- Enabled – to make sure that the TCP/IP protocol is enabled;
- Keep Alive how often to send keep-alive packets to make sure the connection is still available (in milliseconds). Do not change the parameter without a need;
- Listen All is an unobvious setting responsible for the contents of the IPAll section in the IP Addresses tab. If the value of Listen All is No, the IPAll section is ignored
On the IP Addresses tab, you can see the list of all IP addresses of the computer (including IPv6 and local ones) and their settings. Here you can configure different TCP ports for a local and an external connection address, or different ports for different external addresses (if your server has multiple network interfaces in different segments, for example then using VLANs or multiple IP aliases).
It is likely that you may want to change ports for all IP addresses at once, so change its IPAll section.
The TCP Dynamic Ports option is related to using dynamic ports.
- An empty TCP Dynamic Ports value disables using dynamic SQL Server ports;
- 0 enables using dynamic TCP ports from the RPC range 49152 – 65535;
- It doesn’t make any sense to set any fixed port value here — it changes each time after the MSSQL instance is restarted.
To set a static TCP port for an SQL Server instance, disable TCP Dynamic Ports and specify a new number of the static port in the TCP Port parameter.
To apply the changes, restart the SQL Server service. Note that SQL Server Browser service is stopped.
Make sure you can connect to your SQL instance using SSMS over the new fixed TCP port. The connection string format is as follows:
hostname\lab-sql, PortNumber
You won’t be able to connect without specifying the listening port because the SQL Browser service is disabled.
SQL Server Browser Service: TCP and UDP Ports
Before MSSQL 2000 you could not install more than one SQL instance on a host. This feature appeared in newer MSSQL versions. SQL Server Browser appeared in SQL Server 2005 and was used as an intermediary service to distribute connections between multiple MSSQL instances installed on the same computer.
Also, SQL Server Browser is responsible for connection to MSSQL (for example, from SQL Server Management Studio) without specifying a port number (hostname\lab-sql). SQL Server Browser gets the current dynamic port number of an instance from the registry and sends it to a client.
If you disable the SQL Server Browser service, you will have to specify the TCP port manually. For example, hostname\lab-sql, 1440.
If the SQL Server Browser service is stopped and dynamic ports are used, apps won’t be able to get port numbers to connect to.
Standard SQL Server ports:
- TCP 1433 is the standard SQL Server port;
- UDP 1434 is a port used by SQL Server Browser.
Other ports are configured when you install/configure a specific service.
If you are using strict firewall settings or if you want to restrict SQL Server as much as possible, it is recommended to disable Dynamic Ports (set an empty value) and disable the SQL Server Browser.
If your SQL servers have public access, it is worth to change the default port number. It will not protect you from hacker attacks completely, but their frequency will be reduced.