SQL Server Default Ports

The SQL Server default ports that the SQL Engine runs on is port 1433. This is a useful thing to know if managing SQL Server databases, however SQL Server can be configured to run on a variety of port numbers, which I’m hoping to help explain in this post.

As a SQL DBA, you will often need to open network flow on Firewalls, whether it’s the local Windows Firewall or requesting network changes through a network team. It’s part of our job to ensure proper connectivity happens to and from our SQL Servers. When we are updating network rules, we must know the source and destination IP subnets, along with the port numbers needed.

In my experience, the SQL Server Engine generally runs on port 1433, I’d say about 95% of the time it’s running on this port. The other server configurations are usually running with multiple instances on the same server, or some outliers do use custom ports. In the end though, we need to confirm the running port numbers before we get network line of sight opened.

---

SQL Server Ports Aren’t Always the Same

SQL Server runs using several ports, each serving specific functions within the database ecosystem. Here’s a breakdown of some essential ports used by SQL Server:

SQL Server Database Engine:
The core SQL Server engine service runs using port port 1433 as described above, however this can be set to any port number depending on who configured it.

SQL Server Integration Services (SSIS):
SSIS requires port 135 for various communication tasks related to data integration, ETL (Extract, Transform, Load) processes, and package execution. This port cannot be changed.

SQL Server Browser & Dedicated Administrator Connection (DAC):
The SQL Server Browser Service & DAC often run using port 1434. As a DBA, these are not typically used or relied on much.

Database Mirroring Endpoints:
If you’re running with Always On Availability Groups (AGs) or Mirroring, the Mirroring Endpoint will usually operate using port 5022, and sometimes can be 7022.

Multi-Tenant SQL Servers:
If you have many SQL Server instances running on the same server, for each instance installed you’ll need a unique port assigned. This could be any port number that you and/or team decide.

Checkout the Microsoft Docs for a better and more detailed list of ports used by SQL Server.

---

How to Check Running SQL Server Port Numbers

Get SQL Engine Port Number

To check the port your SQL Server instance is running on, follow these steps:

1. Open SQL Server Configuration Manager.
2. Expand SQL Server Network Configuration and click on Protocols for MSSQLSERVER.
3. Right click TCP/IP Properties and select IP Address Tab.

This will show the port number this SQL Server Service is using. If there’s more than one instance installed on this server, you can navigate to the same window for each instance shown in SQL Server Configuration Manager.

Get Mirroring Endpoint Port Number

To view the SQL Server HADR (Mirroring) Endpoint port number, run the following command:

-- Get Mirroring Endpoint Info
-- Includes mirroring_endpoint state & port
SELECT
    e.name AS Endpoint_Name,
    e.state_desc AS Endpoint_State,
    e.type AS Endpoint_Type,
    e.type_desc AS Endpoint_Type_Description,
    d.state AS Mirroring_State,
    d.state_desc AS Mirroring_State_Desc,
    tcp.port AS Port_Number,
    d.is_encryption_enabled AS Encryption_Enabled,
    d.encryption_algorithm_desc AS Encryption_Algorithm_Desc,
    d.certificate_id AS Certificate_Id
FROM sys.endpoints e
JOIN sys.database_mirroring_endpoints d ON e.endpoint_id = d.endpoint_id
JOIN sys.tcp_endpoints tcp ON e.endpoint_id = tcp.endpoint_id;

---

Other Ways of Identifying Running Ports for SQL Server

There are other ways of identifying SQL ports, including the following:

Check Listening Ports on Windows
Checkout my other blog post which shows you how to check Listening Ports on Windows.

Review SQL Server Error Logs
SQL Server Error Log records various events and messages, including information about the SQL Server instance’s port number. You can find the port number logged during the SQL Server Service start-up. To view the Error Log:
1. Open SQL Server Management Studio (SSMS).
2. Connect to your SQL Server instance.
3. Navigate to Management > SQL Server Logs and open the current Error Log.

Run SQL DMV Queries to Check Port Numbers
We can query DMVs like sys.dm_exec_connections to retrieve information about SQL Server network configurations. For example:

-- Check SQL Server Engine Port Number
SELECT DISTINCT local_tcp_port 
FROM sys.dm_exec_connections 
WHERE local_tcp_port IS NOT NULL 

Check Registry for Port Number
SQL Server port numbers are also stored in the Windows Registry. The port number can be found under the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp

---

Testing Connectivity to Remote Server Ports with PowerShell

To test if network flow on a port is open between severs on Windows, we can run the following:

# Test remote port is open
Test-NetConnection -ComputerName lab-sql1.whyte.net -Port 1433

# Same as above command using alternative syntax/ip
tnc 172.31.18.100 -port 1433

Checkout my other post for more information on testing connectivity to remote ports using PowerShell.

I hope all this was useful, cheers!