Tuesday, May 3, 2011

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',
     @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';

    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]
    captured_line like '%IPv4 Address%';

  DROP TABLE #ipconfig

1 comment:

  1. It's worth noting that the xp_regread extended SP is undocumented and unsupported, and that there are better ways to get this info: http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/b83dd2c1-afde-4342-835f-c1debd73d9ba. Specifically see the comment by Jonathan Kehayies from SQLskills.com

    Also many shops don't allow shelling out to the OS for security reasons so the xp_cmdshell ESP isn't always an option...