Peter Larsson Blog

Patron Saint of Lost Yaks

How to get IP address

by Peter Larsson in  , ,

DECLARE       @Interfaces TABLE
       (
              RowID INT IDENTITY(0, 1),
              Interface CHAR(38),
              IP VARCHAR(15)
       )
 
INSERT @Interfaces
       (
              Interface
       )
EXEC   master..xp_regenumkeys     N'HKEY_LOCAL_MACHINE',
                           N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces'
 
DECLARE       @RowID INT,
       @IP VARCHAR(15),
       @Key NVARCHAR(200)
 
SELECT @RowID = MAX(RowID)
FROM   @Interfaces
 
WHILE @RowID >= 0
       BEGIN
              SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface
              FROM   @Interfaces
              WHERE RowID = @RowID
 
              EXEC   master..xp_regread   N'HKEY_LOCAL_MACHINE',
                                         @Key,
                                         N'DhcpIPAddress',
                                         @IP OUTPUT
 
              IF @IP <> '0.0.0.0'
                     UPDATE @Interfaces
                     SET    IP = @IP
                     WHERE RowID = @RowID
 
              SET    @RowID = @RowID - 1
       END
                                 
SELECT IP
FROM   @Interfaces
WHERE IP IS NOT NULL

Legacy Comments
Mladen
2008-07-16		 re: How to get IP address
you could also just run

select * from sys.dm_exec_connections
where client_net_address != '<local machine>'

and check the local_net_address column
Rob Volk
2008-07-16		 re: How to get IP address
If you have xp_cmdshell enabled on your SQL Server, you can also use the following:

set nocount on
create table #ip(data varchar(256))
insert #ip exec master..xp_cmdshell 'ipconfig | findstr "IP Address"'
select substring(data, charindex(':', data)+2, 25) IPAddress from #ip where data like '%[0-9].%[0-9].%[0-9].[0-9]%'
drop table #ip

This should also pick up IPs for multiple NICs on your server, but I haven't tested that.
Nidhi
2008-07-17		 re: How to get IP address
I executed the given code, but it will not fetch the data correctly
Smitha
2008-07-17		 re: How to get IP address
Nice article
need to know about this registry....
pranay choksi
2009-02-07		 re: How to get IP address
because i like to find others ip sdderes
Madhu Menon
2011-11-28		 re: How to get IP address
I have tested the procedure given above to get the client IP but it provides server IP. To get client IP who modifies my database,
I changed Mladen's query which is as under. You may try this

select top 1 * from {any of the table on server}
select top 1 client_net_address from sys.dm_exec_connections
where client_net_address != '<local machine>'
order by last_read desc

Which shows my IP. I used to store this thru triggers when ever crucial data changes. Please test at your end and update the feedback