Obtaining the IP Address of a SQL Server Host

There are numerous online articles that show how to obtain the IP address of a SQL Server host. Most fall into two categories:

1. Run ipconfig.exe via xp_cmdshell and parse the output
2. Query DMV sys.dm_exec_connections

I'm not a fan of option #1. Enabling xp_cmdshell has security drawbacks, and there's lots of parsing involved anyway. That's cumbersome. Option #2 is elegant. And it's a pure t-sql solution, which I almost always prefer. Here are two sample queries for option #2:

SELECT c.local_net_address
FROM sys.dm_exec_connections AS c
WHERE c.session_id = @@SPID;

SELECT TOP(1) c.local_net_address
FROM sys.dm_exec_connections AS c
WHERE c.local_net_address IS NOT NULL;

Sometimes, neither of the above queries works, though. Query #1 returns NULL if you're connected over Shared Memory (logged in and running SSMS on the SQL host). Query #2 may return nothing if there are no connections using a non-Shared Memory protocol. The solution? Force a connection over TCP/IP. To do this, create a new connection in SSMS and use the "tcp:" prefix with the server name.

Now re-run either query and you'll get the IP address.