How To Configure MS SQL Server to Listen on a given port

By Kibet John -September 18, 20193833 0

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

SQL Config Manager Start

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:

SQL Network Configuration

SQL Protocols

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.

SQL TCP IP Properties

MSSQL TCP IP Window

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.

MSSQL Protocol No

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.

MSSQL TCP IP All Port

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.

MSSQL Protocol No2

Step 6: Enable TCP/IP

After the above steps are done, right-click on TCP/IP and select Enable as shown below.

MSSQL Enable TCP IP

Step 7: Restart MS SQL Service

Restart your MS SQL Instance. Hit Windows button and search for Services.

MSSQL Services

Under Services, look for SQL Server (your_instance).

Right-click on it and choose Restart.

MSSQL 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.