0

I am aware of "SQL Server Profiler", But is there any tools or methods available to monitor the issued sql queries from the client machine?

Some of the things currently in my mind are:

1) The SysInternals ProcMon can log and tell when the executable process on client machine connects to sql server. Any similar but more advanced tools available to tell more data?

2) Any debug version or instrumented version of client libraries and APIs available for client machine to allow such operation?

F.I.V
  • 317
  • 1
  • 14

1 Answers1

3

You can enable ODBC Tracing from the ODBC Data Source Administrator (odbcad32.exe). The log file it generates is rather cryptic but it will provide you with the SQL statements run by the logged in user on that machine. There is also a checkbox labeled "Machine-Wide tracing for all user identities" for logging all SQL statements run by any user - including services - on that machine.

Logging may not start until you disconnect and reconnect to the SQL server. So, it is a good idea to restart whatever programs/services connect to your SQL Server. This is especially important if you have connection pooling enabled, which will keep connections open even if the program, in question, has disconnected.

Also, there are two versions of the ODBC Data Source Administrator on Windows 64bit, one for 32bit and the other for 64bit. So, you will need to enable logging for the appropriate bitness of the program you are tracing.

Update: Another option for you would be to use a tool like Wireshark. Since it has the ability to sit at the network driver level, it can intercept all your network traffic - including but not limited to SQL. These are known as TDS packets - a standard that is used by Sybase SQL Server (ASE), Microsoft SQL Server and FreeTDS ( w/ UnixODBC).

Community
  • 1
  • 1
jveazey
  • 5,398
  • 1
  • 29
  • 44
  • It is only applicable when the program / service uses the ODBC data source names, isn't it? Or it will capture native sql clients as well when set to machine-wide? – F.I.V May 19 '13 at 10:37
  • If not, there would be a question, can we trap requests for native clients to pass through our defined ODBC dsn instead? – F.I.V May 19 '13 at 10:43
  • 1
    It's at the driver level. So, as long as you are using one of the drivers listed in the ODBC Data Source Administrator, it should work. – jveazey May 20 '13 at 20:43
  • Have my votes up on your comment and answer, since you pointed something I was not aware of (the tracing thing). We currently do not use ODBC, but that is a piece of useful information for the future.... tnx anyway – F.I.V May 22 '13 at 11:06