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?)
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.
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.