Forensic log analysis in SQL Server
Investigate SQL Server Transactions Log for Forensic Analysis of Database Tampering
SQL Server Transaction Log
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. 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.
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.
- Recovery up to 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.
- Supports Transactional Replication: The database transaction log is monitored by the Log Reader Agent for the transactional replication.
- 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. 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.
Analyse the Default Location of SQL Log Files
- 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
Investigate Log Using fn_dblog() Function
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 starting log sequence number (LSN). Also, one specify NULL that means it will return everything from the start of the log.
- The ending log sequence number. Also, one can specify NULL that means users want to return everything to end of the log.
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.
Forensic Analysis of a SQL Server with SQL Log Reader Tool
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.
Steps to Forensically Analyze SQL Server Transaction Log Details
- Launch SQL Log Analyzer tool and click on Open to add the .ldf file.
- 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
- The software will start scanning LDF files and after this Scanning completed successfully wizard will pop up. Click OK
- 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
- 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.
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.