Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Database Forensics without the Database

bromiley
April 16, 2016

Database Forensics without the Database

Databases. You have them; attackers want them. It's where all the good stuff sits. Let's make sure we're defending them the best we can!

In this talk, we're going to introduce and/or improve database forensics in your incident response workflow. Only; we're not going to touch the database. Through analysis of various artifacts, we're going to show you how to build a timeline of attacker activity and discover what may have happened to your data while it was exposed. We're also going to release new research that can be used immediately to include database forensics in your next case.

bromiley

April 16, 2016
Tweet

More Decks by bromiley

Other Decks in Technology

Transcript

  1. 1 © Mandiant, a FireEye Company. All rights reserved. ©

    Mandiant, a FireEye Company. All rights reserved. Database Forensics without the Database Matt Bromiley Jacob Christie
  2. 4 © Mandiant, a FireEye Company. All rights reserved. Agenda

     Who We Are  Why Do We Care So Much About SQL?  Making the Challenge Harder  Those “Other” Artifacts  Conclusion
  3. 5 © Mandiant, a FireEye Company. All rights reserved. ©

    Mandiant, a FireEye Company. All rights reserved. WHO WE ARE
  4. 6 © Mandiant, a FireEye Company. All rights reserved. $

    whoarewe Matt Bromiley works at Mandiant, having lived a previous life working with crazy chefs and 2 of the Big 4 [@]505forensics[.com] 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. 7 © Mandiant, a FireEye Company. All rights reserved. ©

    Mandiant, a FireEye Company. All rights reserved. WHY DO WE CARE SO MUCH ABOUT SQL?
  6. 8 © Mandiant, a FireEye Company. All rights reserved. Why

    Do We Care So Much About SQL Artifacts?
  7. 9 © Mandiant, a FireEye Company. All rights reserved. Why

    Do We Care So Much About SQL Artifacts? (cont.) Le simplified org.. Yes!
  8. 10 © Mandiant, a FireEye Company. All rights reserved. Why

    Do We Care So Much About SQL Artifacts? (cont.) Le simplified org.. Freak Out! Freak Out! Who is this ? Can we find them?
  9. 11 © Mandiant, a FireEye Company. All rights reserved. Why

    Do We Care So Much About SQL Artifacts? (cont.) Le simplified org.. What I NEED to care about
  10. 12 © Mandiant, a FireEye Company. All rights reserved. Why

    Do We Care So Much About SQL Artifacts? (cont.)
  11. 13 © Mandiant, a FireEye Company. All rights reserved. ©

    Mandiant, a FireEye Company. All rights reserved. MAKING THE CHALLENGE HARDER
  12. 14 © Mandiant, a FireEye Company. All rights reserved. Making

    the Challenge Harder  In previous talks, we have covered research into using a SQL database to pull back artifacts - Direct DB artifacts can be valuable - Can be damaging to volatile artifacts - Requires an analyst to know SQL, or a DB admin to (let you on the box OR run code for you) - None of the above are common  Yes, we’re all asked to wear many hats. How many are also asked to be a DBA?  Databases were not designed as forensic artifacts - They’re designed to store, sort, index, optimize, and deliver data quickly - Beat the crap out of memory
  13. 15 © Mandiant, a FireEye Company. All rights reserved. Making

    the Challenge Harder (cont.)  So we thought….take away the database  Put an analyst in a situation they are comfortable being in  Let the analyst use tools they are already familiar with  Why? - Let’s your team add to the workflow, instead of building a new workflow - No one has to become a SQL guru to interpret data - Value is provided to the client faster, with a better delivery of results
  14. 16 © Mandiant, a FireEye Company. All rights reserved. Making

    the Challenge Harder (cont.)  What does that leave us with? - External logs - AKA Logs that can be opened without having to use a SQL Server client - Possibly copying off SQL data/log files and loading into a separate instance • Great; gives us a view of the data • Does it give us a view of the attacker? - Saving the best for last…memory!!
  15. 17 © Mandiant, a FireEye Company. All rights reserved. Making

    the Challenge Harder (cont.)  Why memory? - Microsoft wants your memory - SQL Server 2014+ utilizes In-Memory Optimization, which allows developers and DBAs to optimize the DB based on business requirements - Also means SQL Server is pushing much more “stuff” to memory - Just would be nice if more companies upgraded their SQL instances…
  16. 18 © Mandiant, a FireEye Company. All rights reserved. Making

    the Challenge Harder (cont.)  Why memory? (more important) - Forensic analysts are comfortable with memory acquisitions • If they’re not…you need to talk to your team - The basics: • Analyzing memory allows an analyst to quickly pivot, whereas a siloed artifact approach doesn’t work
  17. 19 © Mandiant, a FireEye Company. All rights reserved. ©

    Mandiant, a FireEye Company. All rights reserved. THOSE “OTHER” ARTIFACTS Logs
  18. 20 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts - Logs  Logs! • SQL-Generated • Trace Logs • Error Logs • External • Windows Event Logs • Web Logs • Firewall Logs • Network Traffic (Netflow, PCAP, etc.)
  19. 21 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – SQL-Generated Logs  Trace Logs - Internal because they come from within SQL; but you may find a client that exports these logs and stores elsewhere; - Store information about logins, DML/DDL operations - SQL Server 2005+ runs default trace • Stored automatically - Low volatility; files are persistent - New trace file is created each time MS SQL is started - Can be removed from a system and preserved forensically - Examined later on a separate, analysis system • Use ‘SQL Server Profiler’ to load a trace file • Can be loaded using a SQL Server client elsewhere
  20. 22 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – SQL-Generated Logs  Error Logs - Same location as trace files - 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
  21. 23 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – SQL-Generated Logs  Error Logs (cont.) - Important to track account activity! - Authentication Failure States -> - Can be very useful to detail attacker activity 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
  22. 24 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – External Logs  Windows Event Logs - System, Security, Application - Vista+ may give customized Application-specific logs  Web Logs - How does an external user access your DB? Log this route!  Firewall Logs - Know your ports, know your Ips - Suspected data theft? Know how to aggregate byte counts  Network Traffic (Netflow, PCAP, etc.) - Know your ports, know your IPs
  23. 25 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Ransom Case Study 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?
  24. 26 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Ransom Case Study 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
  25. 27 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Ransom Case Study 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  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
  26. 28 © Mandiant, a FireEye Company. All rights reserved. ©

    Mandiant, a FireEye Company. All rights reserved. THOSE “OTHER” ARTIFACTS Memory
  27. 29 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) - Memory  Memory is an essential artifact in database forensics  Anyone want to try and image a DB server?  128GB > 6TB
  28. 30 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) - Memory  The SQL Data Page
  29. 31 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) - Memory  The SQL Data Page (cont.) - THE storage format for many things…not just SQL! - 8092 bytes in length • Header (96 bytes) • Data + Pointers (7996 bytes) • Pointer structure is stored in reverse (first record is stored last) • Any hints as to what a “footer” might be?
  30. 32 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) - Memory  SQL Data Record Header \x01\x01\x00\x00\x20\x02 m_headerVersion m_type m_typeFlagBits m_level m_flagBits
  31. 33 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) - Memory  SQL Data Record Footer - Any thoughts? - \x60\x00 - start of first record
  32. 34 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) - Memory  Let’s parse it!  Introducing our first Volatility parser – sqldatafinder.py - Modeled after earlier plugins that allow for header/footer searching - Parses a memory image, finds sqlserver process - Finds this, so you don’t have to! - Extracts data pages to an output directory  Next Steps: - Output data pages in order of DB; reconstruct tables if possible
  33. 35 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Memory Parsing  What is the value?  The closer we are to the breach; the more relevant the data in memory (duh)  Does my database contain… - PHI - PII - PCI - Something else offensive  Did the attacker.. - Create a new table - Make a change that has not committed yet - Run malicious commands
  34. 36 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Memory Parsing  What else? - Anyone ever interact with a SQL Server? - Your attacker has too! - What tools are typically used to interact with SQL?
  35. 37 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Memory Parsing  SQL Server Management Studio contains valuable artifacts as well! - If the process was still running or if the remnants are still available  By dumping memory and parsing, we can identify queries issued by management studio  We can also capture the text of the user’s query box Use AdventureWorks2008R2; Select * from HumanResources.Employee.PayHistory
  36. 38 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Memory Parsing  What’s next?  Second Volatility parser (TBD); sqlmsparser.py - If found, parse the contents of SQL Management Studio to identify text box queries and/or queries issued - Output commands to analyst  Still working on this structure; highly dependent on the user as SQL code is not space/case/whitespace sensitive - Yes, a query can have 100 lines in between two statements
  37. 39 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Memory Parsing  Lastly…  The plan cache  When a statement is received, the query processor identifies the most efficient way to execute; the “execution plan” - Hence, the “plan cache” - Stores previously executed SQL “plans”  Can be used to find queries from stored procedures and functions - Not all commands touch table records, or force a data page - Great for finding user account activity/creation commands - Some details are stored in plain text!!
  38. 40 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Memory Parsing  The plan cache (cont.)  HIGHLY VOLATILE - Subject to SQL flushing policies - Stored procedure updates - Stopping the SQL Server service (clears associated memory) - May be cleared by an attacker!!!
  39. 41 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Memory Parsing  The plan cache (cont.)
  40. 42 © Mandiant, a FireEye Company. All rights reserved. Those

    “Other” Artifacts (cont.) – Memory Parsing  The plan cache (cont.)  Inside of SQL, the plan cache can be accessed/converted SELECT * FROM sys.dm_exec_sql_text(plan_handle)  Third Volatility parser (TBD); sqlplancache.py - Identifies the plan cache in memory, extracts and converts to nice, pretty queries - Still defining structure, however all components are in memory
  41. 43 © Mandiant, a FireEye Company. All rights reserved. ©

    Mandiant, a FireEye Company. All rights reserved. CONCLUSION
  42. 44 © Mandiant, a FireEye Company. All rights reserved. Conclusion

     Determine if the team needs to change based on an artifact, or if the artifact can changed based on your process - Do we need to go to the DB? - Caveat: Never change an artifact  Non-database SQL artifacts can provide a lot of value for your team; decide if it’s relevant to your investigations  Learn what the artifacts how, and how to parse, interact with, and interpret them. - Do error logs only contain errors?  Writing Volatility parsers is freakin’ fun; do it!
  43. 45 © Mandiant, a FireEye Company. All rights reserved. ©

    Mandiant, a FireEye Company. All rights reserved. THANK YOU!