Configure MS SQL Server to Listen on a given port

Posted on 186 views

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

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-1024x284

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-1024x339

MSSQL_TCP_IP_Window-375x420

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-373x420

Step 4: Change MS SQL Service TCP Port

Click on IP Address Tab. Therein several IP addresses sections appear to be shown as IP1IP2, 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-372x420

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-1024x335

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-1024x449

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.

coffee

Gravatar Image
A systems engineer with excellent skills in systems administration, cloud computing, systems deployment, virtualization, containers, and a certified ethical hacker.