Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Forensic Analysis of Database Tampering on a Specific Time In SQL Server

DZone's Guide to

Forensic Analysis of Database Tampering on a Specific Time In SQL Server

We are going to describe proven methods that can list out the time and what modification is done in the database from T-Log file.

· Database Zone ·
Free Resource

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

Microsoft SQL Server is the best relational database management system that is used by various companies to maintain their crucial databases. However, there are some circumstances — like someone intentionally changing the database — where organizations need a method through which they can track what last modification was done in the database and at what time so they can take appropriate action.

Help of Transaction log file is needed. Actually, T-Log file is a very crucial component of SQL Server database. Because it keeps track of all transaction happens in database and modification done by each transaction. In fact, it is used to restore the entire database in a system failure situation. Along with this, SQL Transaction log file is the only way used in forensic analysis of database tampering on a specific time.

We are going to describe proven methods that can list out the time and what modification is done in the database from T-Log file.

Techniques Used for Forensic Analysis of Database Tampering on Specific Time

Originally, there are two methods through which from T-log files, information related to the database modification can be retrieved. Here, both of the workarounds are mentioned in a detailed manner.

#Approach 1: Use Undocumented Function fn_dblog

The fn_dblog() comes under the list of undocumented functions that used for forensic purposes in SQL Server. Because it is used to display the content of T-Log file. This information comprises crucial status about the things that modified in the database.

How to View Database Changes Along With Time

To understand how to use fn_dblog() in depth, we have created a table whose name is Employee and modified it by the delete operation. After that, with the help of fn_dblog(), we will get the time when the table was last modified from Transaction log.

  • To view the contents of the table, run the ‘Select’ query as shown in the screenshot:

employee table

  • After that, execute the below command to eliminate one row from employee table:

delete records

  • Next, run the ‘Select’ query to again view the modified table contents:

fetch table data

  • Now, use fn_dblog() function to view the time of deletion. If we use Null in the function, then all the log records get displayed on the console. For this, you have to execute the below SQL query:

fetch transactions

  • If you want to view all the operations such as update, delete, and insert, then run the below command:

fetch all transactions

Consequences Associated With Fn_dblog() Function

The problem with the manual way is that it shows the time of associated operation (update, deleted) and not which data row and column was deleted in the entire SQL Server database. So, the users become unable to identify which information was removed exactly. This problem is overcome in the next technique, where the user can easily view the modified content and restore it.

#Approach 2: Use SysTools SQL Log Anaylzer Tool for Forensic Analysis of T-Log File

SysTools SQL Log Analyzer is an outstanding utility that deeply examines the T-log file of SQL Server and restores data from it. A user can get the details like Time and Date, Transaction Name, and Query and export the modified data back into the database. The tool works on an online as well as an offline database. To use the online database, the user needs to provide the server name and authentication mode. On the other hand, in offline mode, the exact location of LDF and MDF are needed. After that, select the database and further operation done by the software.

Things You Can Do With SysTools SQL Log Analyzer

  • In-Depth Scanning

This software is built with a highly advanced algorithm that allows in-depth examination of transaction log file. The entire database saved in T-log file loaded on the left side panel of the tool. One can click on any database to get details about it.

Forensics analysis of database tampering

  • Detailed View of T-Log Information

This tool provides you complete details about the last changes that happened in the database with time-stamp. Because of this reason, it is best suited for a forensic analysis of database tampering on a specific time. The following information is what a user can get from the software:

  • Transaction such as Insert, Update & Delete
  • Login Name
  • Date & Time
  • SQL Query

When a user clicks on any row, the complete data of table gets displayed on the bottom detailed pane:

sort database transactions

  • Recover the Modified Data

After scanning, one can export data back into the database. You only need to select or deselect the transaction that you want to restore. Along with this, you can apply Date Filter by which only a particular range of database gets restored. Besides this, there are three options to export the database i.e., SQL Server Database, SQL Scripts, or CSV. Moreover, you are free to select the destination like Create New Database or Export to Existing Database.

save recovered records

Bringing It All Together

That’s all about the forensic analysis of database tampering on a specific time in SQL Server. In this post, we have covered the best two ways that can track modification happenings in the database along with time. Due to some downsides in fn_dblog(), it is suggested to use SysTools SQL Log Analyzer. It is a great way to view all the modifications done in a database and restore the data back into SQL Server.

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:
forensics ,sql server ,transaction logs ,database ,tutorial ,database tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}