TSQL : Returning the SQL Server IP Address

I recently wrote some audit scripts and wanted to find the IP Address of the server from TSQL. The majority of the replies on twitter correlated with my google findings, i.e. to use extended stored procedure xp_cmdshell to run an operating system command.

Leaving security concerns aside , here's a reminder on enabling xp_cmdshell

Using xp_cmdshell to retrieve server ip address -

create table #cmdresults(ip varchar(255))
insert into #cmdresults exec xp_cmdshell'ipconfig | find "IP Address"'
select ltrim(rtrim(substring(ip,charindex(':',ip)+1,len(ip)))) from #cmdresults where ip is not null
drop table #cmdresults

On moving it to my server, I found it did not work. The difference being that I needed to look for 'IPv4 Address', not 'IP Address' on my Windows 2008 installs where entries for IPv4 and IPv6 could both exist.

A simpler version was suggested by Kendra Little (@Kendra_Little | blog) was to look at the connections dmv, like this

Using DMV to return session ip address -

SELECT local_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPID 

This returns the connected client IP, so it only returns the server IP if you are working on your server (or an rdp session on it).

For the purpose of what I needed (reports emailed from the sql server), it is perfectly adequate (and tidier than xp_cmdshell).