Getting Data In

How to enable Windows 2003 to audit MSSQL changes for PCI compliance

Splunk Employee
Splunk Employee

For the purposes of PCI compliance, has anyone figured out how to monitor changes/queries (containing user CC info) made to MSSQL 2005 (on Windows 2003)? Is there a way to turn on Windows auditing of the MSSQL for this, such that new specific WinEvents are generated? If so, anyone aware of which EventCodes I should search for my PCI reports?

Also, what PCI-related MSSQL log files should I be monitoring. (I tried monitoring the .ldf file, but it's binary I think?)

0 Karma

SplunkTrust
SplunkTrust

Related answer here:

http://answers.splunk.com/questions/8567/reading-sql-server-2008-log-files-for-usernames-queries-and...

The SQL server transaction log is binary and somewhat undefined, and won't contain true "queries" (as they do not change the database). It will contain changes, but not in the form of the original SQL statement.

This is really not a "Windows 2003" question so much as it is a SQL Server one -- the SQL server engine is going to have to be the one to tell you what is going on.

It's possible the "SQL Server Audit" facility will give you the data you're looking for.

http://www.bradmcgehee.com/2010/03/an-introduction-to-sql-server-2008-audit/

Quoting from the above blog:

Audit data is stored either in a file, or as part of the operating system’s event logs. If you want to be able to analyze and report on this data, you will have to manually import it into your own database. In addition, DBAs will have to manually archive or delete old audit data.

There is no built-in reporting, other than looking at the events in the Log Viewer, assuming that is where you store the audit data. For effective reporting, you will have to create your own reports, most likely using SQL Server Reporting Services.

Stored in a file is good for Splunk and you should be able to easily use Splunk to provide the 'reporting' and 'analysis' functions.