Sql - Query to get server's IP address

Uncovering server network details

A variety of connection properties accessible in SQL Server can yield comprehensive network data. For a thorough approach, consider the script below:

SELECT 
    net_transport,  -- The internet has highways? Who knew!
    protocol_type,  -- TCP or UDP. Like snail mail or email!
    auth_scheme,  -- Yes, we need to KNOW you're you 
    local_net_address,  -- Here's your server's IP
    local_tcp_port,  -- The 'door' your data goes through
    client_net_address  -- The client's 'house'
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;


This script provides an in-depth review of your current connection, namely the local_net_address, equating to your server's IP address.

Managing permissions and upholding security

Be cautious: accessing sys.dm_exec_connections demands the VIEW SERVER STATE permission, granted wisely:

GRANT VIEW SERVER STATE TO [YourLogin];  -- Use your powers wisely!


Security should be your beacon. Commands such as xp_cmdshell should be eschewed, they can expose your system to unauthorized activities.

Deciphering server identity

Sometimes, knowing the server's name is as valuable as its IP. To achieve this, use the age-old @@SERVERNAME global variable or the SERVERPROPERTY function:

SELECT @@SERVERNAME AS Server_Name;  
-- Or
SELECT SERVERPROPERTY('MachineName') AS Server_Machine_Name;  


These functions provide the machine name and server identifier, giving you additional means to identify your server on the network.

Designing a stored procedure for IP retrieval

To make your task less repetitive and more efficient, you can wrap your logic within a stored procedure:

CREATE PROCEDURE GetServerIPAddress  -- Because who likes typing the same thing over and over?
AS
BEGIN
    SELECT local_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;
END;


Now, by calling EXEC GetServerIPAddress, you can retrieve the IP address anytime — it is that simple!

Reflecting on network information

Executing local_net_address related query offers you a deep understanding of your networking ecosystem. The net_transport column indicates the transport protocol, while local_tcp_port and auth_scheme convey the communication port and authentication scheme.

Parsing complex address data

In SQL Server, when dealing with hostname strings, CHARINDEX and LEFT are your best friends:

SELECT LEFT(client_net_address, CHARINDEX(':', client_net_address) - 1) AS JustIPAddress FROM sys.dm_exec_connections;


This trims off the port details, returning you just the IP address.

Catering to SQL Server 2005 needs

If you're on SQL Server 2005, remember some functionalities might be limited or require alternative methods due to distinctive specifications of this edition.