The SQL Server default ports that the SQL Engine runs on is 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. For example, you’ll see this on multi-tenant SQL Server hosts, which is many SQL Server instances running on the same server which all need their own port to run on.
SQL Server runs some of its features on other common ports, such as the SQL Browser Service (1434) and Mirroring Endpoints (5022 or 7022). This article includes a more information on managing SQL Ports from a SQL DBAs perspective, as well as a guiding you on how to check which port number a SQL Server Instance is running on.
SQL Server Networking for DBAs
As a SQL Database Administrator, we need to ensure TCP Connections are enabled on our SQL Servers, and we may be responsible for creating the local firewall rule for SQL Server inbound traffic.
Occasionally as a SQL DBAs, we may also need to request network line of sight (Firewall Rule) changes to be carried out by Network teams for MSSQL host-to-host connectivity. This is done via a request form within larger organisations, and we need to include source & destination IP addresses within the request.
And wait, there’s more DBA responsibilities here. It’s our job to test the network connectivity to the database server, verify which ports SQL Server is using, and test it every time the network team asks us to do so. We’re all on the same team when trying to get network flow working successfully!
How to Check SQL Server Running Ports
To check the port your SQL Server instance is running on, open SQL Server Configuration Manager and navigate to the following:
– SQL Server Configuration > Protocols for MSSQL > TCP/IP Properties > 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 dill into the same window for each instance shown in the SQL Configuration Manager.
To view the SQL Server HADR Endpoint port number, run the following command:
-- Get SQL 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;
List of SQL Server Default Ports
SQL Server operates on several ports, each serving specific functions within the database ecosystem. Here’s a breakdown of some essential ports used by SQL Server:
1. Default SQL Server Port: 1433
This port is the default for the SQL Server Database Engine, facilitating client-server communication for database queries and transactions.
2. SQL Server Browser Port: 1434
The SQL Server Browser service listens on this port to provide information about SQL Server instances running on the server. It helps clients identify the appropriate port to connect to when multiple instances are present.
3. SQL Server Dedicated Administrator Connection (DAC) Port: 1434 (UDP)
The Dedicated Administrator Connection (DAC) is a special diagnostic connection that allows administrators to troubleshoot and perform maintenance tasks even when regular connections are not available. It typically uses port 1434 for UDP communication.
4. SQL Server Integration Services (SSIS) Port: 135
SQL Server Integration Services (SSIS) utilizes port 135 for various communication tasks related to data integration, ETL (Extract, Transform, Load) processes, and package execution.
5. SQL Server Mirroring Endpoint Port: Variable (default is 5022/7022)
SQL Server Mirroring involves the use of an endpoint to facilitate communication between the principal, mirror, and witness servers. While the default port for the mirroring endpoint is 5022, it can be configured to use a different port if needed.
These ports represent common and default ports utilized by various SQL Server services and features. All of the above can be configured to run on a custom ports, but most of the time you’ll probably find them running with SQL Server defaults ports.
Next in this post, we’ll explore how to identify the running port numbers for SQL Server instances for when it’s not the above ports.
Other Ways of Identifying Running Ports in SQL Server
Identifying the running port numbers for SQL Server instances is crucial for troubleshooting connectivity issues and configuring network settings. There are other ways of identifying SQL ports, including:
1. 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 SQL Server start-up. To view the Error Log:
– Open SQL Server Management Studio (SSMS).
– Connect to your SQL Server instance.
– Navigate to Management > SQL Server Logs and open the current Error Log.
2.. 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
Replace <InstanceName> with the name of your SQL Server instance.
3. Run SQL DMV Queries to Check Port Numbers
We can query Dynamic Management Views (DMVS) to retrieve information about SQL Server network configurations. As shared above in this post we can do this for the Mirroring Endpoint port number, but for the SQL Engine I’m not sure. People Online say the following query works, but it’s NULL / no rows for me:
-- Check SQL Server Engine Port Number SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL
Conclusion
The conclusion is, I hope this post was useful for you. If you enjoy this content, no need to subscribe, or do anything. Just read it. Thanks. Or, or more random tips from a SQL Server DBA, check out my MSSQL DBA Tips blog tag.
Leave a Reply