How to discover the caller's IP address?

08-12-04, 05:34 PM

faustino Dina

Hi,

I need to know the IP addresses of the PCs that are connected to my SQL
Server. The master.dbo.sysprocesses table contains the field net_address.
This is supposed to be the MAC address of the client interface, but the real
problem is to map this MAC address to the actual IP. I've tryed by using ARP
utility, but with this one I can reach only the clients that are on my
subnet, it doesn't crosses routers. After some googling it looks to be an
unresolved problem on the Internet.
So is it another form on SQL Server to collect the IP address of their
clients? Any other idea?

Thanks in advance

08-12-04, 06:07 PM

If this is a web application (i.e. client -> webserver -> sql) there is no
way for sqlserver to know about the client. The only connection sqlserver
might know is the webserver (in fact, the webserver is the client to
sqlserver).

If this is a client/server app, you might be able to get the ip by looking
at the hostname column of sysprocesses and ping it to see if it's resolved
to an ip. Another route is to use "application name" property of the
connectionstring to store the IP and pass it in.

e.g.
"Provider=SQLOLEDB;Initial Catalog=users;Data Source=computer;User ID=user;
Password=pwd;Application Name=The_IP"

SELECT APP_NAME()

"faustino Dina" <ffdina> wrote in message
news:3348
[..]

08-12-04, 07:03 PM

Aaron [SQL Server MVP]

> If this is a web application (i.e. client -> webserver -> sql) there is no
> way for sqlserver to know about the client.

Actually, in this case, the web server can pass environment variables to the
database. For example, ASP exposes
Request.ServerVariables("REMOTE_ADDR") -- there are similar headers exposed
to other environments such as PHP and ColdFusion.

Of course, there are problems with accuracy, e.g. if the web server is being
hit internally vs. externally, and there is always the problem with 50,000
AOL users sharing a single IP address.

But for machines inside the LAN and connecting within the LAN, assuming no
funky firewall or proxy behavior, the web server can pass it along...

08-12-04, 07:18 PM

Unless you specifically pass the IP to sqlserver, there is -noway- sqlserver
gonna know about the webuser. Even so, the remote_addr variable only returns
the IP that is exposed/assigned by the ISP. Again, it would be almost
*impossible* to know the true client's IP in a typical environment where the
client must go through all kinds of proxies and router to get out to the
web.

"Aaron [SQL Server MVP]" <ten.xoc> wrote in message
news:a380
[..]

08-12-04, 07:41 PM

Aaron [SQL Server MVP]

> Unless you specifically pass the IP to sqlserver, there is -noway-
sqlserver
> gonna know about the webuser.

That's what I said...

> Even so, the remote_addr variable only returns
> the IP that is exposed/assigned by the ISP.

Yes, and I identified that limitation also. The OP said he had users on his
subnet, where ISP is not going to be an issue. For those outside, well, the
OP has to decide if nothing is better than something.

Similar Threads
discover an IP address I have a wireless access point that i may have turned into a paper weight. it shows network activity, but i cant find the IP address of it. how can i find the IP address? I...
E-mail or fax address of Discover What is the e-mail or fax address of Discover -- as in Discover card? Thank you. Barry Karas
OT : Discover Outgoing IP address Is anyone aware of a utility that will test the system and discover its "outgoing Public IP Address". what I want to do is run a task or utiltity to test the system where...
Discover Computer Name, IP Address If I can see another computer c$ share using the computer name (like \\bonnie\c$), is there a file I can open or a way I can discover the IP address of the computer?...
How to discover the caller's IP address? Hi, I need to know the IP addresses of the PCs that are connected to my SQL Server. The master.dbo.sysprocesses table contains the field net_address. This is supposed to be...