Determine the Database Evidence Repositories & collect the evidence files in this Sources that provide the valuable information are at times overlooked by the investigators. For instance, in intellectual property cases, databases containing finance related data are the prime targets for attackers to damage databases. In such case, source code repositories, knowledge management systems, and document management systems may provide better insights to the investigator to a suspected breach. Thus, investigators will be able to help the defendants against Invalid obligations.
The databases can be used for versioning and reviewing the document life cycle. The extended metadata, like descriptions, keywords and comments may provide insights into the document’s purpose. It discloses the information like who accessed and exposed the information and, where and when it was routed.
Location of Files to Restore the Evidence
Along with the Volatile database data, Windows logs and Database plan cache, investigators can examine the following files to have an insight of the activities occurred on the database:
- Database & logs files: \\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ DATA\*.MDF | *.LDF
- Trace files: \\Microsoft SQL Server\MSSQL11.MSSOLSERVER \MSSQL\ LOG\LOG_#.TRC
- SQL Server error logs: \\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ LOG\ERRORLOG
Collecting Volatile Database Data
Volatile Database is a RAM-style memory, which usually loses all its contents on power cuts. Investigators can track the volatile database information like login sessions of an account and the transactions using ApexSQL DBA’s ApexSQL Audit application.
By clicking on “Logon Activity history” in ApexSQL Audit application, the investigator can view the login history for a given date and time, as shown above.
Related Product : Computer Hacking Forensic Investigator | CHFI
Collecting Primary Data File and Active Transaction Logs Using SQLCMD
The primary data file (mdf) and active transaction logs Id play a key role in the forensic investigation. These files offer sufficient information to a forensic examiner for dealing with the investigation. A forensic examiner needs to know the location of mdf and ldf associated with a database, before proceeding with the investigation. The SQLCMD application helps an investigator to obtain the location of these files.
The SQLCMD application lets investigators load and establish a connection with the server.
To initialize connection with the server (WIN-CQQMK62867E), the following command is used in the application sqlcmd -s WIN-CQQMK62867E -e -s”,” -E
-e is used to echo input
- s is used for column separation
- E is used for trusted connection
The above command infers that we want to establish a trusted connection with the server WINCQQMK62867E and output the results of the forthcoming commands with the columns in the output separated by commas (,).
The following is to be issued in SQLCMD to create a new text file with name ForensicTest and save the output to E drive:
out E:\ForensicTest.txt
The sp_helpdb command outputs the information related to the specified database. A forensic investigator can use this command to determine the location of the primary data file and transaction log file that is associated with a database.
Transaction Log Files store log related information, which could be useful in recovering databases. It is divided into smaller parts called virtual log files.
The moviescope database files are stored in the VLF allocations. These allocations can be traced using the following commands in SQLCMD application.
Collecting Active Transaction Logs Using SQL Server Management Studio
As we know that the transaction logs store all the DL operations, along with some of the DDL operations, a forensic investigator can examine these transaction logs to see the transactions performed on the databases. However, since the logs are not in human readable format, it will be difficult for anyone without the knowledge of SQL to examine the log records.
Forensic investigators use undocumented functions like fn_dblog () and fn_dump_dblog () to view the transaction logs.
The function fn_dblog() accepts two parameters
- The starting Log Sequence Number(LSN) or NULL(returs everything from the start of the
log) - The ending Log Sequence Number(LSN) or NULL(returs everything to the end of the log)
Note: This function should not run against an active database instance.
Database Consistency Checker (DBCC) commands may give the investigator valuable insight into what is happening within the Server system. The DBCC LOG command allows investigators to view and retrieve the active transaction log files for a specific database. Following are the other DBCC commands that allow the investigator to obtain additional information related to the specified database.
- DBCC DBTABLE: Returns the structure of the selected database table
- DBCC DBINFO: Returns information related to the database metadata
- DBCC PROCBUF: Returns the contents of the SQL Server Procedure Buffer. The buffer contains SQL Server cached executable statements such as stored procedures and SQL queries.
- DBCC BUFFER: Returns the buffer headers and pages from SQL Server’s buffer cache, where SQL Server stores results.
- DBCC SHOWFILESTATS: Returns information related to the space occupied by the data files in the active database.
- DBCC PAGE: Returns the data page structure of the selected database
Also Read : Perform MSSQL Forensics
Collecting Database Plan Cache
To collect database plan cache, the following query is used in the application:
select * from sys.dm exec cached plans cross apply sys.dm_exec_sql_text (plan_handle)
Issuing sys.dm_exec_cached_plans in the syntax returns a row for each query plan that the SQL server had cached to speed up the query execution. This dynamic management view will help users to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
The command retrieves the SQL text of all cached entries. Note that the plan_handle argument in the syntax uniquely identifies a query plan for a batch that server had cached or is currently executing.
To collect additional plan cache specifics from the database, like viewing the aggregate performance statistics, the following query is used.
select * from sys.dm_exec_query_stats
The result contains one row per query statement within the cached plan, and the lifetime of the rows is tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
To view one row per plan attribute for the plan specified by the plan handle, the following query is used.
Select * fromsys.dm exe_cached_plans cross appl sys.dm_exec_plan_attributes(plan_handle)
It is to be noted that plan_handle in the syntax uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.
Collecting Windows Logs
Windows event logs are simple text files in XML format (EVTX) used by Windows Vista and later versions. Windows holds different types of logs including Administrative, Operational, Analytic, Debug, application, etc.
The Event Viewer in the Windows operating system (OS) allows the user to view the event logs on a local or a remote machine. Launch Event Viewer, expand Windows Logs node and select the type of logs (i.e., logs pertaining to the Application, Security, Setup, System, or Forwarded Events) need to be viewed.
In the forensic point of view, the event log files play a vital role, as these event logs track all the “significant events” on any computer. Any program that runs on the computer posts a notification in the event log, and simultaneously posts a notification before it ends. Events which include system access, operating system jerk, driver or any hardware issue, etc., are saved in the event logs. Investigators can use this data to trace out the attackers.
Collecting SQL Server Error Logs
As discussed above, Trace files record all the events occurred on the SQL Server and databases present in it, while SQL Server error logs record user-defined events and specific system events. The error logs also contain the IP Address of SQL Server client connections. A new error log file is created every time a new SQL Server instance occurs.
Forensic investigators may use SQL Server Profiler to view the trace files, and SQL Server Management Studio or any text editor to view the error logs. Both the files act as a very important evidence for the forensic examiner while conducting an investigation on the SQL Server.
Questions related to this topic
- How do I view SQL database logs?
- How do you check database logs?
- What is log file in database?
- How do I clean up SQL database logs?
- What is Database Evidence Repositories and how collect the evidence files?
This Blog Article is posted by
Infosavvy, 2nd Floor, Sai Niketan, Chandavalkar Road Opp. Gora Gandhi Hotel, Above Jumbo King, beside Speakwell Institute, Borivali West, Mumbai, Maharashtra 400092
Contact us – www.info-savvy.com
