Getting the IP address of a user

[mikelawrence]

My customers are concerned with fraud happening on their system and want to record the IP address of machines accessing their system. I'm using ASP pages with SQL Server 2003. My customers are using generic admin logons so many people could use the same logon. I appreciate this is deeply flawed but this is the way the customer wants it. So in theory i just want to do an insert into a sql table along the lines of

INSERT INTO TransferTrackingHistory(Transfer_From,Transfer_To, Amount, IPaddress)
VALUES(@from, @to, @amount, @ipaddress)

so my problem is how to get @ipaddress.

Any help is much appreciated

thanks

mike

 

[TysonLPrice]

This is from another post and I personally don't have any experience in this area. I would think you would be better off getting it from the application side:

"On SQL2005 you can get this from select * from sys.dm_exec_connections in the client_net_address column assuming that their net_transport is TCP.

On Sql2000 it is a challenge. It appears that for most remote connections, the first connection/session from that client will have the MAC address coded in the net_address column of sysprocesses. Than you could use xp_CmdShell 'ARP -a' if your SQL server is also a domain controller to list the current cache of MAC to IP mappings. If it is not a domain controller, well ... we are already beyond the limits of what I know. Someone else will have to take it from here."

 

[mikelawrence]

Hi,

Thanks v much for this. However when i try to query this table with a query like

select * from sys.dm_exec_connections

i get a message invalid oblect name

so looking this up i think i need to give myself rights to view the table so i tried

grant view server state to adminstrators

but get a message invalid syntax near view.

This is clearly not my area of expertise so assume nothing about my knowledge but lots about my gratitude if i can get this to work!

ta

mike

 

[jbenson001]

TysonPrice is correct, you should be getting that info on the front-end side, SQL is for data maniupulation.

The problem is, you will still not be able to get the user's true IP address in all cases. For expamle, if they are going through a router or proxy server:

http://www.developerfusion.com/code/1626/get-the-visitors-ip-address/