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
- Introduction
- The default port for SQL Server
- Ports configuration
- How to check what port SQL Server is running on
- Manage database connections in dbForge Studio
- 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:
- Open SQL Server Configuration Manager
- In the SQL Server Network Configuration menu, select Protocols for MSSSLSERVER
- 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:
- Open SQL Server Configuration Manager
- In the SQL Server Network Configuration menu, select Protocols for MSSSLSERVER
- Right-click TCP/IP and select Properties
- In the window that opens, click the IP Addresses tab
- You can either set a different port number for each one of the IP addresses or simply set one for all of them
- 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:
- Open SQL Server Configuration Manager
- In the SQL Server Network Configuration menu, select Protocols for MSSSLSERVER
- Right-click TCP/IP and select Properties
- In the window that opens, click the IP Addresses tab
- 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.
- Open Event Viewer
- Expand Windows Logs
- Right-click Application
- Click Filter Current Log
- In the window that opens, filter for events with Event ID 26022
- 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- Copy the command above
- Open SSMS
- Click New Query
- Paste the previously copied command into the SQL document
- 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:
- Double-click a connection in Database Explorer
- Right-click a connection and select Open
- Select a connection in Database Explorer and click
Close a database connection
To close an open connection, there are two ways:
- Right-click a connection and select Close
- 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.
