SQL Server IP Address and Port

You will need the IP Address and Port that SQL Server has been configured to use before you can access your databases remotely.

This can be done through the SQL Server Configuration Manager. See 'SQL Server Network Configuration' in the left pane, and then select 'Protocols for '. 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:

Also, a 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]

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