SQL Server Port - Details Guide

The purpose of the SQL Server port is to transmit and receive data. In this tutorial, we will see which ports are available by default in SQL Server and what the differences between them are. Continue reading to learn more.

Table of Contents

What is a Port in SQL Server

A Port is defined as an endpoint of service for communication purposes. It may be tied to a particular application or service.

After installing the SQL Server, we can see the default port for the SQL Server Services. Each client application uses a combination of IP addresses and port numbers to connect to the SQL Server.

Why SQL Ports are Important

Our devices communicate standardly from end to end. We can also use multiple services and handle multiple processes using different ports, such as the SSIS port for 3882 and the database engine port 1433.

What is TCP and UDP in SQL Server

TCP—The TCP term stands for transmission control protocol, which is particularly designed to manage the transmission of data on the Internet.

It ensures the connection of the transmitted data by repeating it in case of data loss and eliminating duplicates. Since it is a wired connection protocol, once the connection is made, the data can travel in and out.

UDP – UDP stands for user datagram protocol. It allows a device to transfer the data to the recipient without an existing connection.

Compared to the previous protocol, it is thought to be less dependable. Because there’s a chance the destination port isn’t available right now, as a result, the data transmission won’t be flawless.

Below are the most frequently used ports in SQL Server.

Port Number	Protocol	Purpose
80	TCP	To publish SQL server reporting services using HTTP
135	TCP	T-SQL debugging, WMI, MSDTC, Agent file copy
443	TCP	To publish SSRS reports using HTTPS
500	UDP	IPSec to encrypt connections
1433	TCP	Database engine default instance
1434	TCP, UDP	DAC and the SQL Browse
2382	UDP	SQL Server Analysis Services with dynamic ports
2383	TCP	SQL Server Analysis Services (SSAS)
2725	TCP	SQL Server Analysis Services (SSAS)
3343	UDP	Cluster network driver
3882	TCP	SQL Server Integration Services (SSIS)
4022	TCP	SQL Broker Service
4500	UDP	IPsec
5022	TCP	Always On
7022	TCP	Database Mirroring
from 1024 to 5000	TCP	Dynamic ports for named instances
from 5000 to 5099	UDP	Clusters
from 8011 to 8031	UDP	Cluster internode
from 49152 to 65535	TCP	More dynamic ports for named instances

SQL Server Default Port

Once SQL Server is installed, ports are configured by default. The client application uses IP addresses and port numbers to connect to the server. There are two types of ports;

Static Port
Dynamic Port

Static Port—The static port will always be the same, rather than being restarted by the service or system. SQL Server uses static TCP port number 1433 for the MSSQLSERVER instance, and 1434 is used for UDP connections.

Dynamic Port—The dynamic port will be changed upon each SQL Server restart. To use dynamic port allocation, specify zero as the port number from the network configuration. Each time SQL Service restarts, it will request a new port number from the OS and assign it to SQL Server.

To find the default port number in SQL Server Management Studio, follow the simple steps below.

Open SQL Server Configuration Manager. Right-click on TCP/IP and click Properties.

Here, click IP Addresses, then the default TCP Port will be 1433.

This is how we have to check the default port in SQL Server Management Studio.

Port Configuration

You do not need to depend only on the SQL default port. You can also change the port number for security reasons. Below are the points to change the Port configuration in SQL server management studio.

Open SQL Server Configuration Manager. Right-click on TCP/IP and click Properties.

Click IP Addresses and set the same port number for each IP address or a different port number for each IP address and click OK.

The changes will be made once you click OK. This is how we have to give port numbers in SQL Server management studio.

Check What SQL Server Port is Running

To check what SQL Server port is currently running, follow the below steps. We can use the three tools to check the SQL Server Port.

SQL Server Configuration Manager
Event Viewer
SQL Server Management Studio (SSMS)

SQL Server Configuration Manager

Once the port number is set, follow the below steps to see the current port number in SQL Server.

Open SQL Server Configuration Manager. Right-click on TCP/IP and click Properties.

Click IP Addresses, see the port number under the IP address, and click OK.

By doing this, you will get to know the port number in SQL Server Management Studio.

Event Viewer

It is a Windows component that allows the admin to check the logs on a computer or a remote device.

Open Event Viewer from the system, expand Windows Logs, Right-click Application, then click Filter Current Log.

Now, the Filter current log page will open. Given event ID as 26022 and click OK.

The event ID will be updated on the application’s page.

SQL Server Management Studio

SSMS is a familiar tool for working with SQL Server. We will use this command to check what port the service is listening on.

Copy the below query.

xp_readerrorlog 0, 1, N'server is listening on'
Go

Open server management studio.
Click New Query.
Paste the copied query into the SQL document.
Click Execute.

You will see the below output with the port number for the above query.

Conclusion

This tutorial discusses SQL Server ports, their types, and the reasons behind their significance for server connections. Additionally, we have found and checked a handy graphical user interface tool that greatly simplifies and streamlines the process of working with SQL database connections.

SQL Server Port – Details Guide