SQL Server 2008: Database Auditing Standards and Best Practices

This will be the first in a series of blog posts in the next several months designed to touch on many of the topics covered in QuickStart’s SQL Server 2008 training courses; mores specifically database auditing with guidance and discussion for SQL Server 2008 and SQL Server 2008 R2. If the next version of SQL Server (code named ‘Denali’) adds or changes this, I will cover that towards the end of the series. In this first blog, we’ll look regulatory requirements, general organizational security practices, and auditing best practices for databases irrespective to the DBMS chosen. The second blog will continue looking at best practices, specifically what audit event data needs to be in the log and protecting audit systems and data. In the third blog, I’ll discuss the available auditing methods in SQL Server 2008/R2 and the advantages and disadvantages of each one. In the fourth and fifth blogs we’ll do a deeper dive into two specific auditing mechanisms, SQL Server Audit (built-in fine grain auditing introduced in SQL Server 2008), SQL Server Event Notifications (introduced in SQL Server 2005).

SQL Server 2008 Training: Auditing Standards and Requirements

With the growth in importance and pervasive of IT in today’s business has grown government regulations at all levels, from local government requirements all the up to federal and even international regulations and guidelines. This blog is not meant to provide legal advice against the myriad of requirements that exist for data privacy and protection. For that, you will need qualified legal guidance.

A quick search for regulatory audit requirements generates a long list of governmental mandates. The list includes well knownlaws of the last decade or so, like HIPAA and Sarbanes-Oxley, for health careand financial services industries, respectively. Other regulations exist, such as GLBA, Basel II, 21 CFR Part 11, State data breach disclosure laws, FISMA, FERC, NERC {As an aside, let me introduce you to one of my favorite websites:http://www.acronymfinder.com/ – great for finding the definition of hundreds of thousands of acronyms and abbreviations). To help support the laws, various organizations and partnerships have generated guidance documents and policies, like CoBIT and ITIL.

SQL Server 2008 Training: Auditing Best Practices – What Data to Audit

Let’s take a look at Sarbanes-Oxley (SOX): generally, to comply, you will need to answer who changed or deleted data, made changes to database schema (e.g. dropped a table or a column) and all with special detailed emphasis on privileged users. More of agreyarea is auditing unsuccessful attempts to perform those changes. You are generally not required to audit for who accessed data (reads), just for changes. SOX compliance is more about preventing manipulation of data than privacy requirements.

HIPAA on the other handis definitely concerned with data privacy and will include requirements for who has access – all types of access, including read access – and who has used that access. The need to audit who reads data generally adds additional complexity to auditing solutions and will reduce the choices of available methods.

Auditing best practices require us to analyze our data needs, the regulatory requirements, any additional organizational auditing needs to create an auditing plan. As we configure auditing, we want to only audit for what we need. It is often very easy to include far more information than we need in our audit reports. Too much information can prevent us from recognizing security compliance violations which can be more damaging than just not having all the information asked for by an external audit. Most regulations state which types of information need to have access tracked.

In my nextblogwe will continue your SQL Server 2008 training as we discuss specific pieces of information that all good auditing solutions include for every auditing event. We’ll also look at specific tools and mechanisms for enabling auditing compliance.

Questions? Please share them below. Andof course, if you’re Interested in learning more, look into one of our many SQL Server 2008 training courses.

Thank you!

Steven Allen, QuickStart Intelligence, System Engineer