Using SQL Server 2005 to document Sarbanes-Oxley compliance

As the DBA for revenue-generating applications, I am frequently invited by my CIO to meetings with Sarbanes-Oxley auditors to discuss the security and integrity of corporate data. In a nutshell, we're expected to answer any number of questions, via appropriate documentation, about who has access to our data, how access is granted, and how we are monitoring to prevent someone from sneaking in, logging on, and doing something they shouldn't be doing.

My CIO and I have looked at a number of commercial solutions for documenting Sarbanes-Oxley compliance. However, we decided to use SQL Server 2005's built-in tools to create our own "home-grown" auditing system.

What's new in Server 2005
Before SQL Server 2005, we used data manipulation language (DML) triggers to tell us when changes to data occurred in the database. We could point to the audit log and say, there is a list of all of the INSERT, UPDATE, and DELETE statements we've issued since the last audit, including the time, date, and person or program that executed the statement.

DML triggers, which are specialized stored procedures that the database engine fires, have been very useful in giving us the ability to create an audit trail of changes to our data. However, DML triggers were limited to only fire when changes to data occur. Before SQL Server 2005, we had no good way to track structural or security changes to our database server.

SQL Server 2005 supports data definition language (DDL) triggers. These triggers can be setup to fire when any number of server- or database-level events occur. DDL triggers allow us to track critical changes to our database environment--changes that may be intentional, by mistake, or malicious.

DDL events are fired at two different scopes: the database-level and the server-level. When you design DDL triggers, it's important to determine what events you want to audit, and to determine in which scope each event occurs. In this article, we'll write a trigger to capture logins, which are server-level events.

Tracking all logins
Here's a good example of how DDL triggers improve our ability to monitor the database. In previous versions of SQL Server, it was difficult to keep track of when new logins were created, when new databases were created, or when new permissions were assigned to different users.

It's relatively easy to track such security changes in SQL Server 2005. To demonstrate how, let's design a solution that keeps track of all activity on the server, regardless of the user. We'll use the DDL trigger event DDL_LOGIN_EVENTS to set up the audit trail. This event trigger will look for any login events that occur on the server, including all CREATE LOGIN, ALTER LOGIN, and DELETE LOGIN events.

Of course we'll want to track all changes in a database to which the DBA and development team have very limited access. Limiting the ability of the DBA to change the audit trail of a server or database is a key step in ensuring the integrity of the audit trail.

In SQL Server 2005, create a database for the audit trail by running the statement CREATE DATABASE DDLTriggerTest, and then define the following fields:

• IDCol SMALLINT IDENTITY(1,1) PRIMARY KEY,
• XMLEvent XML,
• DatabaseName VARCHAR(50),
• SystemUser VARCHAR(50),
• EntryDate DATETIME DEFAULT (GETDATE())

Note that the table makes use of the XML data-type, which is new in SQL Server 2005. As you'd assume by its name, its job is to hold XML data.