News We Recently Launched AD Migrator and AD Reporter.

SQL Transaction Log Forensics - Complete SQL Server Log Analysis

Investigate SQL Server Transactions Log for Forensic Analysis of Database Tampering

What is SQL Server Transaction Log?

Before we dive into SQL transaction log forensics , let's uderstand what a T-log file is in real. Just like many other RDBMSs, MS SQL Server also follows ‘Write-Ahead Logging’ methodology. It means all the transactions are written to log file before committing and it holds records of all the changes made to a database. For SQL forensics purposes, it's beneficial for users to know that logically, transaction logs are categorized into a few smaller parts known as VLFs or Virtual Log Files.

Every SQL database uses more than one VLF and each of them must have a minimum size of 512 KB. The size and number of virtual files in the log is evolving as the log is changing its size. When one log file is filled with transaction details then, transactions are written to the next available file. Here, we're going to perfrom SQL server forensics on these files only. MDF(primary database file) is not included here.

Basically, the log files are represented in a circular form so that if one file reaches its maximum limit then, it begins again from the starting point. SQL Server uses truncation process to mark the end of file or any unused part of log file so that it can be utilized to store the information. It's not difficult for users to perfrom SQL transaction log forensics if the right technique is known.

SQL Server Forensics
  • Supports Transactional Replication: The database transaction log is monitored by the Log Reader Agent for the transactional replication very carefully.
  • Recovery Up to The Point of Failure: The database can be restored to the exact point of failure, it restore a full backup of database and differential backup of the database.
  • Recover Individual Transactions: In case of database engine failure, the log records helps to roll back individual transactions and modifications that are done during the incomplete transaction.
  • Restore Incomplete Transactions: Due to failure in SQL Server, some of the transactions left incomplete. So, these transactions and modifications can be rolled back when the SQL Server begins. This is done to assure the database integrity.
  • Database Mirroring and Log Shipping: In Log Shipping the active log file of a primary database is sent to multiple locations by the primary server. In database mirroring, the principal server is utilized to send all the records instantly to mirror server.

Forensic Analysis Working Principle Log in SQL Server

Whenever SQL Server is told to do something with the help of query that is written in Structured Query Language syntax, the internal query optimizer of SQL Server checks the query, executes it, and retrieves the required information off of the disk. In case of retrieval query, the database is streamed to requesting client across the network. But, with modification query, it modifies the data pages in memory. Users must be aware of this for executing SQL server forensics carefully.

It does not write these modifications directly to the disk; well, not yet. So, what SQL Server does is it writes the logical transaction entries in the transaction log file with .ldf filename extension where all transaction records are executed. Eventually, after few seconds, SQL Server decides to write the modified pages out to the disk. While doing this, it navigates back to the transaction log and ‘checks off’ the transaction, which made the modifications.

This means the changes are done and been written to the disk. Thus, while performing SQL Server recovery, it goes directly to the transaction log search for uncommitted transactions or those that have not yet been checked off. SQL Server reads those transactions out of log then, re-executes them and quickly writes the affected database pages to the disk. In SQL transaction log forensics, this is one crucial aspect to understand.

Analyse the Default Location of SQL Log Files

Open SQL Server Management Studio and hit a right-click on the database. Select Properties

SQL Forensics

In the newly prompted window, click on Files menu and it will show the saving location of database files along with the saved name

forensics Analysis of sql server

Use fn_dblog() Function for SQL Transaction Log Forensics Investigation

The fn_dblog() function also known as the DBCC command is one of the various undocumented functions for MS SQL Server. It allows to view the transaction log records in the active part of a transaction log file for the current database. The fn_dblog() necessitates the following parameters to be passed:

The fn_dblog() is fairly simple and below is how to use this function to get info from the transaction log:

SELECT * FROM fn_dblog(NULL, NULL)

Now, fn_dblog will return all the transaction details so, select the transactions to analyze. The transaction results include Current LSN, performed operation, Transaction ID, Parent Transaction ID, Time, Transaction Name, and Transaction SID.

Transaction forensics

Forensic Analysis of a SQL Server Using the Automated Method

SQL Log Analyzer Tool is a professional and powerful utility to read and analyze the transactions of SQL log files in a safe manner. It forensically analyzes SQL log file transactions and performs LDF file recovery. As fn_dblog() function is a good choice however, it does not show the transactions and does not give the details about deleted records and their timings.

Besides, the tool displays a preview of all the activities performed in LDF file along with Transaction Name, Login Name, Time, Table Name, and Query. With this, one can read as well as analyze all the transactions like INSERT, DELETE, UPDATE etc. The software provides support to Datetime2, datetimeoffset, sql_varient, geometry and geography data types.

It has the capability to quickly scan, view LDF files and auto locate the associated Master database files. The tool allows to fetch and display records from the Live database. If the database is in Simple Recovery Mode then, users can recover deleted records. The best part of this tool is that it works in both online and offline SQL database environment and supports .ldf files of SQL Server 2017/ 2016/ 2014/ 2012/ 2008/ 2005. In a nutshell, it will handle your SQL server forensics tasks very easily.

Steps to Forensically Analyze SQL Server Transaction Log Details

1. Launch Automated Tool and Click on Open to add the .ldf file.

Add ldf

2. The tool offer two options to add file Online DB Option and Offline DB Option. If Online DB Option is selected then, the tool will allow to choose Server Name by clicking on drop down list. Select the Authentication mode. Hit drop-down arrow to Select Database and click OK

Select mode

3. The software will start scanning LDF files and after this Scanning completed successfully wizard will pop up. Click on the OK button.

forensic analysis of a sql server

4. The tool display preview of transactions. Select the desired Tables to preview and analyze the corresponding operation log entries. It sort the transactions on the basis of Login Name, Time, Table Name, and Transaction Name. Click Export button.

Preview forensic activity

5. You can apply export filters, Date Filter accordingly to export the transaction records of a particular date range. You have option to export database in either SQL Database or as csv. Click Export to save records.

Export log Forensics data

Conclusion

After learning SQL transaction log forensics tactics, we're at the end now. This technical page comprises a complete information on how to forensically investigate SQL Server transaction logs, including their location and working procedure. Apart from all this, we also have disclosed two different ways to examine the details of transaction logs of SQL Server.

The fn_dblog functioning helps to detect all the performed transactions. However, if users are finding the manual method complex, lengthy, and time-taking then, a professional solution is also provided here. It is one of the safest solutions to get adequate results.