Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can i see what IP address made the request to SQL Server?

i need to clearly see what IP address made what sql query to SQL server. I am trying to use SQL Profiler, but it seems there is no way i can somehow to differentiate the machine (browser) where the query came from. It only has the communication details between the web server and the sql server. Is there ANY way for me (any unknown log?) that will allow me to see the identification of the original machine where the query came from?

Thanks for any suggestion.

HF

like image 540
HotFrost Avatar asked Nov 04 '09 15:11

HotFrost


3 Answers

You can get the hostname of the current connection, or really any information from the sysprocesses table

SELECT hostname FROM sys.sysprocesses WHERE spid = @@SPID

This obviously won't work to get the ip address of web hosts if that's what you're looking for.

like image 157
joshperry Avatar answered Oct 02 '22 22:10

joshperry


Like @joshperry said, you can retrieve client address and server address with

SELECT client_net_address, local_net_address 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID
like image 26
Felippe Silvestre Avatar answered Oct 02 '22 21:10

Felippe Silvestre


As far as SQL Server is concerned the request will always come from your webserver. You need to capture and log the IP address in your web app.

like image 44
RMcLeod Avatar answered Oct 02 '22 21:10

RMcLeod