NLog database target with application role support. The target is configured in (roughly) the same manner as the NLog database target, though with fewer features. Credits/sources: https://github.com/NLog/NLog/blob/master/src/NLog/Targets/DatabaseTarget.cs http://tivadj-tech.blogspot.dk/2011/09/spsetapprole-and-adonet-connection.html

DatabaseWithAppRoleTarget.cs

/// <summary>
/// Database target with support for application roles.
/// </summary>
[Target("DatabaseWithAppRole")]
public class DatabaseWithAppRoleTarget : TargetWithLayout
{
  private string _cachedConnectionStringName = null;
  private string _cachedConnectionString;

  public DatabaseWithAppRoleTarget()
  {
    this.Parameters = new List<DatabaseParameterInfo>();
  }

  /// <summary>
  /// Gets or sets the application role name
  /// </summary>
  [RequiredParameter]
  public Layout RoleName { get; set; }

  /// <summary>
  /// Gets or sets the application role password
  /// </summary>
  [RequiredParameter]
  public Layout Password { get; set; }

  /// <summary>
  /// Gets or sets the text of the SQL command to be executed when logging
  /// </summary>
  [RequiredParameter]
  public Layout CommandText { get; set; }

  /// <summary>
  /// Gets or sets the name of the connection string to use when connecting the the SQL Server
  /// </summary>
  [RequiredParameter]
  public string ConnectionStringName { get; set; }

  /// <summary>
  /// Gets the collection of parameters.
  /// </summary>
  [ArrayParameter(typeof(DatabaseParameterInfo), "parameter")]
  public List<DatabaseParameterInfo> Parameters { get; private set; }

  /// <summary>
  /// Writes the specified logging event to the database within the context of the configured application role.
  /// </summary>
  /// <param name="logEvent"></param>
  protected override void Write(LogEventInfo logEvent)
  {
    try
    {
      this.WriteEventToDatabase(logEvent);
    }
    catch (Exception exception)
    {
      InternalLogger.Error("Error when writing to database: {0}", exception);
      throw;
    }
  }

  private void WriteEventToDatabase(LogEventInfo logEvent)
  {
    if (_cachedConnectionString == null || _cachedConnectionStringName != this.ConnectionStringName)
    {
      _cachedConnectionString = ConfigurationManager.ConnectionStrings[this.ConnectionStringName].ConnectionString;
      _cachedConnectionStringName = this.ConnectionStringName;
    }

    using (var connection = new SqlConnection(_cachedConnectionString))
    {
      connection.Open();

      InternalLogger.Trace("Setting application role \"{0}\"", this.RoleName);

      // start an application role scope after opening the connection
      using (new SqlApplicationRoleScope(connection, this.RoleName.Render(logEvent), this.Password.Render(logEvent)))
      {
        var command = connection.CreateCommand();
        command.CommandText = this.CommandText.Render(logEvent);

        InternalLogger.Trace("Executing {0}: {1}", command.CommandType, command.CommandText);

        // add defined parameters to command
        foreach (var parameterInfo in this.Parameters)
        {
          var parameter = command.CreateParameter();
          parameter.Direction = System.Data.ParameterDirection.Input;
          if (parameterInfo.Name != null) parameter.ParameterName = parameterInfo.Name;
          if (parameterInfo.Size != 0) parameter.Size = parameterInfo.Size;
          if (parameterInfo.Precision != 0) parameter.Precision = parameterInfo.Precision;
          if (parameterInfo.Scale != 0) parameter.Scale = parameterInfo.Scale;

          string stringValue = parameterInfo.Layout.Render(logEvent);
          parameter.Value = stringValue;
          command.Parameters.Add(parameter);
        }

        int result = command.ExecuteNonQuery();
        InternalLogger.Trace("Finished execution, result = {0}", result);
      }

      InternalLogger.Trace("Application role \"{0}\" unset", this.RoleName);
    }
  }
}

SqlApplicationRoleScope.cs

/// <summary>
/// Starts an application role scope for a given connection.
/// </summary>
public class SqlApplicationRoleScope : IDisposable
{
  private readonly SqlConnection _connection;
  private byte[] _cookie;
  private bool _disposed = true;

  /// <summary>
  /// Starts an application role scope for the specified connection.
  /// </summary>
  /// <param name="connection">Connection</param>
  /// <param name="roleName">Application role name</param>
  /// <param name="password">Application role password</param>
  public SqlApplicationRoleScope(SqlConnection connection, string roleName, string password)
  {
    if (connection == null)
      throw new ArgumentNullException("connection");
    if (connection.State != ConnectionState.Open)
      throw new InvalidOperationException("Connection must be opened before setting application role");

    if (string.IsNullOrEmpty(roleName))
      throw new ArgumentNullException("roleName");
    if (password == null)
      throw new ArgumentNullException("password");
    _connection = connection;

    Impersonate(roleName, password);
  }

  /// <summary>
  /// Sets the current application role
  /// </summary>
  /// <param name="roleName">Application role name</param>
  /// <param name="password">Application role password</param>
  private void Impersonate(string roleName, string password)
  {
    var com = _connection.CreateCommand();
    com.CommandText = "sp_setapprole";
    com.CommandType = CommandType.StoredProcedure;

    // Quote from http://msdn.microsoft.com/en-us/library/ms188908.aspx:
    //   The cookie OUTPUT parameter for sp_setapprole is currently documented as varbinary(8000)
    //   which is the correct maximum length. However the current implementation returns varbinary(50).
    //   Applications should continue to reserve varbinary(8000) so that the application continues to
    //   operate correctly if the cookie return size increases in a future release.

    com.Parameters.Add(new SqlParameter("@roleName", SqlDbType.NVarChar) { Value = roleName });
    com.Parameters.Add(new SqlParameter("@password", SqlDbType.NVarChar) { Value = password });
    com.Parameters.Add(new SqlParameter("@fCreateCookie", SqlDbType.Bit) { Value = true });
    com.Parameters.Add(new SqlParameter("@cookie", SqlDbType.VarBinary, 8000) { Direction = ParameterDirection.InputOutput });
    com.ExecuteNonQuery();

    _cookie = (byte[])com.Parameters["@cookie"].Value;
    if (_cookie == null)
    {
      throw new InvalidOperationException(
        "Unable to set application role \"" + roleName + "\" (no cookie was returned from operation)");
    }
  }

  /// <summary>
  /// Unsets the application role.
  /// </summary>
  public void Dispose()
  {
    if (_disposed) return;

    // NOTE! There is never any guarantee that an application role will be unset.
    // If connection is closed or command fails, there's nothing we can do at this point.
    // If connection pooling is enabled, the connection might be reused at a later time but will
    // be unusable (errors will occur when trying to execute statements) to avoid security breaches.
    // Disable connection pooling to avoid this error ("Pooling=false" in connection string).
    // This might however have a negative impact on performance.

    if (_connection.State == ConnectionState.Open)
    {
      try
      {
        var com = _connection.CreateCommand();
        com.CommandText = "sp_unsetapprole";
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add(new SqlParameter("@cookie", SqlDbType.VarBinary, 8000) { Value = _cookie });
        com.ExecuteNonQuery();
      }
      catch (Exception)
      {
        // Can't unset role. See above.
      }
    }
    _disposed = true;
  }
}