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.

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 NumberProtocolPurpose
80 TCPTo publish SQL server reporting services using HTTP
135TCPT-SQL debugging, WMI, MSDTC, Agent file copy
443TCPTo publish SSRS reports using HTTPS
500UDPIPSec to encrypt connections
1433TCPDatabase engine default instance
1434TCP, UDPDAC and the SQL Browse
2382UDPSQL Server Analysis Services with dynamic ports
2383TCPSQL Server Analysis Services (SSAS)
2725TCPSQL Server Analysis Services (SSAS)
3343UDPCluster network driver
3882TCPSQL Server Integration Services (SSIS)
4022TCPSQL Broker Service
4500UDPIPsec
5022TCPAlways On
7022TCPDatabase Mirroring
from 1024 to 5000TCPDynamic ports for named instances
from 5000 to 5099UDPClusters
from 8011 to 8031UDPCluster internode
from 49152 to 65535TCPMore 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.
Default Port SQL
  • Here, click IP Addresses, then the default TCP Port will be 1433.
Default SQL Port

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.
SQL Server Port
  • 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.
Server Port SQL

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.
SQL Server Configuration Manager
  • Click IP Addresses, see the port number under the IP address, and click OK.
Server Port SQL

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.
MSSQL Server Port
  • Now, the Filter current log page will open. Given event ID as 26022 and click OK.
SQL Server Port number
  • The event ID will be updated on the application’s page.
MSSQL Default Port

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.

Default SQL Server Port

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.

You may also like to read: