Sql-server – Finding the application behind “.net sqlclient data provider”

connectionssql servertrace

I'm trying to find which application is opening various connections on the SQL Server, all named ".net sqlclient data provider".

From system monitor I got the machine name;
On the client with netstat -a -b -o | Find "SQLServer", I have found 4 connections all with the PID 4 (ntoskrnl) ... yes this is windows and it is a MS SQL server.

"Trace process in SQL server profiler", crashes the Management Studio.

So, before I start killing applications, one by one, do you have an idea how to trace back this connection?

Best Regards;
Ezeq

Best Answer

sp_who2 (MSDN) is always a good start, and you can query the sys.processes table or sys.dm_exec_connections (MSDN) DMV.

Alternatively something like Adam Machanic's sp_WhoIsActive can help find problem processes and queries.

If you can't find what you're looking for directly in SQL Server, you'll be able to find things like the hostname/IP address and loginame that will help you track down the culprit.

Sample code for querying DMV/sys.processes (you'll have to chop it about if you want to do more):

Select spid,hostname,hostprocess,program_name,nt_username, blocked, waittime, waittype, loginame,cmd,spid,waittype,waittime,lastwaittype,cpu,physical_io,memusage,login_time,last_batch,open_tran,status,net_address, t.text 
from sys.sysprocesses sp 
--JOIN sys.dm_exec_connections con ON con.session_id = sp.sid
CROSS APPLY( select text from sys.dm_exec_sql_text(sp.sql_handle))t 
--where hostname
order by sp.spid
Related Question