Obtaining the IP Address of a SQL Server Host
- Posted in:
- SQL Server
There are numerous online articles that show how to obtain the IP address of a SQL Server host. Most fall into two categories:
- Run ipconfig.exe via xp_cmdshell and parse the output
- 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.
Comments