Data Management

Document Sarbanes-Oxley compliance with SQL Server 2005

When the Sarbanes-Oxley auditors ask for the documentation you keep on the security of your production database environment, what will they find? Read how DBA Tim Chapman uses data manipulation and data definition tools built into SQL Server 2005 to document system security.

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. In the end, we decided to use SQL Server 2005's built-in tools to create our own "homegrown" auditing system.

What's new in SQL 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 set up 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, I'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. I'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.

I'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.

Now I'll demonstrate how to use DDL triggers to track database-level changes.

Know your events

To implement an effective custom auditing solution, you must understand the scope of the events that can be caught via DDL triggers. The Server scope includes events specific to the SQL Server instance, such as dropping, altering, or creating Databases, Logins, or Endpoints. The Database scope includes events that are database specific, such as dropping, altering, or creating Stored Procedures, Views, Tables, Users, or Triggers. A large majority of the events you will be writing triggers for in your auditing solution will be using the Database-scope events.

What to watch for

Let's assume your ultimate goal is to be able to provide auditors with a list of any code changes that have occurred in your production environment. What types of changes should you document for them? SQL Server 2005 provides numerous database-level DDL events to allow you to track code changes that Sarbanes-Oxley auditors will want to examine. To demonstrate our ability to monitor unauthorized code changes, let's create a DDL trigger to track changes to stored procedures.

I will track our changes using the same table I used earlier in the article, but I'll add a column to indicate the trigger scope. As a reminder, my original DDLTriggerTest table contained the following fields: IDCol SMALLINT IDENTITY(1,1) PRIMARY KEY, XMLEvent XML, DatabaseName VARCHAR(50), SystemUser VARCHAR(50), and EntryDate DATETIME DEFAULT (GETDATE()).

To add the new column, run this script:

ALTER TABLE EventTableData
ADD TriggerScope VARCHAR(10)

By specifying the trigger scope in my audit table, I can use the same audit table for my tracking server-level and database-level DDL events.

Once you have the necessary table structure in place to track your database events, you're ready to create the trigger to catch these events. Run this script to create the trigger:

CREATE TRIGGER tr_ProcedureEvents
ON DATABASE
FOR DDL_PROCEDURE_EVENTS 
AS
BEGIN

      DECLARE @Scope VARCHAR(10)

      SET @Scope = 'Database'
      INSERT INTO DDLTriggerTest..EventTableData
(XMLEvent, DatabaseName, SystemUser, TriggerScope)
      VALUES
(EVENTDATA(), DB_NAME(), SYSTEM_USER, @Scope)
END

There two are important differences in this trigger compared to the trigger I created to track logins. The first difference is the ON DATABASE statement, which indicates that this trigger will be listening for database-level events only. The second difference is the statement FOR DDL_PROCEDURE_EVENTS, which specifies that I will be monitoring any statement ran on the database that intends to ALTER, CREATE, or DROP a stored procedure.

After you create the tr_ProcedureEvents trigger on your production database, all stored procedure modifications will be logged for later review. If necessary, you can alter this trigger to send yourself an e-mail to let you know someone is trying to modify your production database.

An ounce of prevention

Configuring the trigger to send an e-mail alert is a good idea, but you will only receive the e-mail after the action has occurred. In many instances, the lag between the time the trigger fires and when you receive the alert can be disastrous. For example, think about if someone hacks into your system and deletes a critical table before you get the alert.

Fortunately, DDL triggers can detect events, as well as prevent them from being executed in the first place. The ability to ROLLBACK events that you do not want to occur is one of the most useful features of the Database and Server level DDL triggers, and one that will make any Sarbanes-Oxley auditor smile.

For example, if my production environment is at a point where I know I don't want any stored procedure modifications to occur, or I don't want any more logins created on our server, I can include logic in our trigger to reverse anything that the DDL statement was attempting to accomplish. So, not only do you have the ability to track any changes to your production environment, you can also prevent things from happening.

The trigger I created above can be altered to not allow any stored procedure modifications in the database. Here's the altered version:

ALTER
TRIGGER tr_ProcedureEvents
ON DATABASE
FOR DDL_PROCEDURE_EVENTS
AS
BEGIN

          ROLLBACK

          PRINT 'Procedure modifications

are not allowed on this database.'

          DECLARE @Scope VARCHAR(10)

          SET @Scope = 'Database'
          INSERT INTO DDLTriggerTest..EventTableData
(XMLEvent, DatabaseName, SystemUser, TriggerScope)
          VALUES
(EVENTDATA(), DB_NAME(), SYSTEM_USER, @Scope)
END

Now, if I try to create a procedure on the database, I get the following message:

Procedure modifications are not allowed on this database.

(1 row(s) affected)

Msg 3609, Level 16, State 2, Procedure Test, Line 3

The transaction ended in the trigger. The batch has been aborted.

Our homegrown trigger allows us to log the attempt to create a stored procedure and prevent that change from occurring.

Planning ahead

The key to creating an effective system for auditing security events on your server is to design a strategy for how best to use DDL triggers. Your strategy will involve balancing the actions you want to monitor, and the actions you want to prevent from occurring on your server. If you prevent too many things from occurring, you may wind up prohibiting necessary production tasks.

The right combination of monitoring and prevention will help generate an audit trail you can use to document compliance with Sarbanes-Oxley security requirements.

Related resources from TechRepublic

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

5 comments
jmhmaine
jmhmaine

The issue I see is that most applications abstract the login so that you don't have to create a SQL Server login for each individual authenticating. If you have mixed security, and all of your users are part of your domain, then this is not an issue because their AD login will be stored. The issue I see is with web applications that access a SQL DB with one account and logins are maintained in different process such as ASP.NET Membership. How would track those data access and modification with those setups?

robo_dev
robo_dev

If you had to create your own SOX compliance system, why not use Microsoft Sharepoint? Or Lotus Notes even, if you have that in house? With Sharepoint you could whip together a fully functional SOX compliance database in about twenty minutes. (and if it makes you feel better, MS SQL is the back-end db for sharepoint) There are several commercial SOX compliance apps that may cost more initially (eg. OpenPages Sox Express), but alerady have the workflow parts worked out, the role relationships, all the GUIs. The issue is that for SOX compliance, you need WORKFLOW, not just database. The amount of time spent to workflow-enable a homegrown SQL database would be excessive. My opinion is that MS SQL is the wrong tool for the job. You're trying to adapt a Honda Accord to run the Indy 500...

RipVan
RipVan

...and the time/expense of this crazy compliance! Yet I wonder if government follows the same regulations they set for others. They seem to have a very hard time producing emails and electronic records when they are required by law to produce certain documentation. And if you are a news junkie and really follow the testimony, you will hear overly long, sad tales (sometimes quite clownish) of just how the government lost important records. Just try that in your business. (You will be punished as much for the lapse that allowed records to be lost as you will for the lameness of the excuse.)

RealGem
RealGem

We have a similar situation. We have separate logs that our applications write to. We're required by local legislation to track all access to a particular type of data, but we have a single app userid, so we have to go this route. I'm not the DBA myself, but apparently we cannot pass the app login name to SQL Server. That would make more sense to me.

chapman.tim
chapman.tim

The idea here is not to totally track everything that happens in the enterprise...it is to track who changes what (data/code) inside of the database.

Editor's Picks