Finding client IP-Address

Question

Post reply

Finding client IP-Address

richbgreen

Old Hand

Points: 364
More actions
December 2, 2008 at 7:33 am

#127304

Need a query that returns the client ip-address of who is updating/accessing the database. I am using sql server 2005 workgroup edition. I tried
SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID
But this always returns the Host IP address.. Anyone have any ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

remember connecting TO the SQl server is handled by the operating system...outside of the SQl process...

you can connect via named pipes or TCP/IP.

with some of the info available, you can try to use the OS to geth the iinformation, like this:

SELECT

spid,

hostname,

program_name,

db_name(dbid),

convert(sysname, rtrim(loginame)) as loginname

from master.dbo.sysprocesses (nolock)

Create table tmp (output varchar(255))

Insert tmp

exec xp_cmdShell 'nslookup DAISY'

exec xp_cmdShell 'nslookup DAISY|find "Address"'

select ltrim(rtrim(Replace(output,'Address:' ,''))) from tmp where output like 'Address:%'

drop table tmp

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

richbgreen Old Hand Points: 364 More actions · Answer 2

It seems that using this still only returns the server IP address. If I involke the application from client I still get the IP from the server. Is there any wat to get the IP from the client.

Lowell SSC Guru Points: 323504 More actions · Answer 3

if you have their hostname, and it's on an internal network, you can ping or nslookup the hostname to get the IP address; if it's from the web, you might be able to tie it into from the web logs, but i never tried that.

there is no native way to get the ip address....it never gets passed to the SQL server. if someone came in via named pipes, there would be no ip address.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mysat SSC Enthusiast Points: 154 More actions · Answer 4

Were you able to resolve this issue? I am looking for a solution to the same problem. I have triggers on few key tables which need to get the IP address of the user accessing/updating the application data (both web and other client/server apps). I cannot change the application code to include user's info (no source code and legacy apps)

Lowell SSC Guru Points: 323504 More actions · Answer 5

i found out that in SQL 2005 and above, some of the data management views have the client ip address, and you can narrow it down to the specific SPIDs:

this will get you started:

sample Results:

SPID IPAddress MachineName ApplicationName LoginName

----- --------------- ------------- --------------------------------------- -------------------

51 <local machine> D223 Microsoft SQL Server Management Studio DISNEY\lowell

52 <local machine> D223 Microsoft SQL Server Management Studio DISNEY\lowell

52 192.168.1.100 D223 Microsoft SQL Server Management Studio sa

SELECT

conn.session_ID as SPID,

conn.client_net_address as IPAddress,

sess.host_name as MachineName,

sess.program_name as ApplicationName,

login_name as LoginName

FROM sys.dm_exec_connections conn

inner join sys.dm_exec_sessions sess

on conn.session_ID=sess.session_ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150467 More actions · Answer 6

I usually use client_net_address as Lowell says.

Doesn't work so well for clients connecting over Named Pipes of course :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

lutsenko.dmitry SSC Enthusiast Points: 105 More actions · Answer 7

You can try out this solution. This code works even on sared hosting

http://dev-doc.blogspot.com/2012/08/ms-sql-2008-client-ip-address-on-shared.html