SQL Server Forensic Analysis

SQL Server Forensic Analysis
By: Kevvie Fowler
Last Updated on Safari: 2008/04/05
Publisher: Addison Wesley Professional
Pub Date: December 12, 2008 (estimated)
ISBN:
Pages: 179





Overview

Nearly all forensic investigations reach a turning point when investigators determine that a database has been breached. There may be no third-party logging system in place, but security personnel are still left wondering if credit card data was stolen or if their system was modified.

The situation is made all the more alarming by the fact that large database breaches are increasing in frequency and that, if investigators are unable to assess and qualify the scope of an intrusion, they are forced to report it as a possible compromise.

These disclosures are painful for companies, their customers, and their shareholders. They are even more painful when, in fact, no sensitive data was ever compromised.

The best way to prevent such breaches and inaccurate reporting is to perform forensic analysis on the SQL server and be certain of extent of any attack or breach.

In SQL Server Forensic Analysis, author Kevvie Fowler presents the first in-depth look into how SQL Server forensics can be used to identify and extract the database evidence needed to confirm, assess, and investigate a digital intrusion.The book begins by giving the reader an overview of SQL server forensics and key SQL server database components, then proceeds to a concise and clear look at database forensic techniques that can be used to gather the evidence hidden within the published and unpublished areas of a SQL server.

Readers will learn how to prioritize, acquire, and analyze database evidence using forensically sound practices and free industry tools. The final chapter will include a case study that demonstrates all the techniques from the book applied in a walk-through of a real-world investigation.This book is appropriate for a wide range of professionals, including digital forensic practitioners, information security analysts, information security managers, database administrators, auditors, and law enforcement professionals. Readers will walk away from the book able to do the following:

• Use SQL Server forensics to verify and assess a digital intrusion
• Identify and extract database information from published and unpolished areas of SQL Server
• Build a SQL sever forensic toolkit
• Detect and Remove SQL Server rootkits
• Recover and reconstruct deleted database material
• SQL Server Forensic Analysis is a must-have book for anyone charged with preventing or investigating modern digital intrusions.
  
  
  Purchase "SQL Server Forensic Analysis" - Retail Price: USD $54.99
  Top of Form
  Online, PDF and Print Book Bundle
  USD $74.23
  Online and PDF Access
  USD $38.49
  Print Book Pre-Order USD $54.99
  USD $38.49
  Bottom of Form
  
  

Linked tables to SQL Server

I have found on more than one occasion since migrating to SQL Server 2005 that if you have an ACCESS 2000 front end there can be an incompatibility of datatypes. Modify all ntext or text columns in SQL to nvarchar(MAX) as those types will no longer be supported. Also, modify bit columns to something like tinyint. Apparently, Access can't handle bit fields.

ORDER BY CLAUSE IN A VIEW

I FOUND THIS ONE INTERESTING. IN CREATING A VIEW IN 2005, YOU CAN USE AN ORDER BY CLAUSE WITHOUT A TOP OR FOR XML. I DON'T REMEMBER THIS BEING THE CASE IN 2000 OR BEFORE.

Msg 1033, Level 15, State 1, Procedure vwTESTING_ORDER_BY, Line 30
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

SURVIVAL!!!

For the 1st time in nearly 25 years, I've survived a reorg!!!

Global Variables in the new SSIS packages

GOOD QUESTION FROM A COLLEGUE:


Got a question about SSIS. There doesn't seem to be a good equivalent to the Global Variables in old DTS in the new SSIS packages. Are you using some solution that you recommend? I know there is Win environment variables and xml config files, but these are not very friendly to move.

MY RESPONSE:


Ooooooo interesting question! I'll look but I'm willing to bet with it
being a .NET solution/project, you can probably put them in a bas file or
class file. Also, check the Package properties. Click the ... Button for
expressions. See if that helps. Being that it's a VS project and not a
MSSQL project, I'd assume listing variables like you would in VB.NET. I
recently starting thinking of SSIS in terms of VS instead of SQL. It really
opens the door to a lot of things. I'd written replication apps in VB6 with
the DMO. I know you can do the same in DTS. So that's where I'd start
looking. Its still encapsulated but pushed out of the IDE. I just added a
VB script to a project. I guess it could be called from a task I don't
have regular VS.NET on this server or my machine, but I know I should be able to add a bas or cls to a project. I'll look further and let you know
what I find!

GOTTA LOOK INTO THIS ONE FURTHER!

SQL SERVER COPY OBJECTS TASK AND 2000 COMPATIBILITY

HERE'S A GOOD ONE!

IN TRYING TO USE SSIS TO COPY DATABASE OBJECTS FROM ONE SERVER TO ANOTHER, IT FAILED. IT FAILED BECAUSE THE DATABASES HAD 2000 COMPATIBILITY!!! I HAD TO CREATE A 2000 DTS PACKAGE, USE THE SQL SERVER COPY OBJECTS TASK TO PUSH SPs BETWEEN THE SERVERS! THIS ONE NEEDS TO BE LOOKED INTO. I GUESS MS ISN'T LOOKING BACK!

LogParser 2.2

just completed parsing an IIS log into SQL Server with a tool I found yesterday. LogParser 2.2, which can be downloaded from MS downloads. check with the MS IIS site http://www.iis.net/default.aspx?tabid=1 you can parse a wide variety of log files from system logs like the event log to SQL Server logs to IIS logs. You have to do a little work from the command line, but I like the tool. I generally prefer a nice user friendly gui but this gets the job done when you understand the params.

also, about a month or so ago i downloaded an XML editor i remember from several years ago that several developers i knew really liked. its much improved and i highly recommend it - XML Notepad. Its a VS 2007 tool. give it a whirl. It made understanding the XML data in my GPS much easier. web configs are much clearer especially changes are made in a hurry. it can also "beautify" the xml code making life a little easier!

determine the compatibility level prior to migration

Ahhhh here's one from the past couple of weeks!

When moving a database to 2005 from 2000. select properties of the database, go to options and see what the compatibility level is. DSNs and apps can be affected by this. For example, I was working on a database issue with linked tables in Access. Users lost connectivity. i.e. they couldn't edit. The database compatibility level was set to the default 2005 when created yet ODBC connections and ADO.NET connection strings were using the SQL Server 2000 driver not the SQL Native Client driver. In reducing compatibility of the database to 2000 level, users were able to edit data with their current 2000 settings. issue resolved without user interaction. As I had used copies of the 2005 database, I had to delete non-2000 versions and rename. The users simply had to select and delete their linked tables and check the "Always prompt for a new location" checkbox. Basically, you're going to refresh your DSN connection and table linkage. Also, developers of a database that migrated a 2000 database after our team did, kept the default compatibility level yet, the team here set it to 2000. Connectivity issues in code will generate lost profit for the developers team due to code rewrite, process changes, etc.

The moral of the story is to determine the compatibility level prior to migration to 2005 AND communicate this to all stakeholderes!!

SSIS dropping tasks from 2000 DTS packages

I found a new issue with SQL Server Integration Services yesterday!

It seems that if you have tasks from a 2000 DTS package with the same name, that SSIS will drop one. This is not the first issue I've found when migrating 2000 DTSs to 2005. I really like the tool but I've been finding gotchas along the way.

Anyone else run into this? any thoughts?

Welcome to Critical Status - Database-Oriented Issues Plus

Welcome to Critical Status - Database-Oriented Issues Plus!!!

I hope that the ideas and issues I find in the mindfield we call IT is of help to you all. Please share your thoughts with me as we can never stop learning!