Remote Client IP address Accc In SQL Server

Question

What I want to know when my client application written in the Java connect SQL server then SQL server can provide me the Local IP address of the client machine.
Do we have any mechanism in the SQL server to know the Client machine IP address?
Any help will be greatful.

---

shawn aslam

Answer 1

1. You can get that from SQL server profiler trace files. open the trc files with SQL profiler where you can see the hostname , login name etc from here.
2. You can also look in to the sys.dm_exec_sessions the field hostname will give the computer name (or eventually the ip address).

---

Thanks, Leks

Answer 2

Hello Shawn,

as Lekss wrote you can select the hostname and the used network protocol in a client session.
There is also a field "net_address", it looks & sounds like the MAC address of the client, but when I test & compare it, it don't fit. So I don't know, what this net_address represent.

SELECT hostname, net_library, net_address

FROM sys.sysprocesses

WHERE spid = @@SPID

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 3

1. You can get that from SQL server profiler trace files. open the trc files with SQL profiler where you can see the hostname , login name etc from here.
2. You can also look in to the sys.dm_exec_sessions the field hostname will give the computer name (or eventually the ip address).

---

Thanks, Leks

In shared hosting there is no right to execute from sys.dm_exec_sessions, so how to get client ip. Right query to get client-ip is mentioned below

SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Answer 4

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