Finding SQL Server IP address and TCP port

Lots of blog posts talk about finding the IP address of the SQL Server instance. For most, this is about looking at sys.dm_exec_connections or selecting CONNECTIONPROPERTY(‘local_net_address’).

The problem with those techniquest is that they only work from a remote connection via TCP/IP.  Others talk about finding the IP address via xp_cmdshell and parsing the output of ‘ipconfig’.  The same goes for the TCP port, this is also usually found by the same methods, or by looking at the SQL Server service configuration in the registry.

I needed something the would always work, regardless of where the code was executed, and preferably secure and not too cluncky.

The following script will also use CONNECTIONPROPERTY, but will create a secondary connection via OPENROWSET to the local instance using TCP.  The key here is building the connection string containing ‘tcp:’ and the local servername @@servername.  The rest of the code is merely building dynamic SQL to get around OPENROWSET’s limitation of not being able to take variables as its parameters.

DECLARE @ip_address varchar(15)
DECLARE @tcp_port int 
DECLARE @connectionstring nvarchar(max) 
DECLARE @parm_definition nvarchar(max);
DECLARE @command nvarchar(max)
SET @connectionstring = N'Server=tcp:' + @@SERVERNAME + ';Trusted_Connection=yes;'
SET @parm_definition = N'@ip_address_OUT varchar(15) OUTPUT
 , @tcp_port_OUT int OUTPUT';
SET @command = N'SELECT @ip_address_OUT = a.local_net_address,
 @tcp_port_OUT = a.local_tcp_port
 FROM OPENROWSET(''SQLNCLI'', ''' + @connectionstring + ''', ''
 SELECT local_net_address = CONVERT(VARCHAR(15), CONNECTIONPROPERTY(''''local_net_address'''') )
 ,local_tcp_port = CONVERT(int, CONNECTIONPROPERTY(''''local_tcp_port'''') )
 '') as a'
EXEC SP_executeSQL @command
 , @parm_definition
 , @ip_address_OUT = @ip_address OUTPUT
 , @tcp_port_OUT = @tcp_port OUTPUT;

SELECT @ip_address, @tcp_port

I’d be interested to know if there are easier or more elegant ways, so leave a comment if you happen to know.

Have fun,

Bert.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s