Instigate SQL Log Forensics Investigation Via SQL Server Log Analyzer
Fraudulent incidents in business organizations are inevitable and can happen when you least expect them. Most of the cyber crimes in recent times have been carried out with the help of emailing. But scamming and frauds are not limited to just emails and email clients. They have their roots extending to your servers also.
One of the widely deployed servers used in large organizations is SQL server. Scamming done through SQL server demands a thorough analysis of the SQL log files. Therefore, the first step that must be performed in SQL server is the deep analysis of the SQL database files and to carve out evidence from the database i.e. SQL Log Forensics.
Investigation and examination of forensic artifacts from SQL, log files can be performed by manual methods, but forensic experts usually rely on external forensic tools for the same. This is basically due to the fact that the investigation via forensic tools reduces extra efforts. One such utility is SQL Server Log Analyzer.
This utility offers maximum precision in carving out the forensic evidences whilst consuming least possible time. The tool serves dual purpose at the same instance. It offers a detailed examination of SQL log files and facilitates recovery of MDF file via last log available in the log file. An elaborated description of SQL Log Forensics has been given in the later part of the write up.
SQL Log Files: Critical Assets For Forensics
Evidence repositories of SQL server basically are in the form of Log files. SQL Server maintains a record or journal of every database modification and transaction in transaction log files. Like Main Database Files, Log Database Files (LDF) hold utmost importance in a SQL server.
This is because in the scenario of system failure, transaction logs are used so as to bring back the server in a running and consistent state. The events or changes that take place in SQL server are not immediately written to the data files. But they get stored in the log file and then transferred to the data file.
Detailed Analysis Of Transaction Files (Possible via SQL Server Log Analyzer)
For closely analyzing the SQL database file, named Merchandise, under investigation SQLCMD session is used and following SQL command is run:
When the above command is run, the following details are generated. The information the details reveal is that the Merchandise database is currently using one .mdf file and 2 .ldf (arising need of SQL LDF File Forensics) files at different locations in a Windows drive.
For analyzing the database separately, a copy of the log contents is copied to another location with the help of following SQL query:
For better investigation, the transaction log files can be exported to Microsoft Excel. There are over 100 parameters that generate relevant data, but for our investigation, we have limited our usage up to some parameters. A list of all the parameters useful in our investigation is listed in the table below:
Virtual Files
SQL Server splits its data logically into smaller segments refereed as virtual files. in other words virtual files can be said as the truncation unit of the log files. The number of virtual files created varies from 4-16 files (VLFs)5.
These files can be marked as active at any instant and then can be used to store the transactions. Once the database from transaction file is written to the physical drive, the virtual files which contain committed transaction are marked as reusable and are used for future storage.
The following command is run in the server to get an idea of the logical allocation of the transaction files:
This command will fetch the information that will reveal whether the transaction log file was split into active and also reusable virtual files. These files may contain relevant data which helps in the investigation process. Here parity 2 means that the virtual file is active and shows that the file is unused.
The most important resource for forensic investigators other than the evidences is time. The most effective forensic investigation is characterized by least amount of time consumed during the whole process. The extra time consumed during manual analysis of SQL server log files kills the efficiency of the investigation process. Thus it is wiser to deploy SQL Log Forensics tool for the same.
How Does SQL Server Log Analyzer Facilitates Forensics Investigation Process?
Whenever a manual investigation of SQL log files is performed different commands are run in order to fetch so as to view the log database. The SQL Log Analyzer tool facilitates the investigation process by exempting the user from running commands manually.
The tool allows opening, viewing and analyzing all the activities stored in the log file, carried out by SQL server. It works as a combined utility, providing users the facility to analyze the SQL database log files as well as recovering the deleted MDF file with the help of associated Log file.
To enhance the investigation process and to ease the efforts put forth by the forensic investigators in analysis of SQL Log files, the tool is equipped with some excellent features. These features help the investigators to yield a better output of their investigation and that too in the least possible time.
Advanced Features In SQL Log Forensics
- Detailed Preview Of Log Activities
SQL Log Forensics scans and loads all the activities stored in the database log files. Activity details like transaction name, date of the transaction, table name, query, etc. are can be easily previewed and also can be saved.
- Export Log Files In Different Ways
The SQL Server Log Analyzer lets the investigators to export and save the SQL log queries in three different possible ways. The queries can be exported and saved in SQL server database, in SQL compatible scripts or as a CSV file.
- Analyze Evidences Without SQL server
The software allows to view the carved out log files in an SQL free environment also. At times investigators collect evidences and desire to examine them separately. This can be achieved with the help of SQL Log Forensics.
Summary
The SQL Log Forensics is the most efficient SQL LDF file forensics tool that can be deployed by forensic investigators to give a new turn to their investigation process. The tool helps the investigators to examine the artifacts in the most efficacious way possible.