7

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

HotFrost
  • 1,595
  • 4
  • 17
  • 25
  • ok.. yes.. thanks for confirmation (unless i send the ip explicitly to db, there is no way to say). thanks you. – HotFrost Nov 04 '09 at 16:04

6 Answers6

6

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.

joshperry
  • 41,167
  • 16
  • 88
  • 103
4

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
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Felippe Silvestre
  • 79
  • 5
3

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.

RMcLeod
  • 2,561
  • 1
  • 22
  • 38
1
SELECT r.client_net_address,sqltext.Text
  FROM sys.dm_exec_requests req left join sys.dm_exec_connections as r on req.session_id=r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
user3108684
  • 11
  • 2
  • Your answer certainly is worth a little explanation. Kindly refer to http://stackoverflow.com/help/how-to-answer . – J. Chomel Mar 08 '17 at 10:33
  • 1
    Whilst this code snippet is welcome, and may provide some help, it would be [greatly improved if it included an explanation](//meta.stackexchange.com/q/114762) of *how* and *why* this solves the problem. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Mar 08 '17 at 12:56
0

If you do not write code in the application to pass the client address from the web server to the database you will have to look at the web logs - they will give the ip address of the client.

mmmmmm
  • 32,227
  • 27
  • 88
  • 117
0

SELECT * FROM sys.dm_exec_connections returns information about the connections established to this instance of SQL Server and the details of each connection (https://msdn.microsoft.com/en-us/library/ms181509%28v=sql.120%29.aspx)

Julien Carsique
  • 3,915
  • 3
  • 22
  • 28
Johnny Cage
  • 5,526
  • 2
  • 11
  • 7
  • Could you elaborate a little more? What does this view return and how does it apply to the question? – TT. Jan 05 '16 at 18:31