Home > sql triggers > Have SQL Demand Application Identification

Have SQL Demand Application Identification

How many times have you run Profiler on your SQL Servers to see this?

With all of those apps identifying themselves as .Net SqlClient Data Provider, how can you tell which app is really making those queries? Unless each app uses a distinct login, you would never know. And many times devs will reuse logins for their own ease.

I ran into this problem while I was doing some migration planning. I was profiling the server to see what apps were making queries to a specific database we were migrating over to another server. I wanted to make sure that anything making a connection was properly configured after the move. I found something similar to the shot above; connections from different machines making different queries at different, albeit regular intervals, all identified as “.Net SqlClient Data Provider.” With some other detective work, I was able to figure out where they were coming from.

How can this be avoided? When developers are setting up their connection strings, they need to specify the Application Name parameter. An example is below.

<add name="LOCAL"
    connectionString="Data Source=(local);Initial Catalog=master;Integrated Security=True;Application Name=LoginTriggerDemoApp"/>

Using LOGON TRIGGERS in SQL 2008 and up, you can go one step further and start auditing logons and watching for any connection made that uses a generic Application Name. There is a limited amount of information available in the EVENTDATA() from a LOGON EVENT. You can get more information about the connection by joining to the sys.dm_exec_sessions DMV.

...
DECLARE @data XML, @ApplicationName NVARCHAR(128)
SET @data = EVENTDATA()
SELECT @ApplicationName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
...

In the code above, I first declare an XML variable to hold the data that comes from the EVENTDATA() function. Then use the SPID to join to the sys.dm_exec_sessions DMV to get the program_name, which becomes our Application Name. From this, you can log information about the connection. You can start researching anything that doesn’t have a specific application name and start encouraging your developers to include that portion of the connection string.

You can go even further and block any generic connections. Using the trigger below, any connection that comes in under “.Net SqlClient Data Provider” gets disconnected via ROLLBACK. You can add to or alter the IN clause to prevent generic Java, PHP, etc. connections. You can check for blanks, or even prevent certain apps from logging in all together, regardless of credentials.

USE [master];
GO
 
 
/****** Object: DdlTrigger [Deny_Generic_ConnString_Trigger] ******/
IF EXISTS(
	SELECT * FROM master.sys.server_triggers
	WHERE parent_class_desc = 'SERVER'
		AND name = N'Deny_Generic_ConnString_Trigger')
DROP TRIGGER [Deny_Generic_ConnString_Trigger] ON ALL SERVER
GO
 
CREATE TRIGGER Deny_Generic_ConnString_Trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @AppName nvarchar(128)
SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = EVENTDATA().value(
    '(/EVENT_INSTANCE/SPID)[1]', 'int')
 
 
IF @AppName IN ('.Net SqlClient Data Provider')
    ROLLBACK; --Disconnect the session
 
END;

Notes for Deployment

If you have dev & QA servers, test these out there first. Give your application developers time to make changes and test against these new triggers. Also, you may not be able to change 3rd party apps, so you can make exceptions based on host names or anything else available in the EVENTDATA() or sys.dm_exec_sessions. I did find out that Reporting Services does not like either of these triggers in place. Even the logging trigger will prevent the Report Manager from running.

Blocking generic applications may have consequences on a server that is already serving data to unidentified apps, so be careful. You may want to put logging in for a time and track down any offending applications and give them time to change. For a server coming online though, blocking generic apps may be a good way of preventing a lot of unnecessary detective work, and make profiling or migrating your servers easier.

I have included my scripts as well as sample C# project to demonstrate the technique. Download now.

A special thanks to Brent Ozar for giving me his opinion on this post and inflating my head somewhat.

Categories: sql triggers Tags:

%d bloggers like this: