PDF Forensic Analysis of a SQL -SQL Server Forensics 20 - Securitybyte - forensic-analysis-sql-server-2005-database-server_1906
Wait Loading...


PDF :1 PDF :2 PDF :3 PDF :4 PDF :5 PDF :6


Like and share and download

forensic-analysis-sql-server-2005-database-server_1906

SQL Server Forensics 20 - Securitybyte

SQL Server Forensics | Why are Databases Critical Assets? ▫ Why are The application of computer investigation and analysis techniques to gather database Inside SQL Server 2005 The Storage Engine, Microsoft Press, 2006 VLF #2 Apr 13, 2014 Forensic analysis of

Related PDF

SQL Server Forensics 2 - ORK Home Page

SQL Server Forensics | Why are Databases Critical Assets? ▫ Why are The application of computer investigation and analysis techniques to gather database Inside SQL Server 2005 The Storage Engine, Microsoft Press, 2006 VLF #2
PDF

Database Forensic Analysis Using Log Files - International Journal

Apr 13, 2014 Forensic analysis of database can help to determine Forensic analysis analyzed the evidence SQL SERVER 2005 DATABASE SERVER”
PDF

SQL Server - Black Hat

professionals can use to perform forensics analysis after a database attack We focus specifically on Microsoft SQL Server 2005, however the information 
PDF

Database Forensic Analysis with DBCarver - Data Systems and

Jan 8, 2017 rent tools can provide for forensic analysis in a database Be SQL Server, Oracle, PostgreSQL, MySQL, SQLite, Apache Derby, Firebird 
PDF

a framework for database forensic analysis - AIRCC Publishing

Database activity can be audited through a SQL trace in MS SQL server which is to database systems can be revealed through database forensic analysis C Newman, CTO Founder (2005), “Security Auditing In Microsoft SQL Server”,
PDF

SQL Server Forensics 20 - Securitybyte

Dec 3, 2009 Database forensics – The application of computer investigation and analysis techniques to gather database evidence suitable for presentation 
PDF

Forensic Ballistic Notes

A Simplified Guide To Firearms Examination

webzoom freewebs balisticaterminal Forensic cases of considerable ballistic injury to the lungs, liver, kidneys, spleen, large arteries or central veins, the latent period until incapacitation will be in the range of one or several minutes (delayed incapacitation) This potential for physical activity is not always exhausted due to

Forensic Ballistic Reviewer

BACHELOR OF SCIENCE IN CRIMINOLOGY

webzoom freewebs balisticaterminal Forensic cases of considerable ballistic injury to the lungs, liver, kidneys, spleen, large arteries or central veins, the latent period until incapacitation will be in the range of one or several minutes (delayed incapacitation) This potential for physical activity is not always exhausted due to

Forensic Psychology

Forensic Psychology and the Victims of Crime - Corwin | Home

PDF Introduction to Forensic Psychology SAGE Publications sagepub upm data 64577 Chapter 1 pdf PDF Forensic Psychology SAGE Publications sagepub upm data 39927 1 pdf PDF What Is Forensic Psychology,

forensic_accounting_tracing_stolen_assets

Asset Recovery Handbook - Stolen Asset Recovery Initiative - World

PDF tracing stolen assets Basel Institute on Governance baselgovernance Tracing 20Stolen 20Assets pdf PDF The Recovery of Stolen Assets A Fundamental Principle of the UN repatriationgroup wp content uploads U4 Brief

  1. tracing money
  2. tracing money laundering

Forensic_Medicine___Toxicology_-_Last_Moment_Revision

Forensic Science Multiple Choice Questions And Answers [EPUB

PDF FORENSIC TOXICOLOGY medecine ups tlse 2012 DCEM1 ForensicToxicology pdf PDF lecture notes on human respiratory system physiology liverpool ac uk ~gdwill hons gul lect pdf 24 Oct 2018 October 4th, 2018 Download

  1. forensic medicine and toxicology lecture notes pdf
  2. forensic toxicology examples
  3. question and answer in forensic medicine
  4. forensic toxicology testing methods
  5. forensic toxicology
  6. forensic toxicology cases
  7. review of forensic medicine and toxicology pdf
  8. postmortem forensic toxicology

Forensics Test Bank

Forensic And Investigative Accounting 5th Edition Test Bank

PDF CS6004 CYBER FORENSICS Question Bank Unit I Network layer syedengg ac in pdf IT VIII Sem CS6004 QB pdf PDF Download Forensics Chapter 1 Test PDFcommunity spring is forensics chapter 1 test pdf PDF Guide To Computer Forensics Test

forensik kasus TENGGELAM

1 BAB I PENDAHULUAN 11 Latar Belakang Tenggelam

PDF Pemeriksaan Diatom pada Korban Diduga Tenggelam (Review journal unair ac id download fullpapers 5 20DIATOM 20 fiish pdf PDF tanda intravital yang ditemukan pada kasus tenggelam di repository usu ac id bitstream 123456789 21606 7 Cover pdf PDF Pemeriksaan Luar pada Jenazah

  1. tes getah paru
  2. pemeriksaan diatom adalah

forensik5

Zeitschrift Interne Revision - Erich Schmidt Verlag

PDF Aushang Themenabend Forensik5 pptx Innocence in danger innocenceindanger de Podiumsdiskussion 3 6 2014 pdf PDF 2015 Lageplan außen, 22 10 2015 indd Bezirkskrankenhaus Lohr bezirkskrankenhaus lohr de 17156 lageplan 22 10 2015 gesamt

Forenzicka Balistika

tema broja: tema broja - Akreditaciono telo Srbije

Forenzička balistika 3 Forenzička hemija i toksikologija 4 Forenzička psihologija sa psihijatrijom 5 Sudska toksikologija 6 Forenzička analiza požara   1 II SEMESTAR 5 3 4 1 1 Forenzička hemija i toksikologija O 8 3

  1. Lista institucija i laboratorija koje vrše forenzička
  2. Forenzička balistika
  3. BALISTIKA STRIJELNE RANE I MEHANIZAM OZLJEĐIVANJA
  4. Balistika je znanstvena disciplina koja proučava gibanje projektila
  5. Forenzička toksikologija
  6. Traseologija i balistika
  7. Centra za forenzička
  8. podoblast balistika i
  9. Centar za forenzička ispitivanja
  10. laboratorija četiri neakreditovana forenzička
Home back Next

Description

Interested in learning more about security

SANS Institute InfoSec Reading Room This paper is from the SANS Institute Reading Room site

Reposting is not permitted without express written permission

Forensic Analysis of a SQL Server 2005 Database Server

Copyright SANS Institute Author Retains Full Rights

GIAC Gold Template

Forensic Analysis of a SQL Server 2005 Database Server

GCFA Gold Certification

Author: Kevvie Fowler,

Adviser: Joey Niem

Accepted: April 1,

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Outline Investigation Introduction

Step 2: System Description

Step 6: Data Recovery

Step 7: String Search

Investigation Summary

Appendix A

Appendix B

References

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Investigation Introduction On March 1st,

I received a call from a client who stated that they may have been a

victim of a security incident sometime over the past 24 hours

They believed unauthorized modifications were made to their production database server which had resulted in erroneous product shipments and financial loss to the company

Due to the mission critical nature of the system,

it could not be taken off-line unless significant evidence of system misuse could be

Step 1: Verification

Upon arriving on scene,

I was briefed on the situation and learned that the SQL Server 2005 database server contained a single user database which was the foundation of an online-sales

The client also FA27 informed me998D that they had received a call from a credit Key fingerprint = AF19 2F94 FDB5 DE3D F8B5 06E4 A169 4E46card company

regarding a suspicious transaction that was charged to a client card by their company

Because the server could not be taken off-line,

During a

live analysis volatile and non volatile data is viewed and acquired with the assistance of the live

During a forensic investigation you should utilize binaries on the target

system as little as possible as they may be corrupt or tampered with thus skewing their output

The incident response CD-Rom used in this investigation contains traditional incident response tools in addition to SQL utilities and libraries which allow ad-hoc query submission to SQL Servers using minimal assistance from the un-trusted host

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template To begin the incident verification,

Windows Forensic Tool v1

This configuration file will execute Distributed Management Views (DMV),

Database Consistency Checker (DBCC) commands and other vendor issued procedures to gather data which can be used to prove or disprove the occurrence of an intrusion

For more information on the customized Windows Forensic Tool Chest configuration file,

refer to Appendix A of this document

At precisely 10:02 AM,

the client’s system logged into the PRODSQL05

SQL Server interactively under the user context Administrator

Upon logging into the system,

was observed that the system tray contained no third party application icons and the operating

system appeared to be Windows 2003 Standard Edition

At 10:03 AM,

I assumed

command of the console to begin the investigation

My Forensic Response CD was inserted

into the computer and a trusted command shell was launched by issuing the “D:\FResponse\cmd

Using the full file path in addition to the binary name

ensures that the binary is loaded from the trusted CD

The un-trusted host may contain binaries Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46 with matching names to the binaries contained on my response CD

If these binaries are present

within a directory referenced in the path variable of the target host,

the un-trusted binaries can be

To eliminate the possibility of this occurring,

the full file location in addition to

the binary name will be used during this investigation

The outputs from the tools run during this investigation,

forensic workstation as opposed to the un-trusted target host

From the command shell,

the “D:\FResponse\net use * \\192

The “$Acquisition” share is hidden and password protected to help ensure the integrity A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template of the data within

It was noted that the drive letter associated with the net use command was connected as “E:\” on the target host

The “D:\FResponse\wft

exe –dst E:\” command was issued to launch the customized Windows Forensic Toolchest v1

Once Windows Forensic Toolchest was finished executing,

the results were analyzed and the following notable events were identified

SQL Server reserves Sessions #50 and lower for internal SQL Server processes,

it was identified that two sessions were currently active on the SQL Server

The first Session ID

# 52 which belonged to the instance of WFT executing under the local Administrator context and

the second was Session #51 belonging to an unknown user operating under the login

EASYACCESS

This session had been established at 7:58 AM that morning

Because the login name was unconventional,

it was flagged for client verification

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

SQL Server 2005 maintains a record of the last SQL statement executed by a given session

Viewing this history for the connected users led to the identification of a suspicious transaction

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

The audit policy active on the target system was configured to log successful logins only,

However,

SQL Server maintains its own log that records database related

service errors in addition to authentication data

The error log was stored within the “c:\Program

Files\Microsoft SQL Server\MSSQL

Review of

the error log identified several hundred failed login attempts in succession against the sa account,

followed by its successful login

This activity is normally attributed to evidence of a successful

brute force attack against the database server

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

To further investigate the above findings,

the configuration of the SQL Server needed to be obtained

SQLCMD,

a Microsoft issued utility which allows the submission of ad-hoc SQL

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template statements and scripts to a MS SQL Server will be used from the trusted incident response CD

The ad-hoc query capabilities of this tool will be used during the remainder of this investigation

The “D:\FResponse\Sqlcmd –S PRODSQL05 –e –s”,”” command was executed from the trusted command prompt which opened a connection to the SQL Server using the interactive user

The “-e” switch forces SQLCMD to echo our input statements into the SQL result files and the “-s”,”” switch ensures the outputs are comma delimited which will allow the results to be imported into another application for deeper analysis

associated results securely to my forensics workstation

After logging in,

an output file was established to log the SQL statements and their

A MD5 hash will be created on each output file to ensure data integrity

When a connection is

made to SQL Server the default database context configured under the user Login Properties will Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46 be used

To ensure the database context was indeed set for the OnlineSales database the following

Results: initialconnection

SQL Server 2005 can be configured to use either Windows Authentication,

which allows the host operating system to authenticate users,

which allows authentication to occur at either the Operating System or independently within SQL Server4

There are also various logging options within SQL Server to log successful and/or failed login attempts

To verify the active configuration settings of the subject server the following command was run: A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

xp_loginconfig Results: xp_loginconfig--onlinesales

The following results were produced and show that the server is set for Mixed Mode

authentication and is configured to log both successful and failed login attempts

Authorization within SQL Server 2005 is controlled by two gates

The first gate ensures that users are authenticated at the database instance and the second ensures that users have the

appropriate permissions to access the various and F8B5 database objects

During the Key fingerprint = AF19 FA27 2F94 998D databases FDB5 DE3D 06E4 A169 4E46

verification step of this investigation we identified that the SQL server login EASYACCESS was

However because the investigation is on the OnlineSales database the

database permissions will need to be checked to ensure that the EASYACCESS account has access to this specific database

The following query was run to gather a list of all database users

within the OnlineSales database:

Select * from sys

database_principals where type = 'S' or type = 'U' order by create_date,

modify_date Results: db_principals-onlinesales

This query produced the following results which show that the EASYACCESS user account does have access to the OnlineSales database:

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

The Microsoft extended procedure “xp_cmdshell” allows users to execute dos commands within the underlying host operating system using their SQL client

This can allow an attacker who compromises the SQL Server to then launch attacks against the underlying host operating

However,

this procedure is disabled by default in SQL Server 2005

To verify its

the following command was executed:

Results: sys

Select * from sys

The results showed that this procedure was disabled therefore the assumption is made that

database users are unable to execute operating system level commands on the host

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

At approximately 10:45 AM,

the initial findings were presented to the client who verified

that the EASYACCESS account was an anomaly and not created for any legitimate business purpose

It was also disclosed that the Online Sales application was down for maintenance

therefore no one should have been logged on to the OnlineSalesdatabase or have executed the identified delete statement

At 11:01 AM the client authorized a full forensic investigation to be performed on the server to determine the scope and impact of the intrusion

At 11:05 AM The SQL Server was

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template disconnected from the production network and plugged into a 4 port DLINK hub to isolate the

server and prevent further modification by the unknown user

Step 2: System Description

As previously stated in the verification section of this document,

server the default Microsoft background was visible on the server console and there were no third

party applications visible within the system tray

The following system profile was gathered

from information provided by the client as well as investigator findings gathered during the

Serial Number

US822301223

System Operating System

Microsoft Windows Server 2003 Service Pack 1

Database Version

System Function

PRODSQL05

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

System Name

Function as a backend database to an online order processing system

The system contained 3 peripheral network cards,

one appeared to be a video card,

and the remaining two appeared to be network cards,

only a single network card was actually connected to the network

Physical Description

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Asset Photographs:

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

Step 3: Evidence Collection

As time elapses after a security incident,

evidence can be overwritten by legitimate and/or malicious system activity

Databases can contain large data stores which result in a high data acquisition cost

To help ensure priority is given to the data sources most likely to contain relevant data to support the investigation,

it’s my expert opinion that relevant data sources be

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template assigned a significance and also a volatility value between 1

-5 with,

The following values should be used in the following formula to determine priority [10

Using the above formula,

data stores relevant in this investigation were prioritized as follows:

Importance

Volatility

Priority

SQL Server Connections & Sessions

Transaction Log(s)

SQL Server Logs

SQL Server Database Files

System Event Logs

Now that data stores have been identified and prioritized,

the actual data acquisition can

SQL Server connection & session data

information successfully via theF8B5 customized Windows KeyRelated fingerprint = AF19 was FA27 2F94 998Dcaptured FDB5 DE3D 06E4 A169 4E46 Forensic

Tool chest tool executed during the verification stage of this investigation

Transaction Logs

The SQL Server transaction log contains a record of all insert,

statements made within the database

For performance reasons SQL Server does not immediately

write these events to the physical data files

Instead changes are written to the log file to buffer and later written to the data files

A single SQL Server database can utilize multiple database files and multiple transaction logs

The number of files and locations will need to be identified for the OnlineSales database

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template Using the trusted SQLCMD session,

the following SQL query is executed to gather the database file information:

Results: sp_helpdb-onlinesales

The below results were returned from the above SQL query and show that the OnlineSales database is currently using one physical data file ending with the “

and two transaction log files ending with the “

These files are contained within

mdf … C:\Program Files\Microsoft SQL Server\MSSQL

ldf … C:\OtherLogs\OnlineSales_log2

separate Windows file locations

The following SQL query was then executed to dump the contents of the OnlineSales log Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46 file to the trusted forensic workstation:

Results: dbcclog-onlinesales

Although a SQL Server database can use multiple physical transaction logs internally,

SQL Server splits each physical log file into 4-16 Virtual Log Files (VLFs)5

Selected VLFs are

marked active at any given time and used to record transactions

SQL Server periodically completes a checkpoint process which flushes changes recorded in the log file to the physical disk file

Once this is complete,

SQL Server marks the VLFs containing the fully committed transactions reusable and will overwrite them as required with new log records

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template The following SQL Server command was run from within the OnlineSales context to view the logical allocation status of the physical transaction log:

Results: dbccloginfo-onlinesales

The results of this command may be helpful later in the investigation when it will be determined if the physical transaction log file will be split into virtual log files to separate the active VLFs from the reusable VLFs which may contain historical data relevant in this

In order to obtain a true bit-to-bit copy of the transaction log,

service will need to be shutdown in order to release the locks held on the target files

Server shutdown and startup the database checkpoint process is automatically triggered5 which,

as previously stated before will flush the non committed changes to disk and mark the records as

The following command was executed to force the shutdown of SQL Server

Results: shutdown

Shutdown Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

After the SQL SERVER processes were shutdown,

the physical log files were acquired

using the dcfldd disk imaging tool which also generated MD5 hashes for the acquired data

hashes were compared to the hashes of the on disk files to ensure the data was not altered during

Database files

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template Using the database file locations retrieved from the results of the “sp_helpdb OnlineSales” command executed earlier in the investigation,

the OnlineSales database file was also

acquired using the dcfldd tool

Default SQL Server Trace File

The default configuration of SQL server runs a trace which captures limited activity within the database

This configuration is enabled by default,

but can be disabled by a user with

Using the SQL Server configuration gathered earlier in this investigation,

default trace was confirmed to be enabled

review of the FA27 SQL Server installation several trace files4E46 using the default KeyDuring fingerprint = AF19 2F94 998D FDB5directory DE3D F8B5 06E4 A169 Microsoft trace naming convention “log_##” were identified

These log files were acquired using

the dcfldd tool as they may contain information relevant in this investigation

SQL Server Error Logs

In addition to the current error log used by SQL Server,

Each time the SQL Server service is restarted,

a new error log is created and the existing log is backed up

SQL Server maintains the current error log in addition to 6 log backups

All 7 error logs were acquired using the dcfldd tool

Once all data had been acquired the SQL Server services were restarted

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Step 4: Timeline Creation

Constructing an initial timeline will map out the notable digital events which have been identified

thus far and establish an investigation scope which will be used during the Media Analysis phase

Review of the SQL Server error logs obtained during the Evidence Collection step show that the

SQL Server instance was restarted on March 01,

This will be the first entry in the timeline

As discovered during the verification step of

this investigation on March 2nd,

recorded within the error log between 7:01 AM to 7:39 AM from IP address 192

Following these failed login attempts were successful logins by the SA account at 7:54 AM and the EASYACCESS account at 8:09 AM from the same IP address

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

These events will be added to the timeline in addition to the associated Server Process Identifier

A SPID is a unique number used by SQL Server to track a given session within the

The trace files obtained during the evidence collection phase of this investigation were imported into MS SQL Profiler on my forensic workstation for analysis

During review,

the following notable events were identified: (1)

Creation of EASYACCESS account

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template (4-6) Unknown transactions are executed by EASYACCESS account which required tempdb usage

Often DML operations require tempdb usage2 therefore it is likely that SPID 51 issued DML operations which required object or interim result

Keyonfingerprint AF19 FA27 FDB5 DE3Dthe F8B5 06E4 A169 4E46 Based the events=identified thus2F94 far in998D the investigation,

March 1,

SQL Server instance is restarted

SQL Server Brute Force attack launched against PRODSQL05 server SA SQL Server user account logs into PRODSQL05 server EASYACCESS account created EASYACCESS account granted access to OnlineSales database EASYACCESS account added to OnlineSales db_owner role EASYACCESS SQL Server account logs into

UNKNOWN

March 2,

UNKNOWN

7:26 AM

7:56 AM

8:09 AM

EASYACCESS

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template EASYACCESS

8:13 AM

EASYACCESS

8:13 AM

EASYACCESS

Administrator Administrator Administrator

52 N/A 52

PRODSQL05 server EASYACCESS account executes unknown transaction within ONLINESALES db EASYACCESS account executes unknown transaction within OnlineSales database EASYACCESS account executes unknown transaction within OnlineSales database Start of Forensic Investigation of database server PRODSQL05 server removed from network SQL Server instance shutdown

8:09 AM

The application connected to SPID 51 was recorded by SQL Server as “OSQL-32”

Performing a Google™ search on this name identified the application as a legacy Microsoft

command line query tool called OSQL

This will be noted as it may be relevant in the future if an

investigation is performed on the unauthorized user’s computer

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

Step 5: Media Analysis

The timeline established in the previous step will now be used to set boundaries on the scope of media analysis

Using the timeline,

the focus of the investigation will be on activities executed by SPID 51 between 7:54 AM March 1st,

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Before looking at any of the raw SQL Data files,

the data types in use within the OnlineSalesdatabase will need to be identified

Unicode is a standard method of mapping SQL Server byte representations (code points) to ASCII characters

The Unicode standard is inclusive

of characters which map to all languages throughout the world

SQL Server uses various data types which store Unicode data,

however there are some data types used by SQL Server (char(n),

varchar(n) & text) which store non-Unicode values3

When non-Unicode values are stored within SQL Server,

they are converted to a supported data type using the collation setting

of the respective table column3

If this data is viewed by a computer using a code page which

does not cover the range of characters used within the collation setting of the database,

can occur which can skew the results3

To determine if non-Unicode data was being used by the

Order table and the collation setting in place,

the following procedure was run:

Results: sp_tablecollations-onlinesales

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

The results below show that both Unicode and non-Unicode data is stored within the Order

The columns storing non-Unicode data are using the SQL_Latin1_General_CP1_CI_AS

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

This collation setting was researched on SQL Server 2005 Books Online which showed

that this collation maps to code page 12524

To verify the code page in use on my forensic

the regional and language options application within control panel on my forensic

This identified that the forensic workstation was using a compliant code page in order to correctly translate the code points used by SQL Server

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

The transaction log acquired during the evidence collection phase was imported into Microsoft Excel using code page 1252

A SQL Server 2005 transaction log contains over 100

columns however only a few columns will contain relevant data based on the scope of this investigation

The following table outlines target columns and their function within this investigation

Description

Operation

The type of operation which was performed

The data page affected by the transaction

The row within the data page affected by the transaction

Offset in Row

The first position within the data row affected by the transaction

The Server Process Identifier

Begin Time

Indicates the transaction start time (server time)

Transaction Name

Classification of the active transaction

End Time

Indicates the transaction end time (server time)

RowLogContents0

The value which was updated by the transaction (Insert,

Update statements) The value which was written to disk (Insert,

Update statements) = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

RowLogContents1 Key fingerprint

For a listing of all columns within the transaction log,

please see Appendix B of this document

The imported data set was filtered to display only records which were executed by SPID 51 and

between the date/time ranges captured in the timeline

The first two transactions identified,

associated with the creation and permission augmentation of the EASYACCESS account which

was identified during the trace file review

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

The third transaction executed by SPID 51 was an update statement

The transaction log details

show that a database transaction ID 0000:0000032e which was an update statement affecting 3

records within 3 separate data pages within the database

Marks the beginning of a transaction

Unique transaction identifier

Data Page identifier for row containing the updated record

Type of transaction performed

On data page row location of record

In row data offset of modification

Marks the end of a transaction

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

A SQL Server data page is an 8192 byte structure which stores database data5

A data page can contain multiple rows and each database contains multiple data pages

Data pages are organized into logical groups of 8 called extents6

Using the transaction log dump,

the first update statement was analyzed,

identifying a record on row 20 of Data Page 0001:000000d3

Both the A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template Page ID and Transaction ID values are stored in hex and when converted to decimal produce the following values:

Hex 0000:0000032e 0001:000000d3

Decimal 0:814 1:211

Identifier Transaction ID Data Page

In order to view the raw data pages,

the OnlineSales database was attached within SQL Server Management Studio (SSMS) version 9

to examine the raw data pages which have been modified

the newly added OnlineSales database,

Microsoft-issued commands and procedures will be used

The following command was issued from within the OnlineSales database context

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46 The above command dumped data page 211 which contained the row which had been modified

The header of the table was examined to identify the base table to which the data page belonged

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Objectid 629577281 was used as an argument in the following query which was run to resolve

Select from sysobjects where id = 629577281 Key*fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

This produced the following output which confirmed that the data page belonged to the Order

The method used by SQL Server to store data depends on the data types in use,

the size of each column and the order in which the columns were specified when the table was created

Before the raw data pages were examined,

the table schema was first gathered by executing the following command: SELECT sc

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template systypes st WHERE sc

id = 629577281 ORDER BY colorder

The following output was produced which illustrates the schema of the Order table:

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46 Using slotID: 20 and rowoffset 80 which were obtained previously from the transaction log,

specific point within the data row was identified in which the transaction began

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Using the table schema obtained earlier,

the data type within this row offset is the Price column which contains a 30-byte nchar data type

From the transaction log,

the Key Rowlog0 and Rowlog1 were998D extracted and converted decimal representation

fingerprint = AF19columns FA27 2F94 FDB5 DE3D F8B5to06E4 A169 4E46

Hex ASCII

RowLog0

Hex ASCII

RowLog1

Mapping the data page determined that the offset for the price column is 0x4f (79),

the update statement began at offset 80

This was done so SQL Server did not have to overwrite a value in which it would need to rewrite as part of the transaction

Therefore the offset was augmented by SQL Server from 79 to 80 to compensate

Taking this into consideration,

the A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template statement executed under transaction 0000:0000032e (0:814) was to update the price column

Start of trn

Key fingerprint = AF19 FA27 2F94 Start 998D of col

FDB5 DE3D F8B5 06E4 A169 4E46

Using the same steps outlined above,

the remaining 2 records updated during this transaction

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

RowLog0

= AF19 FA27 2F94 F8B5 30 00 30 998D 00 FDB5 2E DE3D 00 30 06E4 00 A169 30 4E46 0

HexKey fingerprint 35 00 ASCII 5

Hex ASCII

RowLog1

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Hex ASCII

RowLog0 00

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

Hex ASCII

RowLog1

It is noted that all 3 records updated during this transaction were associated with the “Volcano 62

inch Plasma TV VC2332” product

The fourth transaction executed by SPID 51 was another update statement

The transaction log details show that transaction ID: 0000:0000032f was an update statement affecting 2 records located on 2 separate data pages

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

The same process used previously was followed to identify the affected records

The row offset and page ID values obtained from the transaction log were used to identify the specific value

updated within the following records:

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

The data type within this offset of the row is the ShipStatusID which is a 4-byte integer value

RowLog0 Hex ASCII

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

RowLog1 00 00

Hex ASCII

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

RowLog1 Hex ASCII

Hex ASCII

RowLog0

It is noted that after querying the ShipStatus table the ShipStatusID value of 1 indicates that an order has been shipped and a value of 2 indicates that the order has yet to be shipped

It is the investigator’s belief that the value was updated from 2 to 1 in an attempt to have the customer A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template repeat shipment of the referenced product to the designated address

The fifth transaction executed by SPID 51 was an insert statement

The transaction log

details show that a database transaction 0000:00000330 affected a single row

The same procedure used to map the previous update statements to a data pages was followed to

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

Querying the remainder of the transactions showed that no future modifications were made to this slot within the data page 0000:00000330 therefore the data currently residing on the data page remains unchanged from its state as inserted during this transaction

The values contained within this record are as follows:

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template CCType = Visa

FirstName = Nino

CCNumber = 5518530000000000

LastName = Black

ShipStatusID = 2

Address = 72 Starfell Drive

OrderDate = March 1,

City = SpringLake

Product = XBOX 360

State = AZ

Price = 4

OrderID = 417

ZIP = 14410

The price associated with this item seems inaccurate,

and will be flagged for review by the client

It was also noted that the credit card number used in this insert statement was also associated

with one of the records updated during transaction 815

The sixth transaction executed by SPID 51was transaction 0000:00000331 an update statement affecting 3 records

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

The same procedure used earlier to map the previous update statements to a data pages was

followed here and resolved to the Order table

Using the table schema obtained earlier,

the data type within this row offset is the OrderDate column which contains an 8-byte datetime data type

The first record updated during this transaction was located on data page 211,

slot 20 and the updated column began at offset 74

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Updated Value

OrderDate Column

The method in which computers store multiple-byte values vary,

some use little-endian ordering

With little-endian ordering,

most Keyand fingerprint = AF19 FA27ordering 2F94 998D FDB5 DE3D F8B5 06E4 A169 the 4E46

significant byte of the number is placed in the first storage byte

big-endian does the reverse and

stores the least significant byte in the first storage byte

Microsoft operating systems use little-

which is also true in the way SQL Server stores numeric values

From the transaction log the hexadecimal values from the Rowlog0 and Rowlog1 columns were

RowLog0 Hex (BEO) Hex (LEO) Decimal

switched into LEO and converted to decimal representation

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

RowLog1 0x0000000000E49800 0x000000000098E400 39140

Hex (BEO) Hex (LEO) Decimal

The datetime data type within SQL Server breaks an 8-byte date value into 2 fragments,

the first being the number of days before or after January 1st,

Applications

using the datetime data type to store date values only,

will have a default time value of

The decimal representation of the RowLog1 column is

order date of this record was updated from January 21,

This procedure was used to identify the remaining two values which were updated within

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Updated Value

OrderDate Column

Hex (BEO) Hex (LEO) Decimal

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46 RowLog0 (on disk value prior to transaction)

RowLog1 (committed transaction value)

Hex (BEO) Hex (LEO) Decimal

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

OrderDate Column

Updated Value

Key fingerprint = AF19 FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169 4E46

Hex (BEO) Hex (LEO) Decimal

RowLog0 (on disk value prior to transaction)

RowLog1 (committed transaction value) 0x0000000000E49800 0x000000000098E400 39140

Hex (BEO) Hex (LEO) Decimal

The seventh transaction executed by SPID 51 was transaction 0000:00000332,

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

This record will be further examined during the data recovery stage of this investigation

Step 6: Data Recovery

The seventh transaction executed by SPID 51was transaction 0000:00000332,

statement affecting a single record

When a record is deleted within SQL Server,

which tells the database engine to hide it from future query results even though the

Key fingerprint AF19 within FA27 2F94 998D FDB5 DE3D F8B5 06E4 A169runs 4E46periodically underlying data still =resides the data page

A garbage clean-up process

within SQL Server to physically remove the ghost records within the data pages so the space can

Ghost records contained within a data page are flagged within the page header

Examining the header of the page 0001:0000000158 (1:344) containing the deleted row showed

that the m_ghostRecCnt value was set at 0 indicating that the ghost records had already been

physically removed from the data page

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template

Using the same procedure used earlier in this document to map a data page to the owning

table identified that the data page associated in this transaction mapped to the OrderHistory

This table had an identical schema to that of the Order table

Within the transaction log,

the following value was obtained from the RowLog0 column of the delete statement:

“0x30006C009F0000005000610079006500740074006500200020002000200020002000 200020002000200020002000200046004C003100360036003000320001000000000000003A980

C61736D6120545620564332333332”

The data above is the actual data row deleted from the data page during the transaction

To determine exactly what customer data had been deleted,

it was necessary to reconstruct the

SQL Server uses two different data row structures,

one for rows which contain fixed length columns only,

and another for rows containing variable length columns and/or fixed length columns

Based on the schema obtained earlier in this investigation we know that the Order table contains both fixed and variable length data types

The data row structure for a variable length row is as follows:

A Real World Scenario of a SQL Server 2005 Database Forensics Investigation

© SANS Institute 2007,

As part of the Information Security Reading Room

GIAC Gold Template 1

Fixed length columns

Variable length columns

Source: Inside SQL Server 2005 The Storage Engine5

Legend Item 1

Storage Allocation 1 byte

2 bytes

Fixed length columns 4

Fixed column length for all fixed columns 2 bytes

Location of in row fixed length data columns

2 bytes

Variable length columns

Used length of all variable length columns

Null Bitmap Number of variable length columns within 5 data row Row offset marking the end of each variable 5 length column Location of in row variable length data 5 columns

Description 5

StatusBits A contains data row properties 5

Unused in SQL Server 2005 Row offset to in row location containing the 5 number of columns in the data row

Total number of columns in data row