How to identify a client application whose host_process_id points to sqlserver.exe

Question

I am in the process of trying to identify a client application which uses one of the SQL Servers I support (2008 R2). Periodic queries against sys.dm_exec_sessions regularly return sessions whose login_name is, variously, either one of two non-system SQL-authenticated accounts, or a single, specific, non-system local Windows account, on the machine that hosts the SQL Server instance. The value in program_name is 'Microsoft SQL Server'; the value in host_name is the name of the server in question; and the value in host_process_id points to the sqlserver.exe process on the same server (as cross-referenced via Task Manager).

My question is, what mechanism could be initiating these sessions using the host_process_id associated with the engine executable itself? I realize program_name is configurable by any application, but I didn't think that was true for host_process_id. A SQL Agent job would connect from a separate executable, as would sqlcmd or the data collection utility. To my knowledge, we don't use Service Broker for any user applications. Could it be coming from another engine instance as a linked-server connection? I feel like I'm missing something that should have been obvious. How can I track this down?

There are multiple session_ids, all greater than 50, and all with user-created login names, not 'sa' or any account typically used by an internal process. The sessions also run queries against application tables in user databases.

Answer 1

Since the sessions are using user-created logins and are accessing application tables in user databases, and since the host_process_id aligns with the sqlserver.exe process, it sounds like there is a SQLCLR assembly running code.

Use the following query to look for SQLCLR Assemblies in all databases:

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
UNION ALL
' END + 'SELECT DatabaseName = ''' + QUOTENAME(d.name) + ''', a.*
FROM sys.assemblies a
WHERE a.name <> ''Microsoft.SqlServer.Types''
'
FROM sys.databases d
ORDER BY d.name;
SET @cmd = @cmd + ';'
EXEC sys.sp_executesql @cmd;

Answer 2

When the program_name value is 'Microsoft SQL Server' it is often a linked server.

If you have a linked server configured that links back to the local server it would appear as 'Microsoft SQL Server' with the sqlservr.exe process id as host_process_id.

This query could be from basically any application that connects to the SQL Server then queries the linked server, which in turn establishes a new connection with the host_process_id of sqlservr.exe and the program_name of 'Microsoft SQL Server'.

Check your linked servers to see if they have security configured to use these logins. If not, then you can trace the activity of these logins using a SQL trace to source the original connection details to track down the source.

Also, if you're using replication and the local machine is a distributor and a publisher or subscriber then this could be the source (at least the source of the linked server), as replication creates a linked server during setup.