SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Here is one of the very common interview questions I often ask people –

Q: What is the default Port SQL Server running on?

A: 1433 for TCP/IP and 1434 for USD connection.

I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.

Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?

A: (I have yet to get the correct answer for this one in interview).

Solarwinds

Because of the same reason, I have decided to blog about this.

Here are different ways one can figure out on which port the second instance of SQL Server is running.

Method 1: using xp_readerrorlog

Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

The above query will return results something very similar to the following:

SQL SERVER - Find Port SQL Server is Listening - Port SQL Server is Running xpport

Method 2: Configuration Properties

Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager

SQL SERVER - Find Port SQL Server is Listening - Port SQL Server is Running xpport2

Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.

SQL SERVER - Find Port SQL Server is Listening - Port SQL Server is Running xpport3

Now over here when you scroll down you will notice Port Details.

SQL SERVER - Find Port SQL Server is Listening - Port SQL Server is Running xpport4

It is that easy to find the port of the second instance of SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Error – Resolution – Could not allocate space for object in database because the filegroup is full
Next Post
SQL SERVER – What is the Difference Between Latches and Locks

Related Posts

13 Comments. Leave new

  • Imran Mohammed
    March 16, 2014 8:42 pm

    Slight correction. It’s not 100 % true that first instance takes default port of 1433. What you said is true If and only if the first instance is a default instance. If we happen to install a named instance first on server, SQL server dynamically assigns port # other than 1433 (shown in your example) which can be forced to listen on 1433, if it’s not being used by other SQL instance.

    Even if you install a named instance first and then install default instance, SQL server will still assign 1433 for the default instance irrespective when in sequence was it installed ( first or second ).

    So by default, default instance 1433 port # and named instance take port# other than 1433.

    ~ IM.

    Reply
  • It’s also possible to get the portnumber by reading the registry with my scirpt below:

    DECLARE @tcp_port nvarchar(5)

    if @@SERVICENAME = ‘MSSQLSERVER’
    BEGIN
    EXEC xp_regread
    @rootkey = ‘HKEY_LOCAL_MACHINE’,
    @key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’,
    @value_name = ‘TcpPort’,
    @value = @tcp_port OUTPUT
    END
    ELSE
    BEGIN
    declare @keyname nvarchar(500)
    set @keyname = ” + ‘Software\Microsoft\Microsoft SQL Server\’ + @@SERVICENAME + N’\MSSQLServer\SuperSocketNetLib\TCP’

    EXEC xp_regread
    @rootkey = ‘HKEY_LOCAL_MACHINE’,
    @key = @keyname,
    @value_name = ‘TcpPort’,
    @value = @tcp_port OUTPUT

    END

    select @tcp_port

    Reply
  • You are right, you can get the port by using the method you mentioned, However In the above example the port shown is dynamic and not fixed, so whenever the server restart, it will change again. It would be good you should assign a fixed port to the instance, so that people across different networks can connect using the same fixed port.

    Reply
  • the following command works also :

    SELECT DISTINCT
    local_tcp_port
    FROM sys.dm_exec_connections
    WHERE local_tcp_port IS NOT NULL

    Reply
    • Correct. you may want to add another filer ” and net_transport = ‘TCP'”

      SELECT DISTINCT
      local_tcp_port
      FROM sys.dm_exec_connections
      WHERE local_tcp_port IS NOT NULL
      and net_transport = ‘TCP’

      Reply
  • How does SQL Server Management Studio know the port number(s) of different instances? For example, the “Browse for Servers” dialog finds instances of SQL Server on the network (i.e. “Network Servers” tab) and you’re able to connect to any instance, including those using non-default ports (as is the case with multiple instances).

    Reply
  • rezaulhoque123Reza
    November 10, 2015 8:22 pm

    U know what, YOU ROCK. Whenever I find this site in google, I always find the right answer.

    Reply
  • If I only have one default instance(MSSQLSERVER), can I change it to dynamic port?

    Reply
  • Dear Pinal,

    For Log Shipping and Windows Clustering port no 135 , 443, 445, 137, 138 are mandatory .

    These are known and default ports used by SQL Server and Windows server and clustering
    From security point of view what are alternative ports which can used instead of default ports or is there any other solution for the same.

    Reply
  • What if I have multiple SQL Servers? (Want to make a report)

    Reply
  • Thank you, it really helped me

    Reply
  • How to set (or preserver) the port porgramatically? Sql Code? When I restrat SQl Instance changes the port, so I need to change conectionstring in every pc software :(. Sql Server 2014 Express I have try to set manually the previous port when in chages but at restarting sql changes again :(, How make it stable?

    Reply

Leave a Reply

Menu