How to audit usernames in ASP.Net application (4213618)

Applies to
ApexSQL Trigger

Summary
This article explains how to modify an ASP.NET application so that it can give ApexSQL Trigger its usernames/application information

Description
ApexSQL Trigger will collect all DML and DDL operations executed on auditing databases on SQL Server. We don't actually know what layers are above the SQL Server so we can't know actually how to collect all the external information. In these cases you must pass custom data to SQL Server somehow. Unfortunately, SQL Server doesn't have any option to attach additional metadata to specific transaction. So, the only thing which is left is to tweak SQL connection used to execute the given command. On each SQL connection you can set application name property with custom data

For this, in .NET you can use SqlConnectionStringBuilder. You will find ApplicationName property which can be set to arbitrary string

Quick tip: If you don’t set this property, default name will be “.NET SQLClient Data Provider”

If you need both Application Name and custom data (in this case actual user) you can create your own format and parse it later:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); 
                builder.ApplicationName = string.Format("MyApplicationName - @@{0}", username); 

When you query reports, you must match the pattern for given user, like:

Where application name is like @@john

About ApexSQL Trigger
ApexSQL Trigger is a SQL Server auditing tool, which tracks data changes in SQL Server databases using triggers. ApexSQL Trigger allows you to audit database access by login, host and application name