Rajendra Gupta
Get SQL Server Port using Windows Event viewer

Overview of SQL Server Ports

June 17, 2019 by

This article is useful for a beginner in SQL Server administration and gives insights about the SQL Server Ports, the methods to identify currently configured ports.

Introduction

We can define the port as an endpoint of service for communication purposes. It might bind to a particular application or service. Once we install SQL Server, it configures default ports for SQL Server services. Each client application uses the combination of IP addresses and port number to connect to SQL Server.

We can have two kinds of SQL Server Ports in SQL Server.

  1. Static Port: A static port is always bound to a service or application. It does not change due to a service or system restart. By default, SQL Server uses static TCP port number 1433 for the default instance MSSQLSERVER. If you configure SQL Server to use a static port other than the default port, you should communicate it to the clients or application owners to specify in the connection string
  2. Dynamic Port: You can configure SQL Server to use a dynamic port. If you use dynamic port allocation, you specify port number zero in the network configuration. Once SQL Service restarts, it requests a free port number from the operating system and assigns that port to SQL Server.

    As you know, Application uses a combination of SQL Server IP address and port number, you might think of a question – How will an application know the port number for connecting to SQL Server?

Once the operating system allocates a dynamic SQL Server Port to SQL Server, it writes that port number in the Windows registry. SQL Server Browser service uses UDP static port 1434. It reads the registry for the assigned TCP port. SQL Server client library connects and sends a UDP message using port 1434. SQL Server Browser service gives back the port number of a specific instance. An application can connect to SQL Server using that dynamic SQL Server port. SQL Server default instance uses the static port; therefore, SQL Server Browser does not return port for the default instance.

In most of the cases, SQL Server uses the same dynamic the SQL Server Port upon restart of the SQL Service as well. Suppose you stopped SQL Services and operating system allocated the dynamic port number (previously assigned to SQL) to another service, SQL Server gets another dynamic port assigned to it.

SQL Browser service is essential for the named instances with dynamic port allocation. It should be in running status for application to query and get the port details.

Check SQL Server Port Number

In this section, we will check a different method to check for the SQL Server Port number.

Method 1: SQL Server Configuration Manager:

It is the most common method to find the SQL Server Port number.

  • Step 1:

    Open SQL Server Configuration Manager from the start menu. In case you have multiple SQL Server versions you might get an error message while opening SQL Server Configuration Manager:

    Cannot connect to WMI provider. You do not have permission or the server is unreachable

    In order to fix it, open the administrative command prompt and execute the following command

    In this command, change the SQL Server version

    • SQL 2008 – 100
    • SQL 2012 – 110
    • SQL 2014 – 120
    • SQL2016 – 130
    • SQL 2017 – 140
    • SQL 2019 – 150

    SQL Server Configuration Manager error

  • Step 2:

    Go to Network Configuration, click the SQL instance for which you want to check SQL port

  • Step 3:

    It opens the protocols list. Right click on TCP/IP and properties

    Check TCP IP port

  • Step 4:

    Click on IP Addresses and scroll down to IPAll group. You can see TCP dynamic SQL ports and TCP port in the following screenshot.

    Check static and dynamic SQL Server port

    TCP dynamic ports value shows that we are using dynamic ports configuration. The Current assigned TCP dynamic SQL port is 51688.

    If we want to use a static port, remove the dynamic port value and specify a static port in the TCP port. This port number should be unique and not being used by other applications. You need to restart SQL Services to make this change effective

    • Note: You should change the SQL Server Port configuration using the SQL Server Configuration Manager only.

Method 2: SQL Server Error Logs:

SQL Server logs an entry in the SQL Server Error logs on each restart of SQL Services. We can use extended stored procedure xp_readerrorlog to filter the error log using a particular keyword.

The following query uses extended stored procedure xp_readerrorlog to check for the Server is listening on a keyword.

You get the detail of the SQL Server port on which current SQL Server instance is configured.

SQL Server Error Logs

  • Note: If you are recycling error logs regularly, you might not be able to find SQL Server Port using this method.

Method 3: Get SQL Server Port from the registry using xp_instance_regread:

We can use an extended stored procedure to get the SQL Port value. Execute the following code in SSMS to get a dynamic port for the currently connected SQL instance.

Get SQL Server Port from registry using xp_instance_regread

We can use @Value_name=’ TcpPort’ to get detail about the static port.

Method 4: Get SQL Server Port from using sys.dm_exec_connections DMV:

We can use sys.dm_exec_connections DMV to check for the TCP port of connected SQL Server instance. We use the @@SPID variable to return the current session SP ID.

Get SQL Server Port using DMV

Method 5: Get SQL Server Port from using the Windows Event Viewer:

We can use Windows Event Viewer as well to check for the SQL Server Port details. Once we restart SQL Services, Windows Event Viewer also gets an entry for the SQL Port. Windows Event Viewer might have a large number of events. It is better to filter event logs for event id 26022.

Go to Run and type eventvwr. It opens the windows event viewer console. Click on – Filter Current Log.

Get SQL Server Port using Windows Event viewer

Specify the event ID 26022 and click OK

Windows Event viewer filter events

The Event viewer shows events for all installed SQL Servers in the servers. You can look for a specific instance and check for the port number.

You can also select a specific SQL instance in the drop-down list of Event Sources.

Windows Event viewer filter events

In the following screenshot, we can see an entry of SQL Server Port in the Windows event viewer.

check SQL Server ports in Windows Event viewer

Conclusion

In this article, we explored and identify methods to check SQL Server Ports and change the static and dynamic port configuration. If you have any comments or questions, feel free to leave them in the comments below.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views