How do I trace what is calling SQL Server from my Server 2003 box?

Question

I have a Windows Server 2003 machine that I'm trying to decommission but my SQL Server team are telling me that there are still calls coming from this machine to the SQL Server machine. I have shut down everything (that I can think of) but apparently I still have something calling out.

How can I trace/find the service/application that's calling out to the SQL Server machine?

Answer 1

Here's a couple of ideas:

If the program has a persistent connection open you'll see the process ID (right column) in:

netstat -a -n -o | find "TCP" | find "1433"

(That assumes it's connecting to the default instance, TCP port 1433, on the destination server.) You could also put the destination server's IP in there in place of the 1433, too.

If it's intermittent you'll have a harder time catching it with netstat. The TCP/IP monitoring functionality of Process Monitor could give you a long-running trace that might show you something.

Your SQL Server admins could run an SQL trace on their end and see what application proports to be connecting, but that won't necessarily be accurate.

Answer 2

Apart from the good answers already, set up a trace in Profiler and see what the sql statements are that are being run. That should give you a good clue what is trying to access your DB.

If you have no sql being run, then it doesn't really matter if something else is trying to access the server - you can still shutdown the SQL server.

If you need to know what other networking calls are being made to it, install wireshark and watch them as they happen.

Answer 3

Have the SQL Server team check using a query similar to the following. This should give you more information as to what process is doing it:

SELECT conn.connect_time, conn.client_net_address,
  sess.login_time, sess.HOST_NAME, sess.program_name, sess.login_name
FROM sys.dm_exec_connections conn
  INNER JOIN sys.dm_exec_sessions sess
    ON conn.session_id = sess.session_id;

Answer 4

Previously answered on Stackoverflow: How to determine total number of open/active connections in ms sql server 2005