1 Going Right to the Data Part 2 An Advanced Look at MS SQL Forensic Analysis Matt Bromiley Jacob Christie 

2 • Who We Are • Approaching SQL Analysis • Case Study • The Investigation • How Do We Get Better? • Conclusion Agenda 

3 Who We Are 

4 Matt Bromiley works at Mandiant, having lived a previous life working with crazy chefs and 2 of the Big 4 [@]505forensics[.com] Who We Are Jacob Christie is a Consultant at Mandiant, having cut his teeth doing desktop support and having done forensics at 2 of the Big 4 jc4n6.blogspot.com 

5 Approaching SQL Analysis 

6 Approaching SQL Analysis Page 6 • The closer you are to the breach… …the more valuable SQL artifacts will be …the more SQL artifacts will be available 

7 Approaching SQL Analysis (cont.) Page 7 • Consider artifact volatility • DBs live largely in memory • DB admins are employed to make them run more efficiently (aka more memory) • One service restart/system restart wipes away volatile data • We’re not saying don’t patch • We’re saying detect better 

8 Approaching SQL Analysis (cont.) Page 8 • Consider what you are trying to achieve • First goal is never “dump all the DBs” • What questions are you trying to answer? • Host access? • Database access? • Data access? • Data sensitivity? 

9 Approaching SQL Analysis (cont.) Page 9 • Consider your timeframe • How long ago was the breach? • What has happened to my DB server during that time? • Do I actually have any DB artifacts available? • Do I have any supporting artifacts available? 

10 Case Study 

11 The client: A multinational education company that assists in professional certifications and continuing education. The problem: Client received an email from an attacker, stating that they hacked into the company and stolen sensitive database information. Attacker provides a sample of stolen data that the client determines to be legitimate. Attacker offers not to release data in exchange for Bitcoins The question: Did the attacker actually access the database? If so, what data was accessed, what data was stolen, and how to mitigate against future attacks? Case Study 

12 How did Mandiant help? • Acquired as many SQL logs as we could get our hands on!!  Event logs, SQL error logs, transaction logs, web server logs for DB application server • Used scripts to obtain a copy of the database schema to understand architecture • Additional scripts to dump plan cache, user activity, DB stats, and recent transactions • Worked closely with database administrators to gain insight into linking and view structures  Necessary to understand how complex database environment is; how crucial is one DB vs whole set Case Study (cont.) 

13 What did we find? • Rogue application signatures; non-existent hosts making SQL queries  Attacker initially compromised Linux web servers, installed drivers and scripts to access data  Attacker setup tunnels that allowed them to query at their leisure saving results locally (danger!?) • Mapped queries to attacker access to see what was at risk  Attacker queried multiple data tables; did not acquire linking tables  Attacker had a lot of metadata; very little actual (sensitive) data • Temporal correlation with external logs (firewall) allowed us to find attacker IPs and entry vectors Case Study (cont.) 

14 The Investigation 

15 • Client received an extortion email from an attacker. • Attacker claims to have gained access to the company databases; has dumped account passwords and other sensitive data. • Client has contacted your team for assistance in analyzing DB artifacts to determine if attacker actually accessed DBs • Would you advise the client to just pay the ransom? The Investigation Background 

16 • What has been provided to you: • Windows Event logs from the SQL Server • Targeted SQL query results • MSSQL\LOG directory contents • Database schema • The SYSTEM registry hive from the SQL controller • Additional registry output The Investigation Artifacts 

17 • Questions we want to answer: • Did the attacker access the DB server? • Did the attacker access the DB? • If so, what queries were run? • What artifacts support our case? The Investigation Artifacts (cont.) 

18 • Let’s start with Remote Access to the DB server • Client believes the SQLUser account may have been compromised • …That’s all they’ve got. The Investigation Analysis 

19 • Event logs! • Security Event IDs (depends on OS; Win2k8R2 in this case): • 4624 • Logon Types: 3, 10 • Local Session Manager Event IDs • 21 (Successful logon) • 22 (Shell start notification) The Investigation Analysis (cont.) 

20 The Investigation • Local Session Events can quickly help us “book-end” activity periods • Need to parse out message fields to get user accounts and context • What tool can we use to examine event logs? • Windows Event Viewer (native) • Log2timeline/Plaso • EVTX parsers • … Analysis (cont.) 

21 The Investigation • Let’s dig deeper: • Security Event Log (EID 4624): RDP-specific event log (EID 21): Analysis (cont.) 

22 The Investigation • Use our event logs to map out user account activity to the host • Timeframes become guides for the rest of our artifacts • Do we have any new IOCs? • Task your team to go investigate 192.168.1.101! Analysis (cont.) Session Start Session End Hostname Username 2016-05-25 08:44:00 2016-05-25 09:10:14LOCAL SQLSERVER\forensics 2016-05-25 09:11:48 2016-05-25 09:27:08LOCAL SQLSERVER\forensics 2016-05-25 10:00:45 2016-05-25 10:05:54LOCAL SQLSERVER\forensics 2016-05-25 10:05:55 2016-05-25 10:08:54192.168.1.101 SQLSERVER\SQLUser 2016-05-25 10:09:26 2016-05-25 10:17:07192.168.1.101 SQLSERVER\SQLUser 2016-05-25 10:19:26 2016-05-25 10:29:08LOCAL SQLSERVER\forensics 2016-05-25 10:30:54 2016-05-25 10:35:54192.168.1.101 SQLSERVER\SQLUser 2016-05-25 10:35:55 2016-05-25 11:28:43LOCAL SQLSERVER\forensics 2016-05-25 11:31:45 2016-05-25 11:32:32LOCAL SQLSERVER\forensics 

23 • What was our DB server doing during this time? • SQL ERRORLOG • Plain text (FINALLY!), can be opened by ${insert_your_fav_text_editor} • Tracks startup, shutdown, login, logout, authentication success/failures • Oddly enough, not just errors.. • Can be removed from a system and preserved forensically • Examined later on a separate, analysis system The Investigation Analysis (cont.) 

24 • Error Logs (cont.) • Important to track account activity! • Authentication Failure States -> • Can be very important to detail attacker activity The Investigation Analysis (cont.) – SQL ERRORLOGs State Code Meaning 2 Invalid ID 5 Invalid ID 6 Invalid Windows login attempt 7 Incorrect password/disabled 8 Incorrect password 9 Invalid password 11 Server access failure with valid creds 12 Server access failure with valid creds 18 Password must be changed 

25 • Best way to analyze ERRORLOGs? • They’re plain text, so stick to keyword and timeline searches first • What will some of our keywords of interest be? • Failed • Login • ${IOCs} The Investigation Analysis (cont.) – SQL ERRORLOGs 

26 The Investigation Analysis (cont.) – SQL ERRORLOGs • Any new IOCs? 

27 The Investigation Analysis (cont.) – SQL ERRORLOGs 

28 • We know our attacker accessed the database • What’s inside? • Let’s ask the DB! • Client confirms that AdventureWorks2008R2 contains sensitive information The Investigation Analysis (cont.) Database ID DB Name Name Size (MB) 1master master 4 1master mastlog 1 2tempdb tempdev 8 2tempdb templog 0.5 3model modeldev 1.25 3model modellog 0.5 4msdb MSDBData 12.13 4msdb MSDBLog 0.5 5ReportServer ReportServer 4.25 5ReportServer ReportServer_log 6.13 6ReportServerTempDB ReportServerTempDB 2.25 6ReportServerTempDB ReportServerTempDB_log 0.75 7AdventureWorks2008R2 AdventureWorks2008R2_Data 195.94 7AdventureWorks2008R2 AdventureWorks2008R2_Log 0.49 

29 • What did the attacker access? • We turn to the plan cache • Refresh • Step 1: Find data cache related to our database/table • SELECT * FROM sys.dm_exec_query_stats • Step 2: Use the plan_handle to isolate a specific query • SELECT * FROM sys.dm_exec_sql_text(plan_handle) The Investigation Analysis (cont.) 

30 The Investigation Analysis (cont.) 

31 The Investigation Analysis (cont.) • Your team has completed analysis on 192.168.1.101 • This host was determined to be a Domain Controller • It was discovered that HeidiSQL was installed • The DB admins have no idea what this tool is • Any other problems with this finding? 

32 The Investigation Analysis (cont.) • HeidiSQL leaves us great registry artifacts! 

33 The Investigation Analysis (cont.) • Any other artifacts of consideration? 

34 How Do We Get Better? 

35 • SQL artifacts help us add context to an investigation • “Paint a better picture” • Volatility tells us that we should focus monitoring in key places • Access to DB servers • Logs that may roll/get overwritten • DB-specific logs that should be forwarded How Do We Get Better? 

36 • Database servers are often == crown jewels • If not crown jewels, then they contain sensitive data • Better to wrap monitoring/controls around the perimeter, or the crown jewels? • Both, if you can! • If not, pick and choose key points for each and monitor • How is my DB server accessed from internal and external systems? How Do We Get Better? (cont.) 

37 Conclusion 

38 Databases can be finicky for forensic analysis • However they may be extremely necessary! Consider the timeframes of your breach and data you’re concerned with • What will DB forensics yield? • Does it help me identify the actions of my attackers? DB forensics doesn’t mean boil the ocean • We can grab, and get a lot of information from external artifacts • Sometimes 5MB > 6TB Conclusion 

39 Thank You Matt Bromiley | Senior Consultant | Mandiant Jacob Christie | Consultant | Mandiant