What Is an SQL Server Port?

The main purpose of SQL Server ports is to receive and transmit data of a certain type. In this article, we are going to talk about what kinds of ports there are, what are the difference is between them, what is the default SQL port, and many other theoretical things. Moreover, here you will find a practical illustrated guide on how to connect to a port in dbForge Studio for SQL Server.

Contents

1. Introduction
   • What is TCP
   • What is UPD
2. The default port for SQL Server
   • Troubleshooting
3. Ports configuration
4. How to check what port SQL Server is running on
   • SQL Server Configuration Manager
   • Event Viewer
   • SQL Server Management Studio (SSMS)
5. Manage database connections in dbForge Studio
   • Create a new database connection
   • Modify an existing connection
   • Open a database connection
   • Close a database connection
   • Delete a database connection
   • View connection information
6. Conclusion

Introduction

When establishing a connection, the two endpoints (a.k.a. hosts) are identified according to their port numbers. IANA (Internet Assigned Numbers Authority) is the entity that allocates and registers certain port numbers for specific purposes. However, in practice, there are often cases of their unofficial use. The most frequently used ports are:

Port Number	Protocol	Purpose
80	TCP	To publish SQL Server Reporting Services (SSRS) reports 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	AlwaysOn
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

In modern computer networks, TCP and UDP protocols are the ones that are used most often. The former is a connection-oriented protocol, whereas the latter is a connectionless one. Let us take a closer look at TCP and UDP.

What is TCP

TCP stands for Transmission Control Protocol which is designed to manage the transmission of data on the Internet. It guarantees the integrity of the transmitted data by repeating the request in case of any data loss and eliminating duplicates. Since it is a connection-oriented protocol, the data can travel both in and out once the connection is established.

What is UDP

UDP stands for User Datagram Protocol. Unlike TCP, it allows a host device to transmit data to its recipient without a previously established connection. As convenient as it might sound, it is considered less reliable than the previously described protocol. The reason is that the destination port might be unavailable at the moment. Therefore, the data will not be transmitted seamlessly.

The default port for SQL Server

As soon as we install SQL Server, the default ports are configured for all the services. The client applications use both IP addresses and port numbers to connect to the server. There can be two kinds of ports:

1. Static Port always remains the same despite service or system restart. By default, SQL Server uses static TCP port number 1433 for the MSSQLSERVER instance. 1434 is used for UDP connections.

2. Dynamic Port changes upon each SQL Server restart. In order to use dynamic port allocation, specify zero as a port number in the network configuration. This way, each time the SQL Service restarts, it will request a new port number from the operating system and assign it to SQL Server.

Troubleshooting

When trying to connect SQL Server to a remote machine, you can encounter an error “1433 SQL server not listening”. To fix this issue:

1. Open SQL Server Configuration Manager
2. In the SQL Server Network Configuration menu, select Protocols for MSSSLSERVER
3. Make sure TCP/IP is enabled

Ports configuration

You do not have to stick to the default port at all times. Moreover, it might be even better to change it due to security reasons. In order to configure the port number:

1. Open SQL Server Configuration Manager
2. In the SQL Server Network Configuration menu, select Protocols for MSSSLSERVER
3. Right-click TCP/IP and select Properties
4. In the window that opens, click the IP Addresses tab
5. You can either set a different port number for each one of the IP addresses or simply set one for all of them
6. Click OK

The changes you have just made will not take effect until you restart the service:

How to check what port SQL Server is running on

As we have already mentioned, the SQL Server Database Engine listens on port 1433 for the TCP/IP connections, and port 1434 is used for the UDP connections by default. However, if you are working with more than one SQL Server instance at the same time, you will have the default port numbers. At this point, it might be rather challenging to keep track of all of them. Therefore, we would like to provide you with some tips on how to check what port the SQL Server is running on using different tools:

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

SQL Server Configuration Manager

Earlier in this article, we have already used SQL Server Configuration Manager to configure ports. In a similar way, we can also check the port number without changing it later:

1. Open SQL Server Configuration Manager
2. In the SQL Server Network Configuration menu, select Protocols for MSSSLSERVER
3. Right-click TCP/IP and select Properties
4. In the window that opens, click the IP Addresses tab
5. Check what port the SQL Server is currently running on

Event Viewer

Event Viewer is a Windows component that allows administrators to check the logs either on the local computer or on a remote machine.

1. Open Event Viewer
2. Expand Windows Logs
3. Right-click Application
4. Click Filter Current Log
5. In the window that opens, filter for events with Event ID 26022
6. Click OK

Now, you can only see the events associated with the SQL Server. By clicking each one of them, you will see a message containing the information on what IP and port the Server is listening on.

For example: “Server is listening on [ ‘any’ <ipv4> 1433]“, where:

• ‘any’ <ipv4′ means that the server is listening on any available IP of a fourth version.
• 1433 – is the port number the server is listening on.

SQL Server Management Studio (SSMS)

SSMS is one of the most popular tools to work with SQL Server. To check what port the service is listening on, we will be using this command:

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on', N'any', NULL, NULL, N'asc' 
GO

1. Copy the command above
2. Open SSMS
3. Click New Query
4. Paste the previously copied command into the SQL document
5. Click Execute

On clicking Execute, you will get a message containing the information on what IP and port the Server is listening on.

Manage database connections in dbForge Studio

With all its merits, SSMS can not always meet all the users’ requirements. There are endless plug-ins out there that can help customize the tool, however, it takes time to look for them and go through the installation process. Fortunately, there is a convenient GUI that instantly solves this issue. dbForge Studio for SQL Server has everything you need to set up your SQL development environment.

You can visually manage all the SQL Server connections through Database Explorer. Here, you will see recently used database connections, along with databases and database objects within those:

Create a new database connection

To open the Database Connection Properties window, do either of the following:

• Click New Connection on the Database Explorer toolbar
• Right-click Database Explorer and select New Connection on the shortcut menu

In the Database Connection Properties window that opens, specify the connection details:

• Server: choose the required server from the drop-down list or specify a new one.
• Authentication: choose the authentication mode from the drop-down list.
• Login and Password: type in the user credentials respectively.
• Allow saving password: select if you wish to allow saving the password for further authentication.
• Database: select the database from the drop-down list or enter the name of a SQL database you want to connect to.
• Connection Name: the name of the connection will be generated automatically from the hostname. If you wish to change it, simply type in a new one.
• Environment Category: this step is optional. You can choose the connection category (Development, Production, Sandbox, and Test) from the drop-down list.

Once all the properties have been specified, you can click Test Connection to make sure everything is set correctly. Alternatively, you can simply hit Connect without any double-checking. Rest assured that if there are any issues during the connection, dbForge Studio will provide you with an informative error message.

Modify an existing connection

If you would like to change anything in the connection that already exists, simply right-click it and select Modify Connection.

Then, the Database Connection Properties window opens and you can edit any field, just like during the creation of a database connection. Once done, either click Test Connection or Connect.

Open a database connection

There are three ways to open a connection that has been closed:

1. Double-click a connection in Database Explorer
2. Right-click a connection and select Open
3. Select a connection in Database Explorer and click

Close a database connection

To close an open connection, there are two ways:

1. Right-click a connection and select Close
2. Select a connection in Database Explorer and click

Delete a database connection

In order to delete a connection that you do not need anymore, give it right click and select Delete. To confirm the removal, click Yes in the dialog that opens.

View connection information

In Database Explorer, right-click a connection and select Properties. Alternatively, simply press F4. In the Properties pane that opens, you will see all the information about the connection.

Conclusion

In this article, we have been talking about SQL Server ports, their types, and why they are important for server connection. Moreover, we have discovered and looked into a convenient GUI tool that makes working with SQL database connections much easier and user-oriented. Download a fully-functional trial version of dbForge Studio for SQL Server for evaluation purposes.