In this blog post we will look at how to configure an instance of the SQL Server Database Engine to listen on a specific fixed port. The default instance of a SQL Server Database Engine listens on TCP port 1433. Named instances on the other hand are by default configured for dynamic ports. This means they select an available port when the SQL Server service is started. Because the port selected might change every time that the Database Engine is started, this can pose a bit of a challenge if there is a firewall between the application server and the SQL server, which in most deployments there will be. Hence configuring the Database Engine to listen on a specific port, can be advantageous, and it’s also recommended by Microsoft to do this.
1. Start SQL Server Configuration Manager
2. Expand SQL Server Network Configuration, select the Database Engine instance you want to edit (Protocols for <instance name>), and then double-click TCP/IP
Here I for example have two instances, so I selected Protocols for INST01 beneath SQL Server Network Configuration
3. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. (You will see both IP version 4 and IP version 6 addresses.)
4. The TCP Dynamic Ports field will contain a 0. This denotes that the Database Engine is listening on dynamic ports.
5. On the IP address you want to configure to listen on a static port, delete the 0 in the TCP Dynamic Ports field, and then assign a port number in the TCP Port field. Here I’m assigning port 60000 on all IP addresses. It’s not a multihomed server, so all of those IP addresses belong to the same NIC.
Click Apply and OK when you are done.
6. Click OK on the Warning.
7. In the console pane, click SQL Server Services
8. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart the service.
9. Or better yet, restart the server.
10. Run the following cmdlet from the application server to verify that the SQL Server is now indeed listening on port 60000.
Test-NetConnection SqlServerName -port 60000
