June 17, 2019 by Rajendra GuptaThis 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.
- 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
- 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.
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
1
> mofcomp "%programfiles(x86)%\Microsoft SQL Server\150\Shared\sqlmgmproviderxpsp2up.mof"
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
- 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
- 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.
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.
1 2 3 | EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC' GO |
You get the detail of the SQL Server port on which current SQL Server instance is configured.
- 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.
1 2 3 4 5 6 7 8 | DECLARE @portNumber NVARCHAR(10); EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll', @value_name = 'TcpDynamicPorts', @value = @portNumber OUTPUT; SELECT [Port Number] = @portNumber; GO |
We can use @Value_name=’ TcpPort’ to get detail about the static port.
1 2 3 4 5 6 7 8 | DECLARE @portNumber NVARCHAR(10); EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll', @value_name = 'TcpPorts', @value = @portNumber OUTPUT; SELECT [Port Number] = @portNumber; GO |
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.
1 2 3 4 | SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID; GO |
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.
Specify the event ID 26022 and click OK
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.
In the following screenshot, we can see an entry of SQL Server Port in the 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.
- Author
- Recent Posts
Rajendra Gupta
Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023
Related posts:
- Overview of SQL Server Aliases
- Overview of SQL Server Startup Parameters for the SQL Database Engine Service
- SQL Server network configuration
- Overview of Service Principal Name and Kerberos authentication in SQL Server
- SQL Server 2019 overview and installation
