Investigate SQL Server Transactions Log for Forensic Analysis of Database Tampering
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.
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.
Open SQL Server Management Studio and hit a right-click on the database. Select Properties
In the newly prompted window, click on Files menu and it will show the saving location of database files along with the saved name
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.
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.
1. Launch Automated Tool and Click on Open to add the .ldf file.
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
3. The software will start scanning LDF files and after this Scanning completed successfully wizard will pop up. Click on the OK button.
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.
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.
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.