FORENSIC INSIGHT; DIGITAL FORENSICS COMMUNITY IN KOREA SQL Server Forensic AhnLab A-FIRST Rea10ne unused6@gmail.com Choi Jinwon
Contents 1. SQL Server Forensic 2. SQL Server Artifacts 3. Database Files 4. Recovering Deleted Data From Data Files forensicinsight.org 2
SQL Server Forensic - What is SQL Server Forensic - SQL Server Forensic Methodology forensicinsight.org 3
What is the SQL Server Forensic Database 내에서발생한침해사고에대한입증혹은반증 Database 침해사고에대한영향분석 사용자 DML 혹은 DDL 사용행위추적 Database에서이전혹은이후에발생하는 Transaction 식별 삭제된데이터복원 forensicinsight.org 4
SQL Server Forensic Methodology Investigation Preparedness Incident Verification Artifacts Collection Artifacts Analysis Investigation Preparedness Incident Verification Artifacts Collection Artifacts Analysis forensicinsight.org 5
SQL Server Artifacts - Resident Artifacts - Non-Resident Artifacts - SQL Server Artifacts - SQL Server Artifacts Category forensicinsight.org 6
Resident Artifacts SQL Server를관리하는목적으로, 시스템설치시기본적으로설정되는 Artifacts 예 ) SQL Server Error Log, Data Files 등 System의 Version에따라차이가발생할수있음 휘발성데이터 (Volatility Data) 와비휘발성데이터 (Non-Volatility Data) 모두존재함 forensicinsight.org 7
Non-Resident Artifacts SQL Server를운용하기위한필수적인요소는아니지만, 연관성을갖고동작하는 Artifacts 예 ) System Event Log, Web Server Log 등 대부분의 Non-Resident Artifacts는비휘발성데이터로구성되어있음 forensicinsight.org 8
SQL Server Artifacts SQL Server Artifacts Volatility Resident Artifacts Non-Volatility Non-Resident Artifacts Data Cache Plan Cache Cache Clock Hands Active VLFs Server State Ring Buffer Database SQL Server Error Logs Data Files Authentication Setting Authorization Catalogs Database User Database Objects Server Versioning SQL Server Logs Jobs Triggers Native Encryption CLR Libraries Reusable VLFs System Event Logs External Security Controls Web Server Logs SQL Server Forensic Kevvie Fowler forensicinsight.org 9
SQL Server Artifacts Category 분류 행위재구성 Artifacts Data Cache, Plan Cache, Active VLFs, Reusable VLFs, Server State, Database Objects, AutoEXEC Procedures, Cache Hands, Jobs, Triggers, Trace Files, Server Error Logs 데이터복구 Active VLFs, Reusable VLFs, Statistics, Data Files 인증및권한 Authentication Settings, SQL Server Logins, Authorization Catalogs, Database User, Trace Files, Server Error Logs 서버설정및버전 Server Configuration, Collation Settings and Data Types, Native Encryption, Server Versioning 연관성자료 Database, Data, End Points, Schema SQL Server Forensic Kevvie Fowler forensicinsight.org 10
Database Files - Database Files - Data Storage - Data Rows & Data s - Data - Extents forensicinsight.org 11
Database Files 기본적으로모든 SQL Server는 Data File과 Log이구성되어있음 Data File : Database Object 들을저장하고있는파일 (*.mdf) Log File : DML(Database Manipulation Language) 과몇몇 DDL(Database Definition Language) 의동작을기록하는파일 (*.ldf) Database의로그는이벤트단위의로그가아닌 Transaction 단위의로그로기록됨 (Transaction Log) Data Files File1.mdf File1.mdf File1.mdf Database Log Files Log1.ldf Log2.ldf SQL Server Forensic Kevvie Fowler forensicinsight.org 12
Data Storage SQL Server는많은양의데이터를관리하기위해서여러단위의논리적인구조를사용함 Records Columns Rows s s Extents forensicinsight.org 13
Data Rows & Data s Data Rows 각 Column에속해있는 Record들의집합으로이루어진간단한구조체 Data Row는고정적인사이즈의데이터를포함하고있는 Fixed Rows와가변적인사이즈의데이터를포함하고있는 Variable Rows로나누어짐 Data s 동일한 Column 으로구성된 Row 들의집합으로이루어진간단한구조체 Record Data Row Column forensicinsight.org 14
Data Data s Data Row 의집합으로되어진 8192 Byte 크기의구조체 Header, Data Rows, Rows Offset Array 로구성되어있음 각 들은 ID(PID) 로구분되어짐 SQL Server Data Header Data Row 1 Data Row 2 Data Row 3 Free Space 3 2 1 http://msdn.microsoft.com forensicinsight.org 15
Extents Extent 8개의연속된 로구성된 64KB 크기의구조체 Extent를구성하는 과 Index에따라 Uniform Extent, Mixed Extent로구분됨 Uniform Extent : 동일한종류의 로구성된 Extent Mixed Extent : 서로다른종류의 로구성된 Extent Index Index 01:07 Index forensicinsight.org 16
Recovering Deleted Data From Data Files - Data Recovery - Identifying Data Rows Offset - Extract the Delete Data Row - Data Row Reconstruction forensicinsight.org 17
Data Recovery SQL Server Data Recovery Identifying Deleted Data Data Geometry Data Writing mechanism Overwriting (First Available or Next Available) Data Row Internal Structure forensicinsight.org 18
Identifying Deleted Data Rows 조사하고자하는 이포함되어있는 Data 를식별함 MS-SQL 의 DBCC(Database Console Command) 를통해식별하도록함 조사하려는 의 FID 와 PID 를확인함 DBCC TRACEON (3604) DBCC IND (DB_Name, _Name, -1 ) -- 해당테이블에할당된모든 ID 를출력 DBCC TRACEON (3604) DBCC IND (DB_Name, _Name, 0 ) - 해당테이블의내용이포함된 ID 를출력 확인된 ID 들을통해해당 의내용을확인함 DBCC TRACEON (3604) DBCC PAGE (DB_Name, File_ID, Data_, 1) -- 각 Row 의메타데이터를포함하여출력 DBCC TRACEON (3604) DBCC PAGE (DB_Name, File_ID, Data_, 2) - File 의내용을 Raw Data 로출력 forensicinsight.org 19
Extract the Delete Data Row Data Rows Offset Array 에서삭제된데이터의 ID 와 Offset 정보를확인할수있음 삭제된데이터는 Data Rows Offset Array 필드의값이 0x00 으로변경됨 Deleted Rows!! Deleted Rows!! Offset Array 에서 0x00 으로변경된 Rows 의 Offset 정보를확인함 Offset Array 에는삭제된 Row 의 Offset 정보는초기화됨 삭제된 Row 앞에존재하는 Row 의 Offset 과 Size 정보를통해삭제된 Row 의 Offset 정보를확인함 forensicinsight.org 20
Offset Array에서 0x00으로변경된 Rows의 Offset 정보를확인함 Offset Array에는삭제된 Row의 Offset 정보는초기화됨 삭제된 Row 앞에존재하는 Row의 Offset과 Size 정보를통해삭제된 Row의 Offset 정보를확인함 forensicinsight.org 21
Data Row Reconstruction Data Row Structure Status Bits A Status Bits B Position of Number of Columns Fixed-length Column Data Number of Columns Null Bitmap Column Offset Array Variable Length Column Data 1 Byte 1 Byte 2 Byte Variable 2 Bytes Variable 2 Bytes Variable Component Status Bits A Status Bits B Position of Number of Columns Fixed-length Column Data Number of Columns Null Bitmap Description Data row 속성을표현하는 status bit A SQL 2000, 2005, 2008에서는사용하지않음 Number of Columns의 Offset 고정길이데이터필드해당 Data Row에있는 Column 들의전체개수 Column Data의사용현황 Column Offset Array 각가변길이데이터의 Offset 정보 ( 끝지점을나타냄 ) Variable Length Column Data 가변길이데이터필드 forensicinsight.org 22
Question and Answer forensicinsight.org 23