TCP Port & IP Address

Very quick method to confirm the TCP Port that SQL Server is listening on:

  DECLARE @tcp_port NVARCHAR(5)

  EXEC xp_regread

     @rootkey     =     'HKEY_LOCAL_MACHINE',

     @key    =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',

     @value_name    =    'TcpPort',

     @value        =    @tcp_port OUTPUT

  SELECT @tcp_port [Port]

This can also be done through the SQL Server Configuration Manager.  See 'SQL Server Network Configuration' in the left pane, and then select 'Protocols for <INSTANCENAME>'.   Double-click the 'TCP/IP' protocol name in the right pane, and this will open a 'TCP/IP Properties' dialog.  Select the 'IP Addresses' tab, and you will see the TCP Port.  See here:

You can also use xp_cmdshell to return the IP Address of the SQL Server you are connected to, like this:

  EXEC master.dbo.xp_cmdshell 'ipconfig'

That will return all of the other media and state details specfic to the network, such as DNS, Subnet Mask, Default Gateway, etc.  Try this if you ONLY want to return the SQL Server IP Address:

  CREATE TABLE #ipconfig(

   captured_line VARCHAR(255)

  )

  INSERT #ipconfig

  EXECUTE xp_cmdshell 'ipconfig /all';

  SELECT 

    LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),4) AS VARCHAR(4))))+'.'+

    LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),3) AS VARCHAR(3))))+'.'+

    LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),2) AS VARCHAR(3))))+'.'+

    LTRIM(RTRIM(CAST(PARSENAME(SUBSTRING(captured_line,40,15),1) AS VARCHAR(3)))) [IP Address]

  FROM 

    #ipconfig

  WHERE 

    captured_line like '%IPv4 Address%';

  DROP TABLE #ipconfig