Database Forensics Paresh Motiwala SQL Solutions Architect at

Database Forensics Paresh Motiwala - SQL Solutions Architect at www. actifio. com

pareshmotiwala@gmail. com http: //www. linkedin. com/in/pareshmotiwala http: //www. facebook. com/pareshmotiwala http: //www. circlesofgrowth. com yout text here

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. Introduction Goals Breaches File Formats Methodology Incident Preparedness Incident Verification Artifacts- Collection, Verification, Analysis Log Readers Demo Q&A Bibliography

Database Forensics 1. Introduction 2. Goals 3. Breaches 4. File Formats 5. Methodology 6. Incident Verification 7. Artifacts- Collection, Verification, Analysis 8. Log Readers 9. Demo 10. Q&A 11. Bibliography

Database Forensics Goals a) b) c) d) e) Prove or disprove the occurrence of a data security breach Determine the scope of a database intrusion Retrace user DML and DDL operations Identify data pre- and post-transactions Recover previously deleted database data

Database Forensics Introduction a) Breaches b) e. Discovery

Database Forensics Breaches

Database Forensics File Formats

Database Forensics Methodologies Investigation Preparedness Incident Verification Artifact Collection Artifact Analysis

Database Forensics Incident Preparedness 1. Configure your forensics workstation(Server/WS) 2. Create a SQL Server forensics IRT 3. Develop SQL Server incident response scripts. 4. Integrate base scripts with automated live forensic suites (optional).

Database Forensics Incident Verification Identifying signs of penetration: A. SQL Server Penetration B. Active unauthorized SQL Server Connections C. Past unauthorized SQL Server access a) SQL Server error logs b) Plan Cache c) Session details yout text here

Database Forensics Artifacts 1. Volatile: 1. 2. 3. ( sqlcmd- : out c: dbse_loginfo. txt, dbcc loginfo go) ; select * FROM sys. dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR‘; Data Cache, Plan Cache, Recent executed statements, Active connections, Active sessions, Active VLFs, Ring Buffers 2. Non-Volatile- Default Trace Files 3. Pre-planned 4. Configuration 5. Constant update Item Importance Volatility Priority SQL Server Connections & Sessions 5 5 0 Transaction Logs 5 4 1 SQL Server Logs 4 3 3 SQL Server Database Files 3 2 5 System Event Logs 2 2 6

Database Forensics Artifacts –Collection Summary of Volatile SQL Server Artifacts Automate d Artifact Ad Hoc Collection Artifact (WFT) Collection Data cache ♦ Cache clock hands ♦ Plan cache ♦ Most recently executed (MRE) statements ♦ Active connections ♦ Active sessions ♦ Active virtual log files (VLFs) ◊ Ring buffers ♦ ♦

Database Forensics Artifacts –Analysis 1. Pre analysis – • • • 2. Security Audit- Use of Honeypot. . . • • • 3. 4. 5. Create an Image Use write blockers Create a repository (database) Audit level Log history History of suspect SQL Logs System Event Viewer Logs Profiler-Trace or Monitoring software like Idera

Database Forensics Log Readers 1. 2. 3. 4. Expensive Pre-planned Configuration Constant update

Database Forensics Q&A - Bibliography http: //www. bmyers. com/public/1958. cfm SQL Server Forensic Analysis By: Kevvie Fowler, K. (2007). Forensic analysis of a sql server 2005 database. Informally published manuscript, .

Database Forensics THANK YOU No Oracle DBA was hurt in the making of this presentation As prudent investigators, our job is to find the clues that the perpetrator doesn’t know he/she left behind. William Petersen CSI 2001

Explore Everything PASS Has to Offer Free SQL Server and BI Web Events Free 1 -day Training Events Regional Event This is Community Business Analytics Training Local User Groups Around the World Session Recordings PASS Newsletter Free Online Technical Training