ApexSQL Knowledgebase: How to audit user names in ASP.Net application

LINKS

ApexSQL Knowledgebase

Tips and How-to Articles for ApexSQL Tools

How to audit user names in ASP.Net application

This KB article discusses how to modify an ASP .Net application so that it can give ApexSQL Audit the information it needs to audit the ASP .Net application's user names/login information.

DESCRIPTION
ApexSQL Audit is a server-side application that gathers information directly from the server. When using ApexSQL Audit along with ASP.Net applications, it may not be able to log the user information you want. For most ASP.Net applications, users will have an application login separate from the login information that the application uses to connect to the SQL Server. Usually, Asp.net applications connect to a database as a single user, using either NT authentication or SQL Server authentication (e.g. “sa”).

If the ASP.Net application uses NT authentication and the NT users are granted login rights to the SQL server--then ApexSQL Audit will audit the user names correctly (NT authentication should also be used in the connection string within ASP.Net application). However, in the case where the ASP.Net application uses a single user login (for example, “sa”) or custom name mapping (that is, each ASP.Net user is mapped to the appropriate SQL server login inside the application), then by design, this is the only information that ApexSQL Audit will audit.

With just a few simple steps however, you can enable ApexSQL Audit to audit the user names in the ASP .Net application as well. The following steps discuss an easy and convenient way to let ApexSQL Audit know about the ASP.Net user and log it correctly.

SOLUTION
To define the application’s user name within the audit trigger, we recommend using SQL connection’s CONTEXT_INFO functionality. The following query will set the user name for current connection:

DECLARE @BinVar varbinary(128)
SET @BinVar = CAST(N'User Name' AS varbinary(128) )
SET CONTEXT_INFO @BinVar

This way, you can easily map your ASP.Net user to corresponding connections.
In the audit trigger, this user name can be obtained from its context by the following query:

SELECT CAST(CONTEXT_INFO() AS NVARCHAR(64) )

The typical function to execute queries or stored procedure calls in ASP.Net applications looks like below:

public static int ExecuteNonQuery(string connectionString, CommandType
commandType, string commandText, params SqlParameter[]
commandParameters)
{
if( connectionString == null  connectionString.Length
== 0 ) throw new ArgumentNullException
 ( "connectionString" );

 // Create & open a SqlConnection, and dispose it after we are done
    using (SqlConnection connection = new SqlConnection(connectionString))
 {
connection.Open();

// Call the overloaded method that takes a ready connection
  instead of the connection string
return ExecuteNonQuery(connection, commandType, commandText,
commandParameters);
 }
}

Instead of directly creating a SqlConnection, we recommend you to create a function that will set up the newly created connection. Note that in below example, the “username” parameter is the desired application user name we want ApexSQL Audit to log in the audit triggers:

public static SqlConnection NewSqlConnection(string connectionString,
string username)
{
if( connectionString == null  connectionString.Length == 0 )
throw new ArgumentNullException( "connectionString" );
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
// Set username for the created connection in CONTEXT_INFO
 SqlCommand command = connection.CreateCommand();
command.CommandText = string.Format("DECLARE @BinVar varbinary
(128)" +
" SET @BinVar = CAST(N'{0}' AS varbinary(128) ) "+
"SET CONTEXT_INFO @BinVar ", username);
command.ExecuteNonQuery();
return connection;
}

Now we change the very first method using our helper function:

public static int ExecuteNonQuery(string connectionString, string username
, CommandType commandType, string commandText, params SqlParameter
[] commandParameters)
{
// Create & open a SqlConnection, and dispose it after we are done
 using (SqlConnection connection = NewSqlConnection
(connectionString, username))
{
    // Call the overloaded method that takes a ready connection instead
 of the connection string
return ExecuteNonQuery(connection, commandType,
commandText, commandParameters);
}
}

Function NewSqlConnection should be called everywhere when you create a new connection. It will set up the user name and guarantee that the command will always be executed from the appropriate user.

You will now be able to get the user name in the trigger’s context as shown below:

INSERT
INTO {%AUDIT.PRINT DATABASENAME%}.{%AUDIT.PRINT TRANSACTIONSTABLENAME%}
(
TABLE_NAME,
TABLE_SCHEMA,
AUDIT_ACTION_ID,
HOST_NAME,
APP_NAME,
MODIFIED_BY,
MODIFIED_DATE,
AFFECTED_ROWS,
[DATABASE]
)
VALUES(
'{%Audit.Print Replace(objTable.Name, "'", "''")%}',
'{%Audit.Print Replace(objTable.Owner, "'", "''")%}',
2, -- ACTION ID For INSERT
CASE
WHEN LEN(HOST_NAME()) < 1 THEN ' '
ELSE HOST_NAME()
END,
CASE
WHEN LEN(APP_NAME()) < 1 THEN ' '
ELSE APP_NAME()
END,
CAST(CONTEXT_INFO() AS NVARCHAR(64) ),
GETDATE(),
@ROWS_COUNT,
'{%Audit.Print Audit.Database.Name%}'
)

Using the above approach, you should always call most of functions with the user name.

To define the application’s user name along with application's name, function's name and client pc name within the audit trigger, we recommend using the following approach:

public static SqlConnection NewSqlConnection(string connectionString,
string username,string app_name,string client_pc_name,string func_name)
{
if( connectionString == null  connectionString.Length == 0 )
throw new ArgumentNullException( "connectionString" );
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
// Set username for the created connection in CONTEXT_INFO
 SqlCommand command = connection.CreateCommand();
command.CommandText = string.Format("DECLARE @BinVar varbinary
(128)" +
" SET @BinVar = CAST(N'{0}/{1}/{2}/{3}' AS varbinary(128) ) "+
"SET CONTEXT_INFO @BinVar ", username, app_name, client_pc_name, func_name);
command.ExecuteNonQuery();
return connection;
}

This will create one long string with all required data separated with the slash (/) sign.
However, you must be careful when creating this string because you have only 128 chars to store all the data, which is the CONTEXT_INFO maximum length.

Above approach may be inconvenient as you will have to change your interface in all functions. We suggest yet another approach to make changes only in the data access layer. To do this, you can create an additional helper class. You should be able to instantiate the Class, for example, the SqlHelper class is a connection provider:

internal sealed class SqlHelper
{
private string username = string.Empty;
public SqlHelper(string username)
{
this.username = username;
}
public SqlConnection NewSqlConnection(string
connectionString)
{
if( connectionString == null
connectionString.Length == 0 ) throw new
 ArgumentNullException
  ( "connectionString" );
SqlConnection connection = new SqlConnection
(connectionString);
connection.Open();
// Set username for the created connection in
  CONTEXT_INFO
SqlCommand command = connection.CreateCommand();
command.CommandText = string.Format
("DECLARE @BinVar varbinary(128)" +
" SET @BinVar = CAST(N'{0}' AS
 varbinary(128) ) "+
"SET CONTEXT_INFO @BinVar ", username);
command.ExecuteNonQuery();
return connection;
}
}

And this will not change the interface:

public int ExecuteNonQuery(string connectionString, CommandType
commandType, string commandText, params SqlParameter[]
commandParameters)
{
// Create & open a SqlConnection, and dispose of it after we are
  done
using (SqlConnection connection = NewSqlConnection
(connectionString))
{
// Call the overload that takes a connection in place of
   the connection string
return ExecuteNonQuery(connection, commandType,
commandText, commandParameters);
}
}

Connection provider should be stored in the session variables or created for each usage with the username (which is usually stored in the session variables).

Now your ASP.Net users will be logged correctly by ApexSQL Audit.

AUTHOR
Denis Mack

LAST REVIEW DATE
10 August 2007

Labels: Apex SQL Audit

posted by ApexSQL on Friday, August 10, 2007

6 Comments:

At November 8, 2007 12:22 PM,

Alan said...

Since you are associating the username with the connection, does connection pooling cause any issues?

At November 9, 2007 8:30 AM,

Denis Mack said...

No, any time when you receive connection from the pool you set the new CONTEXT_INFO value for current user.

At April 21, 2008 3:34 PM,

Yaron said...

Does this functionality work with SQL Server 2000? If we store the actual Username of the person hitting the web page within the table, can ApexSQL Audit log this value as the User?

At June 1, 2009 11:09 AM,

Anonymous said...

Any pointers for doing this using the Linq Context diagrams?

We have an application built and would like to try ApexAudit.

At August 10, 2009 3:22 PM,

Anonymous said...

Will I need to change all of the triggers manually to pull the user name from the connection_info, or does the latest version of Audit allow one to select 'ASP.NET-style' logging when it writes out the triggers? Or is it possible to change the template for the triggers and have all trigger code pick up the change from the template?

At August 11, 2009 11:13 AM,

Alexey Daryin said...

Hello,

Thanks for the inquiry.

"Or is it possible to change the template for the triggers and have all trigger code pick up the change from the template?"
I can confirm that this is the answer to your question.
You will just need to re-create all your triggers based on the changed template.
The best and fastest way is to have a saved project file with all tables and fields selected for auditing, so you will just need to select the changed architecture.

For expedited support pls post your questions on our forum here http://www.apexsql.com/forum/default.asp?C=и or by email support@apexsql.com

Please let us know if you need a ready template example.

Thanks,