Take the DB Audit Challenge - Intermediate   - DB Audit Challenge #1

For those of you who have been following my recent blogs, as well as those who attended the SQLSaturday October 24 event in NJ, know that my presentation covered several topics with respect to database forensics.  I demonstrated the various tools and methods, by leveraging the native out-of-the-box features in SQLServer, to audit and recover data.  Topics included DDL Triggers, the Default Trace, CDC, and the Transaction Log.  I gave a review of db internals on how to read the transaction log through the very useful system function: fn_dblog.

I present to the SQL Community at-large the first of perhaps a series of DB Audit Challenges below.

In order to decipher the puzzle, I am including the schema of a simple table called Item, with the column names, data types and length shown in Figure 1A.  In addition, I am including the hexadecimal output of the RowContents0 field from the Transaction Log of a captured Delete Operation, otherwise known in the log as an LOP_Delete_Rows operation executed against the Item table in Figure 2A.  Deletes are a logged operation and therefore can be recovered from the transaction log.  These records are known as ‘Ghost Records’.

Using the information provided, I am asking you to reconstruct the data row, and give me the exact contents of the ItemDescription field.  In this field is a simple message apropos for this time of year.

If you can reconstruct the contents of the entire row, I will consider it a bonus, but just want to see how many people can tell me what it is that’s in the ItemDescription column.    I’ll run this challenge for the week, and will drop some clues and hints throughout to solve the puzzle.

Ok, if you need some help, you can use the book called Inside Microsoft SQL Server 2005: The Storage Engine – by Kalen Delaney.  Also, if you were fortunate enough to catch my presentation, you received some handouts that can also help!

OK, so this is an internals exercise on translating the contents of the Transaction Log.  If you are up to the challenge, then it’s time to get busy, and send your answers to sqlcentric@pearlknows.com.

I will post the results of how many folks got the right answer. 

   Figure 1A

Please note, I’ve formatted the output below into four rows for readable format.  Take the four rows and concatenate it into one string, as you begin you journey to find the hidden message, and reconstruct the row.   I will consider giving away some prizes, depending on how many people get the right answer.   The first official hint is, remember, that all fixed columns appear first in the log, followed by variable-length columns. 

 I hope you find this interesting.  Good Luck and stay tuned to this blog for more clues!

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Coming soon!  Our fully integrated SQL Server OS Live Performance Dashboard!

In the meanwhile for SQLCentric monitoring, and remote DBA services, check us out at

http://www.pearlknows.com