SQL Port

Introduction of SQL Port

In networking, a port is a docking endpoint through which communication is established between the server and client and information flows from a program in the user’s computer to servers on the internet. In SQL also, we have multiple types of database engines such as SQL SERVER, POSTGRESQL, MYSQL etc. which communicate through ports. When a port number is used in combination with an IP address, it determines the direction of flow of information. These ports are part of the Transport Layer and are usually of two types TCP(Transmission Control Protocol) and User Datagram Protocol(UDP). Each port in the SQL database engine has a unique service to perform. For example, TCP 1433 is the default port number in SQL Server, and it is used for managing SQL instances over the network. While PostgreSQL uses TCP 5432 to perform the same task.

You must be wondering that I get it that there are designated ports for communication, but who gets to decide which service will get what port number? These port numbers are provided to services by Internet Assigned Numbers Authority (IANA). It is also responsible for managing the uses of these ports in the networking world.

In this article, we will attempt to illustrate how you can check the port number on which your SQL database engine is running, how it can be changed, and a few other things. For this article, we have written examples for PostgreSQL and SQL Server. But once you get the hang of it in any database engine, it’s quite intuitive in others.

Examples of SQL Port

Following are the examples are given below:

Example #1

How to find the port number to which the PostgreSQL database server is listening and change it to a new port number?

We can find out the port number and other details of the port to which the server is listening using a SELECT statement on the pg_settings table as shown below.

SELECT name, setting, category , short_desc
FROM pg_settings
WHERE name = 'port';

In this example, we have just fetched the port number, category, and description from the table. You can use SELECT * instead and fetch all the details. Now, observe the port number ‘5432’ and the short_desc corresponding to it. TCP port 5432 is the default to which the server listens to. For the curious ones, you can find more details on networking in SQL database server from the postmaster file or use the query given below.

select *
from pg_settings
where context = 'postmaster';

You will set some output, as shown in the image above.

Changing Port Number to A Different Port Number.

Step 1: Open SQL shell and write the following commands in the shell.

locate postgresql.conf

Step 2: The last step gave you the current port number. Next, to change it, write the following commands in the shell and restart your postgres server.

port = 5433
listen_addresses='*'

The above mentioned command will change the port number to ‘5433’ corresponding to all the IP addresses. You can get specific here by mentioning a specific IP address instead of ‘*’.

Example #2

How to find the port number to which the MS SQL server is listening and how to change it?

In SQL server, we have SQL Server configuration manager where we have all the configuration details saved. If you want to see what port number your database server is listening to follow the following steps then.

Step 1: Open SQL Server configuration manager. (If you are not able to find it directly look for it in the search tab)

 Step 2: Once you have opened your SQL Server configuration manager, open SQL Server Network Configuration as shown in the image below.

Step 3: After selecting the SQL Server Network Configuration, click on Protocols for SQLExpress. A menu as shown in the image below will appear. Select TCP/IP from it. (If it’s not already enabled, enable it.)

Step 4: When you will select from the Protocols menu, the TCP/IP dialog box as shown below will appear. There is no port number mentioned in this case because it is running on dynamic TCP. TCP Dynamic Port = ‘0’ means that your server is listening on dynamically allocated port numbers. TCP Port is the field that gives us the desired port number.

 

Step 5: If you wish to change the TCP port number then provide a new port number in the blank space corresponding to IP address 127.0.0.1, this corresponds to your localhost. You may make a change in IPALL if you want to change it for all the addresses.

Step 6: Once you are done with making all the necessary changes, click on Ok. An alert pop-up, as shown below, will appear. Click on OK and restart your SQL Server.

And you are done changing the port number.

Conclusion

In this post, we have covered how to find the port number to which the SQL database server is listening to and have also learned to change it to a specific port number. A SQL port is basically a TCP port that acts as the endpoint of communication between your local computer and database server on the network.

Recommended Articles

This is a guide to SQL Port. Here we also discuss the introduction and explanation of SQL port along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. SQL DECODE()
2. MySQL GROUP BY
3. SQL Users
4. MySQL Interval