Port Number

• pshrestha4

  SSC Enthusiast

  Points: 193

  More actions

  December 25, 2007 at 1:23 pm

  #180082

  Could someone tell me how to figure out the port number of a particular instance of SQL server.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736063

  More actions

  December 25, 2007 at 4:48 pm

  #762626

  In 2005, Configuration Manager. I think it's also in the error log and possibly the Windows Event logs.

  You can do a netstat -a and look for the listener. I think it shows up on non-standard ports.

  In 2000, Server Configuration Utility.

• Kevin Brennan

  Right there with Babe

  Points: 738

  More actions

  December 26, 2007 at 6:36 am

  #762663

  pshrestha4 (12/25/2007)

  ---

  Could someone tell me how to figure out the port number of a particular instance of SQL server.

  You have several options for determining the port number a SQL Server instance is using. SQL Server dynamically chooses the port when the server is first installed. The same port number is used each subsequent time the server is stopped and started. Dynamic port allocation is a one-time occurrence.

  To see the port number the server is using, launch the Server Network Utility from the server in question and click Properties in the TCP/IP entry in the Enabled protocols list. You can also check the port number an instance of SQL Server is using by looking in the error log for the specific instance. You should see an entry in the error log that looks like this:

  SQL server listening on 127.0.0.1: 1362.The four-digit number after the colon is the port that SQL Server is listening on for the IP address, which is specified to the left of the colon. (:)

  Alternatively, you can check the port number an instance of SQL Server is using by looking in the registry. Specific paths to registry keys vary based on how you've installed and upgraded SQL Server, but you should find a key similar to the following:

  hkey_local_machine\softwaremicrosoft microsoft sqlserverInstanceName\MSSQLServer SuperSocketNetLib\Tcp\where InstanceName is the instance name of the SQL Server you're using. The key will have an entry called TcpPort, which contains the port number the instance is using.

  Here is some code that should return the DEFAULT instance port num:

  -- Run from Query Analyser

  DECLARE @port varchar(5)

  EXEC xp_regread @rootkey='HKEY_LOCAL_MACHINE',

  @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',

  @value_name='TcpPort',

  @value=@port OUTPUT

  PRINT @port

  ---And the following will should return NAMED INSTANCE port num:

  DECLARE @port varchar(5)

  EXEC xp_regread @rootkey='HKEY_LOCAL_MACHINE',

  @key='SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer\SuperSocketNetLib\Tcp',

  @value_name='TcpPort',

  @value=@port OUTPUT

  PRINT @port

  CodeMinkey