Find SQL Server TCP/IP Port Number

• Vijendran

  SSC Veteran

  Points: 278

  More actions

  July 14, 2009 at 1:05 am

  #212316

  Comments posted to this topic are about the item Find SQL Server TCP/IP Port Number

• SQLGerman

  SSC Journeyman

  Points: 75

  More actions

  July 20, 2009 at 5:11 am

  #1026858

  I think we should start using the SQL CLR to accomplish all this going forward. But, a good code though.

• BlackHawk-17

  SSChasing Mays

  Points: 628

  More actions

  May 13, 2010 at 7:08 am

  #1166836

  SQLGerman (7/20/2009)

  ---

  I think we should start using the SQL CLR to accomplish all this going forward.

  Not every one is capable of coding CLR modules and that's a lot of unnecessary overhead for simple info gathering, IMHO.

• Rudy Panigas

  SSChampion

  Points: 10702

  More actions

  May 13, 2010 at 8:09 am

  #1166891

  Nice code. Any idea where to fine the port number for SQL 2008?

  Rudy

• Rudy Panigas

  SSChampion

  Points: 10702

  More actions

  May 13, 2010 at 9:03 am

  #1166968

  Hello everyone,

  Here is the code that will also scan SQL 2008 servers

  --SQL 2000/2005/2008 Version

  set nocount on

  go

  DECLARE @SqlPort Nvarchar(10)

  DECLARE @instance_name Nvarchar(30)

  DECLARE @reg_key Nvarchar(500)

  Declare @value_name Nvarchar(20)

  if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),2) = '10'

  BEGIN

  select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

  if @instance_name is NULL

  BEGIN

  set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'

  --END

  --ELSE BEGIN

  --set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'

  END

  EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

  @key=@reg_key, @value_name='TcpDynamicPorts',

  @value=@SqlPort output

  select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port

  END

  --------------------------------------------------------------------

  if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'

  BEGIN

  select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

  if @instance_name is NULL

  BEGIN

  set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'

  END

  ELSE BEGIN

  set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'

  END

  EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

  @key=@reg_key, @value_name='TcpPort',

  @value=@SqlPort output

  select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port

  END

  if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'

  BEGIN

  Create table #Port_2000 (value nvarchar(20),Data nVarchar(10))

  insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp', 'tcpPort'

  select @SqlPort = Data from #Port_2000

  select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port

  drop table #Port_2000

  END

  Rudy

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 19, 2016 at 7:00 am

  #1880203

  Thanks for the script.