Oh boy, this article may get me into troubles, maybe even for its "simple" approach according to the KISS principle, but here we go anyway.
Public impact of affairs like Enron, WorldCom or Lernhout & Hauspie did create a huge shockwave, so much that almost every big company nowadays feels the impact. Especially the IT departments, because of the lack of:

• formal security procedures,
• separation of responsibilities
• authority of signatures
• formal procedures for installations, chain of command, ..

Indeed, in many companies, which do not consider IT as being strategic to their interests but rather a support tool to get their business done, now have to make a huge investment in order to get all "theoretical" things done. And it needs to be said: they have to get it to be done properly !

Not only do the procedures need to be written for business processes, but they need to provide physical evidence that the procedure is applied and works as designed. For SQL Server these procedures contain, for example;

• A formal installation procedure for a SQL Server instance, implementing best practices for security,
• The request and install procedure to have a SQL Server instance installed on a server
• The request and install procedure to have a database installed on a certain SQL Server instance
• Backup regimes that are available, have rules for retention for the backups in a safe zone
• Policies for user access and passwords, stating at least that passwords must be different at the various servers.

For many of us, the segregation of duties will have a big impact. Especially for system engineers that maintain servers, regardless of the logical type of server purpose (development, quality assurance, pre-production-approval, production).

C2 or SOX

Let's start with C2.
According to Books Online :

In Microsoft© SQL Server™ 2000, use the c2 audit mode option to review both successful and unsuccessful attempts to access statements and objects. With this information, you can document system activity and look for security policy violations.

You can switch it on by enabling the 'c2 audit mode' using sp_configure.

In many heavy duty systems, when you use SQL Profiler, you can see Profiler messages stating the tool missed some events because of heavy activity. With C2, you cannot apply filters, so if you enable it, you may end up with tons of MBs of trace files, containing the full audit trails, and maybe also including these service messages: "missed some events".

With SOX, chances are you don't need to trace approved applications, because each SOX-approved application did get tested and approved by a number of responsible parties. So once an application is signed off for approval in the SOX-documentation, everything performed with that application is considered to be legitimate.

So, in fact, what you need to do for SOX is capture all evidence of other activities performed on your SQL Server instance. Does this also mean you have to monitor your operating system itself? Probably it does. So restrict access to your Windows server to only those who are supposed to actually do something with, on, or for that physical sever.

SQL Server 2005 will implement folder security at installation time, this way, non-administrators are prohibited access to the folders created by your SQL Server installation, except for the service-accounts you specified at install time or when you modify a service account using the SQL Server Configuration Manager.

So, what else needs to be done for SQL Server?

You'll have to implement a tracing system, in which you can exclude the approved applications. You'll also have to implement a system to backup your trace files to a safe zone, other than your normal backup safe zone, because probably access to those saved trace files will need to be restricted too, well, certainly not available for your regular system administrators of your SQL Server.

The retention period for these trace files will probably also exceed the normal retention periods for databases. That fact by itself may already justify the use of separate safe zones.

I'm pretty sure software vendors already provide products to support both evidence gathering as well as reporting. One example is Idera's: SQL compliance manager

How did we implement these SOX-traces for SQL Server?

For the moment, all our SOX-affected system are still on SQL Server 2000. We've implemented a startup procedure that starts a proprietary trace. This is actually the C2-trace (full audit), but we added filters to it. Applications that have been approved by the SOX chain of approval get registered in a table in the master database for the server that hosts their database(s). This table is used to configure the filters.

It needs to be said that applications like Query Analyzer, Enterprise Manager, DTSexec certainly, and other tools are to be considered as NOT APPROVED !

Because we want the trace files to be placed in the instance's \LOG folder, we implemented a proc that determines that location, depending on the startup parameters of the SQL-instance. We've also implemented a job to copy the trace files to a safe zone on a very regular base. This way, evidence gathering is fulfilled.

If we need to take action on a SQL Server, we record the justification of the action in a log application and it will be approved by our chain of command.

Reporting, based on the traces, still needs to be implemented. The big question is who in the company's security system will be tasked with investigating the trace data, gathering the justification of actions and reporting abuse or non-justified actions. For the moment the focus is on evidence gathering.

For our developers, one of the things they need to adjust is the connection string. They need to provide the application name and SOX-approval code in every connection string they provide. Only in this way will the filter criteria will work.

Any user access using a privileged account will be monitored.

There is always this question that may have a huge impact regarding data resource you may need : "If we detect/suspect abuse, must we also be able to dig into the db-logs for database data analysis ?" If the answer is "yes", you'll also need to keep all db log backups at least for the same retention period as your SOX-traces. You'll also need some type of log analyser software like Red Gate's Log Rescue

Our aim is to prevent abuse and put detection in place. There will always be burglars around, and they may even get away with some of their actions, but at least we've put systems in place to detect and report anomalies on short terms.

As always I'm still open for comments, suggestions, additions, alternatives, etc.

Sample SQL Servertables and stored procedures for SOX: Code. Some script remarks when you implement it on SQL2000.

If you implement these scripts on SQL2000, that startup stored proc will not be able to start the trace because it cannot read the reagistry at startup time! Simply adding a SQLAgent job that starts whenever SQLAgent is started, that executes the startup proc (sp_Proprietary_StartSOXTrace), is a good workaround for this issue.

Some interesting links :

• http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=161&messageid=212223
• http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=161&messageid=118657&p=1
• http://www.sarbanes-oxley-forum.com/modules.php?name=Forums&file=viewtopic&t=988
• http://www.krell-software.com/omniaudit/data_auditing_for_sarbanes_oxley_compliance.asp
• http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=7888
• http://www.databasejournal.com/features/oracle/article.php/3650681