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, 06:36 PM

Nitin Rana

In a web server application, you can view client IPs by
browsing Web Server Log.

IIS log will have all the information that you need.

-Nitin

>-----Original Message-----
>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
>> 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
[..]

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 address of Discover -- as in Discover card? Thank you, Barry Karas
Cannot Discover static IP Address Hi, Can anyone advise on why static workstation cannot be discover by sms 2003 in an NT4 Domain? Thanks.
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...
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...