Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.448 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • August 30st, 2015
  • 3
  • Database DMVs and Catalog Views Hacks Maintenance SQL Server

How to identify the port used by the instance of SQL Server

Views: 30.187
Reading Time: 3 minutes

Hello people,
Good Morning!

In this post I will show how to identify the port used by the instance of SQL Server. As you may know, the default port for TCP / IP connections is 1433 and for UDP connections is 1434. However, if you have more than one installation on the same server, you can use these ports for only one instance, making it interesting to identify this information.

Identifying the port by reading SQL Server error logs

The SQL Server error log is a great place to learn what happens to the instance of SQL Server. Using the extended stored procedure xp_readerrorlog, we can easily get this information.

Transact-SQL
1
EXEC master.dbo.xp_readerrorlog 0, 1, N'Server is listening on', 'ipv', NULL, NULL, N'asc'

How to identify the port used by SQL Server - 1

Identifying the port by catalog views (DMV)

Another quick way to obtain the information is to consult system catalog views (DMV's), as examples below:

Transact-SQL
1
2
3
SELECT TOP 1 local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL

SQL Server - Identify the port with DMV dm_exec_connections

Using the sys.dm_server_registry DMV (Only from SQL Server 2008 R2):

Transact-SQL
1
2
3
4
5
SELECT value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE '%IPALL%'
AND value_name LIKE 'Tcp%Port%'
AND NULLIF(value_data, '') IS NOT NULL

Identifying the port via Windows registry

As practical as the first alternative would be to perform a Query T-SQL by querying Windows registry data through the extended stored procedure. xp_regread.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
DECLARE @Instancia NVARCHAR(50)
DECLARE @Porta VARCHAR(100)
DECLARE @RegKey_Instancia NVARCHAR(500)
DECLARE @RegKey NVARCHAR(500)
 
SET @Instancia = CONVERT(NVARCHAR, ISNULL(SERVERPROPERTY('INSTANCENAME'), 'MSSQLSERVER'))
 
 
-- SQL Server 2000
IF ( SELECT CONVERT( VARCHAR (1), (SERVERPROPERTY ('ProductVersion'))) ) = 8
BEGIN
    
    IF (@Instancia = 'MSSQLSERVER')
        SET @RegKey = 'SOFTWARE\Microsoft\' + @Instancia + '\MSSQLServer\SuperSocketNetLib\TCP\'
    ELSE
        SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @Instancia + '\MSSQLServer\SuperSocketNetLib\TCP\'
    
    EXEC master.dbo.xp_regread
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @RegKey,
        @value_name = 'TcpPort',
        @value = @Porta OUTPUT
    SELECT @@SERVERNAME AS Servidor, @Instancia AS Instancia, @Porta AS Porta
 
END
 
 
-- SQL Server 2005 ou superiores
IF ( SELECT CONVERT( VARCHAR (1), (SERVERPROPERTY ('ProductVersion'))) ) <> 8
BEGIN
 
    SET @RegKey_Instancia = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
 
    EXEC master.dbo.xp_regread
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @RegKey_Instancia,
        @value_name = @Instancia,
        @value = @Porta OUTPUT
 
    SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @Porta + '\MSSQLServer\SuperSocketNetLib\TCP\IPAll'
 
    EXEC master.dbo.xp_regread
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @RegKey,
        @value_name = 'TcpPort',
        @value = @Porta OUTPUT
    SELECT @@SERVERNAME AS Servidor, @Instancia AS Instancia, @Porta AS Porta
 
END

How to identify the port used by SQL Server - 4

Identifying the port with SQL Server Configuration Manager

Another alternative to this if you have access to the server is by using SQL Server Configuration Manager.
To open this utility, simply use the Start Menu> Programs> Microsoft SQL Server 2008 R2 (Or your version)> Configuration Tools and open the app SQL Server Configuration Manager.

A faster alternative is to open the Run menu, and type SQLServerManager10.msc (10 represents the version of your SQL Server)

How to identify the port used by SQL Server - 2

How to identify the port used by SQL Server - 3

Identifying the port with the server event viewer

Finally, we can check the port used by our instance by looking at the Application event log.

To open this app you must go to: Control Panel> Administrative Tools> Event Viewer or open the Run menu and type: eventvwr.msc

In the left panel, expand the "Windows Log" menu and then check the "Application" option. In the right panel, click on the option “Filter current log” and filter by the Event ID 26022

How to identify the port used by SQL Server - 5

How to identify the port used by SQL Server - 7

How to identify the port used by SQL Server - 6

That's it folks!
To the next!

tags: portportsqlsql serverUsedused

You may also like ...

  • Introduction to SQL Common Language Runtime (CLR) in SQL Server

  • SQL Operations Studio - 64-bit Management Studio running on Windows, Linux and Mac

  • SQL Server - How to know how long a specific database has been online

  • Next Checking a user's permissions in SQL Server
  • Previous The undocumented SQL Server extended procedures

Comments

  • Comments3
  • Pingbacks0
  1. Rafael Fernandes said:
    15 April 2020 13 gies: 40

    Hello, good afternoon! I ran Xp_regread and the result came like this:

    Server Instance Port
    DC14SQL14 \ INST01 INST01 NULL

    I don't have access to the bank's Master, so I can't do it in other ways…

    Any other ideas?

    Reply
  2. lorduakiti2 said:
    28 from 2019 to 02 at 18: XNUMX

    Excellent post, helped a lot.

    Reply
    • Dirceu Resende said:
      18 from 2019 from September to 23: 01

      Thanks ?

      Reply

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.