Introduction
How do I configure an instance of MS SQL Server Database Engine to listen on a specific fixed port? This guide will walk you through the process of configuring your SQL Server Database Engine to listen on a port that you choose using SQL Server Configuration Manager. Let us begin:
Step 1: Open Configuration Manager
Hit Windows Keyboard key and search for SQL Server Configuration Manager as shown in the screenshot below
Step 2: Server Network Configuration
Once SQL Server Configuration Manager is open, click on the drop-down list of SQL Server Network Configuration and choose “Protocols for [name of your SQLServer]”. That will reveal three options on the right panel as shown on the image below:
Step 3: Unset Listen All
Right-click on the TCP/IP and choose Properties which will pop a new TCP/IP Properties window as shown below.
On the Window, there are two Tabs on the top left corner, that is Protocol and IP Address. Depending on which IP and port you would wish your Database Engine to listen on, the settings on the Protocol Tab will help you. By default, listening is set to All on the “Listen All” part. If you would wish specific IPs and Port to be used, set this value to No as shown below.
Step 4: Change MS SQL Service TCP Port
Click on IP Address Tab. Therein several IP addresses sections appear to be shown as IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. In case you left “Listen” to “All” in the Protocol Tab, then head over to the IPALL section and set the TCP Port to your desired number and delete the 0 against TCP Dynamic Ports as shown below.
Step 5: Configure Database Engine Port
In case you set “Listen All” part to No, head over to IP1, change “Enabled” to “Yes“, type in the IP Address eg 10.20.2.29 here, delete the 0 in TCP Dynamic Ports and key in the Port you would wish the Database Engine to Listen from. Check the screenshot below for more visual details. After you are done keying in the necessary data, click Apply and OK.
Step 6: Enable TCP/IP
After the above steps are done, right-click on TCP/IP and select Enable as shown below.
Step 7: Restart MS SQL Service
Restart your MS SQL Instance. Hit Windows button and search for Services.
Under Services, look for SQL Server (your_instance).
Right-click on it and choose Restart.
Your SQL Server is now listening on the Port you have specified. You only need to enable the port on Windows Firewall if it was not allowed yet.
