SQL Audit Trail with pass through user credentials

For a customer we needed to add an audit trail to the database.

When you first start looking on the internet, you will probably find this link: http://www.nigelrivett.net/AuditTrailTrigger.html. The mayor problem with this audit trail is that the table will be huge because it stores the change of every field in a table in a seperate row. For our audit trail we needed a more simple approach: Just a shadow copy of the record which was edited. After some investigation I've found the build in SQL feature called 'Change Data Capture', which is only available in the enterprise edition. This is a little above our budget of 0 euro (as usual in IT it may cost nothing) ;). The other problem I found with Change Data Capture is that it isn't transparent enough. It does it's job, but nobody knows exactly how it works, I find this a bit tricky for our production environment.

So after just some more (2 hours!) searching I've come to the magnificent piece of SQL you'll find below from http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL.

After some minor adjustments it was ready to go!

But wait...

Our website users don't connect directly to the database, they use LINQ to SQL which has the user credentials of our Application Pool, so we still can't see who did the change :( StackOverflow has the answer: http://stackoverflow.com/questions/3197607/how-to-pass-out-of-band-current-user-id-data-to-sql-server-2008. Apparently there is an option to set CONTEXT_INFO on a connection so we can pass some extra data from the website user.

Time to hack LINQ! We want to execute the following statement everytime a new connection is used by the user:

SET CONTEXT_INFO 0x<binaryusername>

First we create a base class for our DataContext on which we fetch all Connection StateChange events. When the connection is opened send the 'SET CONTEXT_INFO' statement.

public class DataContextAuditBase
{
    public DataContextAuditBase(string connection) : base(connection)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(IDbConnection connection) : base(connection)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(string connection,
             MappingSource mappingSource) : base(connection, mappingSource)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(IDbConnection connection,
             MappingSource mappingSource) : base(connection, mappingSource)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    private string UserName
    {
        get { return (HttpContext.Current != null) ? HttpContext.Current.User.Identity.Name : Environment.UserName; }
    }

    private string StringToHex(string convert)
    {
        System.Text.ASCIIEncoding  encoding = new System.Text.ASCIIEncoding();
        return BitConverter.ToString(encoding.GetBytes(convert)).Replace("-","").ToLower();
    }

    private void ConnectionStateChange(object sender, StateChangeEventArgs e)
    {
        if( e.CurrentState == ConnectionState.Open)
        {
            ExecuteCommand("SET CONTEXT_INFO 0x" + StringToHex(UserName));
        }
    }
}

And change the BaseClass of the DataContext:

Run the newly created website and see the magic happening, right before your eyes!

The used audit trail stored procedure (used to create tables on which you want to use the audit):

/******************************
** Name: sp_GenerateAuditTable
** Desc: creates an audit table in a database of  your choosing from a user 
**       table that you specify in whatever database you run the sproc against
** Original Author: Cedric Baelemans
** Modifying Author: Andrew Tappert (2011-3-17)
** Modifying Author: Luuk Sommers (2012-04-17)
** Date: 2012-04-17
** 
** Found on: http://sameproblemmorecode.blogspot.com/2012/04/sql-audit-trail-with-pass-through-user.html
** More info on original: http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL
**************************/
ALTER PROCEDURE [dbo].[sp_GenerateAuditTable]
 @TableName varchar(128),
 @Owner varchar(128) = 'dbo',
 @AuditNameExtension varchar(128) = '_shadow',
 @DropAuditTable bit = 0,
 @AuditDatabaseName varchar(128) = null
AS
BEGIN
 declare @sql nvarchar(4000)
 
 if not exists( SELECT schema_name FROM information_schema.schemata WHERE schema_name = @Owner )
 BEGIN
  PRINT 'Creating chema [' + @Owner + ']'
  set @sql = 'CREATE SCHEMA [' + @Owner + '] AUTHORIZATION [dbo]'
  print @sql
  EXEC (@sql)
 END

 -- Check if table exists
 IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
  PRINT 'ERROR: Table does not exist'
  RETURN
 END
 
 IF @AuditDatabaseName is null
 BEGIN
   set @AuditDatabaseName = (select DB_NAME())
 END
 
 -- Check @AuditNameExtension
 IF @AuditNameExtension is null
 BEGIN
  PRINT 'ERROR: @AuditNameExtension cannot be null'
  RETURN
 END
 
 -- Drop audit table if it exists and drop should be forced
 IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
 BEGIN
  PRINT 'Dropping audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  set @sql = 'drop table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  print @sql
  EXEC (@sql)
 END
 
 -- Declare cursor to loop over columns
 DECLARE TableColumns CURSOR Read_Only
 FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
  FROM sysobjects a 
  inner join syscolumns b on a.id = b.id 
  inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname' 
  WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']') 
  and OBJECTPROPERTY(a.id, N'IsUserTable') = 1 
  ORDER BY b.colId
 
 OPEN TableColumns
 
 -- Declare temp variable to fetch records into
 DECLARE @ColumnName varchar(128)
 DECLARE @ColumnType varchar(128)
 DECLARE @ColumnLength smallint
 DECLARE @ColumnNullable int
 DECLARE @ColumnCollation sysname
 DECLARE @ColumnPrecision tinyint
 DECLARE @ColumnScale tinyint
 
 -- Declare variable to build statements
 DECLARE @CreateStatement varchar(8000)
 DECLARE @ListOfFields varchar(2000)
 SET @ListOfFields = ''
 
 declare @AuditTableExists int
 declare @existsParams nvarchar(4000)
 set @existsParams='@Exists int output'
 set @sql = 'if exists (SELECT * FROM [' + @AuditDatabaseName + '].information_schema.tables  WHERE table_schema=N''' + @Owner + ''' and table_name=''' + @TableName + @AuditNameExtension + ''') set @Exists=1 else set @Exists=0'
 -- Check if audit table exists
 print @sql
 Exec sp_executesql  @sql, @existsParams, @Exists=@AuditTableExists output
 print @AuditTableExists
 IF @AuditTableExists = 1
 BEGIN
  -- AuditTable exists, update needed
  PRINT 'Table already exists. Only triggers will be updated.'
 
  FETCH Next FROM TableColumns
  INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
   IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
   BEGIN
    SET @ListOfFields = @ListOfFields + @ColumnName + ','
   END
 
   FETCH Next FROM TableColumns
   INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
 
  END
 END
 ELSE
 BEGIN
  -- AuditTable does not exist, create new

  -- Start of create table
  SET @CreateStatement = 'CREATE TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ('
  SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'
 
  FETCH Next FROM TableColumns
  INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
   IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
   BEGIN
    SET @ListOfFields = @ListOfFields + @ColumnName + ','
  
    SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
    
    IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
    BEGIN
     IF (@ColumnLength = -1)
      Set @CreateStatement = @CreateStatement + '(max) '   
     ELSE
      SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '   
    END
  
    IF @ColumnType in ('decimal', 'numeric')
     SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '   
  
    IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
     SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
  
    IF @ColumnNullable = 0
     SET @CreateStatement = @CreateStatement + 'NOT '   
  
    SET @CreateStatement = @CreateStatement + 'NULL, '   
   END
 
   FETCH Next FROM TableColumns
   INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  END
  
  -- Add audit trail columns
  SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
  SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
  SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
  SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,' 
  SET @CreateStatement = @CreateStatement + '[AuditContext] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)' 
 
  -- Create audit table
  PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  print @CreateStatement
  EXEC (@CreateStatement)
 
  -- Set primary key and default values
  SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD '
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtension + '] PRIMARY KEY  CLUSTERED '
  SET @CreateStatement = @CreateStatement + '([AuditId])  ON [PRIMARY], '
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditApp]  DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp],'
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditContext]  DEFAULT (CAST(CONTEXT_INFO() AS VARCHAR)) for [AuditContext]'
 
  EXEC (@CreateStatement)
 
 END
 
 CLOSE TableColumns
 DEALLOCATE TableColumns
 
 /* Drop Triggers, if they exist */
 PRINT 'Dropping triggers'
 
 
 set @sql = '
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Insert]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]'')
 
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Update]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]'')
 
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Delete]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]'')'
 
 
 exec (@sql)
 
 /* Create triggers */
 PRINT 'Creating triggers' 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Insert ON [' + @Owner + '].[' + @TableName + '] FOR INSERT AS INSERT INTO ['+ @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted'
 print @sql
 EXEC (@sql)
 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Update ON [' + @Owner + '].[' + @TableName + '] FOR UPDATE AS INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted'
 print @sql
 EXEC (@sql)
 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Delete ON [' + @Owner + '].[' + @TableName + '] FOR DELETE AS INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted'
 print @sql
 EXEC (@sql)
 
END

Use the comments for any issues, questions and of course comments :)

Happy auditing!

Luuk

MD5 hasing in SQL Server 2008

Today I wanted to import a whole bunch of passwords in the database. The problem is, our passwords are md5 encrypted. The cool thing is, this is possible using SQL servers hashbytes function

HASHBYTES( 'md5', 'demo' )

this results in a varbinary value of: 0xFE01CE2A7FBAC8FAFAED7C982A04E229.
use the convert function to make this a readable string:

CONVERT(VARCHAR(MAX), HASHBYTES( 'md5', 'demo' ), 2)

for style make it a lowercase using the lower function:

LOWER(CONVERT(VARCHAR(MAX), HASHBYTES( 'md5', 'demo' ), 2))

+1 for SQL Server

Luuk

The row was not found at the Subscriber when applying the replicated command.

When you get the error as described below:

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000013DC0003277A006D00000000, Command ID: 2)

Error messages:
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598

Lookup the executed command by viewing the call (see my previous post) and update the stored procedure by commenting the rowcount check

ALTER procedure [dbo].[sp_MSdel_genTokenBalance]
  @pkc1 varchar(19)
as
begin  
 delete [gen].[TokenBalance]
where [TokenNumber] = @pkc1
--if @@rowcount = 0
--    if @@microsoftversion>0x07320000
--        exec sp_MSreplraiserror 20598
end

NOTE: don't forget to restore to the original when the problem is solved.

Good luck!

Preventing changes in one-way transactional replication

Last week we had problems with the replication. Our this weeks mission was to prevent this in the future. I've found a (really simple) way to prevent these changes which can cause duplicate keys and invalid foreign key references. With plain old triggers!

Just use the 'INSTEAD OF' together with the 'NOT FOR REPLICATION' keywords. Your triggers will fire when the action is done by everything but the replication. Also no auto increment is triggered because of the 'INSTEAD OF'. I've included the examples below.

CREATE TRIGGER TR_Account_PreventInsert on [Account]
INSTEAD OF INSERT NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO

CREATE TRIGGER TR_Account_PreventUpdate on [Account]
INSTEAD OF UPDATE NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO

CREATE TRIGGER TR_Account_PreventDelete on [Account]
INSTEAD OF DELETE NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO

The subscription(s) have been marked inactive and must be reinitialized

Yesterday we've found out that our SQL Server replication has been down for 2 days. After reinitializing the subscriptions I wanted to find out what caused the problem. After some searching on the internet I've found a very usefull Stored Procedure which can help you what the query is when you see replication errors.

use distribution
exec sp_browsereplcmds
 @xact_seqno_start = '0x<first 20 bytes of Transaction sequence number>',
 @xact_seqno_end = '0x<first 20 bytes of Transaction sequence number>'

But when the replication has been down for quite a while it's impossible to see what caused the problem in SQL. Allthough it wasn't the first thing that came to my mind, it can be found in the event log:


Hopefully this helps.