CHFI11

CEH chapter 11 summary

Database forensics involves the examination of the databases and related metadata in a forensically precise manner to make the findings presentable in a court of law

Examine the MAC attributes of tables that could verify the actions of the attacker Determine the transactions occurred within a database system or applications that indicate evidence of fraudulent activities Recover the deleted rows Retrace the DDL and DML operations performed by the attacker

1. Primary Data Files The primary data file (MDF) is the starting point of a database; it points to other files in the database. Every database has a primary data file that stores all data in the database objects (tables, schema, indexes, etc.). The file name extension for the primary data files is .mdf.
2. Secondary Data Files The secondary data files (NDF) are optional. A database contains only one primary data file, but it can contain zero/single/multiple secondary data files. The secondary data files can be stored on a hard disk, separate from the primary data file. The file name extension for the secondary data files is .ndf.
3. Transaction Log Data Files The transaction log data files (LDF) hold the log information associated with a database. A transaction log file helps a forensic investigator in examining the transactions that occur in a database and recover the deleted data, if required. The file name extension for the transaction log date files is .ldf and each file is divided into multiple virtual log files.

Each data file (excluding log files) contains multiple data pages (basic storage units with a storage capacity of 8 KB)

The data pages are divided into: Page Header – Presents the page ID, page type, etc. Data Rows – Store the actual data Offset Table – Points to the location of the actual data

SQL Server data is stored natively within the SQL Server, and externally in the Windows machine hosting the server

▪ Database and log files \Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA*.MDF | *.LDF ▪ Trace files \Microsoft SQL Server\MSSQL14.MSSQLSERVER \MSSQL\ LOG\LOG_#.TRC ▪ SQL Server error logs \Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\LOG\ERRORLOG

Collect volatile database information such as user login sessions, and user transactions Use ApexSQL DBA’s ApexSQL audit application to track the login history

Investigators can track volatile database information such as the login sessions and transactions of an account using ApexSQL DBA’s ApexSQL Audit application. By clicking on “Logon Activity History” in the ApexSQL Audit application, investigators can view the login history for a given date and time.

Primary data+ active translation logs Connect to a server with the command sqlcmd -S WIN-1BKS09O92OO -e -s”,” -E (WIN-1BKS09O92OO is the server used for this demonstration) Issue :out E:\ForensicTest.txt to create a text file named “ForensicTest” and log the output of the collected data to E:
Issue the commands sp_helpdb moviescope and go to determine the locations of the transaction log files associated with moviescope database The result will be recorded in E:\ drive in the respective file (ForensicTest.txt) Issue the commands dbcc loginfo and go to obtain the VLF allocations for the moviescope database The result will be recorded in E:\ drive in the respective file (ForensicTest.txt)

sqlcmd -S WIN-1BKS09O92OO -e -s”,” –E Parameters: -e is used to echo the input -s is used for column separation -E is used for a trusted connection

Collect the database files (.mdf) and log files (.ldf) from C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER \MSSQL\DATA These files contain the entire data (in primary data or .mdf files) and logs (in transaction log or .ldf files) related to the databases

The fn_dblog() function allows us to retrieve the active portion of the transaction log file fn_dblog () function filters transactions by: Targeting the database object Specific columns SPID and/or date/time range Issuing the query Select * from ::fn_dblog(NULL, NULL) displays the active portion of the transaction log file as shown in the screenshot Assigning NULL values implies that the start and end points for log sequence numbers (LSNs) are not specified

The DBCC LOG command allows us to retrieve the active transaction log files for the specified database Syntax: DBCC LOG(,

) The output parameter specifies the level of information that a forensic examiner wants to retrieve 0= minimal information of each operation such as the Current LSN, Operation, Transaction ID, etc. 1 = slightly more info than 0, such as Flag Bits, and Previous LSN 2 = detailed information, including (AllocUnitId, page id, slot id, etc.) 3 = full information about each operation 4 = full information on each operation along with the hex dump of the current transaction row Issue the query DBCC LOG(moviescope, 3) to view the transaction log file for the moviescope database, with the detailed information for each operation

Syntax: DBCC LOG(,

) The output parameter specifies the level of information that a forensic examiner wants to retrieve: ▪ 0= minimal information of each operation such as the Current LSN, Operation, Transaction ID, etc. ▪ 1 = slightly more info than 0, such as Flag Bits, and Previous LSN ▪ 2 = detailed information, including (AllocUnitId, page id, slot id, etc.) ▪ 3 = full information about each operation ▪ 4 = full information on each operation along with the hex dump of the current transaction row

▪ 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 the SQL Server buffer cache where the results are stored ▪ DBCC SHOWFILESTATS: Returns information related to the space occupied by the data files in an active database ▪ DBCC PAGE: Returns the data page structure of the selected database

Issue the syntax select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text (plan_handle) to retrieve the SQL text of all cached entries The plan_handle argument retrieves the compiled query plans from the SQLCP or the OBJCP cache stores Issue the syntax select * from sys.dm_exec_query_stat s to view the aggregate performance statistics for the cached query plans. It displays only one row per query statement

Windows Logs store the logon events performed on the SQL Server Launch Event Viewer, expand Windows Logs node and view various Windows event logs

To collect the trace files (.trc), navigate to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\LOG The trace files contain the events that occurred on the SQL server and the host databases

To collect the SQL Server error logs, navigate to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\LOG The SQL Server error logs contain user defined events and specific system events

Timeline construction: Step 1: Examine the Windows Logs Examine the Windows Logs to obtain information related to SQL Server authentication, start-up and shutdown instances, and the IP addresses of client connections it is observed that an event associated with the server login and pertaining to the MSSQL Server is recorded Now, the error logs must be examined to determine any successful login event

Step 2: Examine the Error Logs Navigate to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQ L\LOG and open the ERRORLOG file with Notepad Examine the log file to see the records of user-defined events (such as user logins) Here, it is evident that there is a successful login instance recorded for user sa. Now, the trace file can be viewed to determine SQL Server-based events associated with this us

Step 3: Examine the Trace Files Navigate to C:\Program Files\Microsoft SQL Server\ MSSQL14.MSSQLSERVER\MSSQL\LOG and check the trace files. The trace file opens in SQL Server Profiler. Examine the file to identify any suspicious activity By examining the file, some user-based events were observed on the moviescope database. Make a note of the SPID and the start time of the instance

Step 4: Examine the Active Transaction logs Launch the SQL Server Management Studio and connect to the SQL Server Execute the command dbcc log(moviescope, 3) in the query window to view the transaction log file for the moviescope database, with detailed information for each operation

Here, an event can be observed (SPID: 74 and Transaction ID: 0000:000008ae) with a modified row. 1 - Indicates the beginning of a transaction 2 - Indicates the type of transaction performed 3 - Indicates the end of a transaction 4 - Unique transaction identifier 5 - Indicates the process ID of the current user 6 - Data Page Identifier for rows showing the updated record 7 - On data page row location of record 8 - In row data offset of modification 9 - Value of the row before modification 10 - Value of the row after modification

Step 5: Examine the Data Page Now, we inspect the modified data pages to find the object ID where the data has been modified Now, the error log needs to be examined to find out any successful login event. Execute the commands: dbcctraceon(3604)dbccpage(moviescope,1 ,154,1) to view the 154th data page on the query window The PAGE HEADER contains information regarding the data page such as the type of page, partition ID, and object ID. Note down the Object ID

Step 6: View the Object Next, we use the object ID to find the name of the object/table in the moviescope database, whose data was modified Execute the command Select * from sysobjects where id = 21575115 The object User_Profile has been modified. Next, we use the same object ID to obtain the object schema

Step 7: Gather the Object Schema Execute the command: SELECT sc.colorder , sc.name, st.name as ‘datatype’, sc.length FROM syscolumns sc, systypes st WHERE sc.xusertype = st.xusertype and sc.id = 21575115 ORDER BY colorder By issuing the above command, the object schema is obtained. One of the entries in the table is subject to modification

Step 8: View the Modified Record As observed in Step 4, the page ID is 154 and slot ID is 4. Therefore, issue the commands dbcc trace (3604) dbcc page(moviescope,1,154,1) To view the data page 154, scroll down to slot no. 4 (data row no. 4)

Step 9: Identify the Data Type We can now further investigate using slot ID 4 and row offset 8 as obtained from the transaction log to look for the specific point in the data row where the transaction started As per the object schema (table) previously obtained, we can see that the data type within this row offset is the age column which displays a 4-byte int data type

Step 10: Compare the Row Logs Note down the hex values of RowLog Contents 0 and RowLog Contents 1 and convert them to their equivalent decimal values. Thus, it is evident that the age entity was tampered from 25 to 200, which was successfully determined using forensic investigation

Forensics using ApexSql DBA Step 1: Collecting Volatile Database Data Obtain volatile database information such as user login sessions, and user transactions Use ApexSQL DBA’s ApexSQL Audit application to track the login history A login event can be observed with the username anonymous from the client host (WIN-1BKS09O92OO)

Click on Security configuration history to view the security related entry modifications It can be observed that the transactions occurred on the goodshopping database

Step 2: Examine the Database Transaction Log File Launch ApexSQL Log and establish a database connection with the goodshopping database Select the goodshopping_log file in the Data Sources section Select the “Open results in grid” option in the Select output section Configure the options in the “Filter setup” section and click on Finish The ApexSQL Log application examines the log file and displays the transactions occurred on the database A delete operation was observed on the login object by the username “anonymous”. The deleted entries (username: smith and password: smith123) can be observed under the Operation details tab Similarly, examine the Row history, Undo script, Redo script, and Transaction information tabs to obtain more information about the transaction

ApexSQL DBA Source: http://www.apexsql.com ApexSQL offers the most comprehensive set of SQL Server DBA and Developer tools, such as ApexSQL Audit and ApexSQL Log, that automate critical tasks for database administration. ApexSQL Audit ApexSQL Audit is an SQL Server auditing tool that provides auditing access, changes, and security on SQL Server instances, databases, and objects. It audits queries, DDL and DML operations, security events (authentication changes, permissions changes, and attempted logins), events on stored procedures, and functions. ApexSQL Audit saves the captured information in a centralized auditing repository and provides comprehensive reports. By analyzing the volatile data with ApexSQL Audit, forensic investigators can obtain information on the login activities, the clients connected to the server, and the database on which the transactions occurred. ApexSQL Log ApexSQL Log is an auditing and recovery tool for SQL Server databases that reads the database transaction logs and audits, reverts or replays data, and analyzes the object changes affecting the database. It restores updated or missing data and objects and captures information related to the users, applications, and hosts used to make each change. Forensic investigators use an SQL transaction log reader for forensic auditing and rollback of malicious or inadvertent database changes.