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

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL 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.

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 Janice Lee on Friday, August 10, 2007

3 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?