SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Steps to Configure SQL Server to Listen On A Specific TCP Port

Management of the database is very much essential in today’s date and for doing this, there is a need to have a platform. SQL Server is one such platform to manage the database in a relational manner. It was developed by Microsoft and operated by many users, especially in different organisations, for the management of data. This database server is a software, which is for storing as well as for retrieving data. Moreover, SQL server has two port allocation one is static and another is dynamic. The default or static SQL Server listens on a fixed TCP port i.e., 1433. However, a demand occurs in which the server users want to configure a server to listen on a specific TCP port (SQL server configuration manager). In the following section, we will discuss the difference between a Static and Dynamic port and then learn how to configure a server to listen on a specific TCP port?

Difference Between Static and Dynamic Port

If a user configure an instance of SQL Server for the use of Static port, this server listens on static port that is previously specified. By default, SQL Server listens on TCP port 1433. SQL Server clients will have to send all the requests specifically to the static port, which is listen by SQL Server. However, if the server is configured for the static port or for any another program running on the system, then working of the defined static port gets started. Now, SQL Server will not listen on that specific static port. This type of port can be changed into Dynamic port, as per requirement.

When SQL Server is configured for other TCP port (which is different from the default one) then it is called Dynamic port. When a user starts the instance of SQL Server in Dynamic port allocation process, the port is already set to “0”. This is the reason due to which SQL Server requests free port value from an operating system. Randomly, a user can put any port number to the SQL Server and then the server starts listening on allocated port.

Need to Configure a Server to Listen on a Specific TCP Port

Following are the reasons due to which users alter the default port number:

  • Users change this port settings mainly due to security purposes.
  • The another reason to change Static to Dynamic is because of requirement of a client application.

Method to Change SQL Server Static Port to Dynamic

Following are the steps, which a user needs to follow for changing the SQL Server port from Static to Dynamic:

  • Firstly, you need to run the SQL Server Configuration Manager on your system
  • Then, click on “SQL Server Network Configuration”
  • Now, choose an instance from the list, which you want to configure for listening on a specific port.
  • Once you make this choice of changing the port, make a right-click on the TCP/IP protocol. After that, select “Properties” option
  • Click on the tab where IP address is given
  • Now, you need to mention the port number, which you want to put instead of 1433. You can use any number, as per your choice.
  • Finish the task by choosing “SQL Server Services >> SQL Server and restart the machine

Note: You first need to remove “0” for turning off the dynamic port number.

Conclusion

SQL Server is very useful means when it comes to management of bulk amount of data either for an organisation or for a user. It manages the database in a well-structured form. In this write-up, we have discussed the difference between dynamic and static ports along with different needs to change port from static to dynamic. We have also learned that how to configure a server to listen on a specific TCP port.

Zora's SQL Tips

Hi! I am Zora Stalin, an IT geek and a passionate learner of technology. Besides my job as an Information Technology Analyst, I love searching and sharing new things that excite me help for others.

Comments

Leave a comment on the original post [sqlserveroverview.blogspot.com, opens in a new window]

Loading comments...