Tuesday, July 3, 2012

How to find which port SQL Server instance currently listening?


There are different ways to find the current port that SQL Server instance using (compare to default port:1433) and I listed some of them in below:
1)Go to SQl Server error log and find thr entry for instance:
SQL server listening on 127.0.0.1: 1433.
2)Open the Sql Server Network Utility(in 2000) or SQL configuration manager(2005 and higher) and click on properties for TCP/IP in instance
3)Issue netstat -an from an MS-DOS command window produces the following results showing three established connections for SQL Server
This example uses 157.54.178.40 as the IP address of SQL Server and 157.54.178.30 as the client IP address. The ports opened by the client are 1749, 1750, and 1751 respectively.

Proto   Local Address          Foreign Address                State
 TCP    157.54.178.40:1433     0.0.0.0:0                       LISTENING
 TCP    157.54.178.40:1433     157.54.178.30:1749     ESTABLISHED
 TCP    157.54.178.40:1433     157.54.178.30:1750     ESTABLISHED
 TCP    157.54.178.40:1433     157.54.178.30:1751     ESTABLISHED
4) Check Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

and you will see TCP Port as one of the entries and it’s value represents the port number for that instance.
In case you are using a named instance, then the registry entry will be
: HKLM\Software\Microsoft\Microsoft SQL Server\
<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP

3 comments:

  1. Is this a default port of all sql server..

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Yes 1433 is the default port all Sql Server except during installation or after you change it

    ReplyDelete

Please feel free to leave your comments.