Can DB Connect see into MS SQL Audit logs?

[cgisplunk]

Hello,

We have a need to start tracking MS SQL 2008 & MS SQL 2008 Express Audit logs and hence track changes based on them. Can DB Connect do that?
Thank you,
S.

[jcoates_splunk]

Hi from the future... we now have an Add-on for Microsoft SQL Server that helps you configure DB Connect for this purpose: https://splunkbase.splunk.com/app/1524/

[Richfez]

For future reference, we do exactly this and it's far more involved than one would have guessed.

Assume you have created a Server Audit Specification and a Audit and have those recording the auditing you need to file. Now, the problem is that it's in a file - it's not quite accessible the usual SQL way. The permissions in order to read those are quite extensive, and in our case we didn't want to allow the Splunk DB user such rights, so we did the following.

Create a table (we put ours in a newly created "Audit" DB) with an auto-incrementer key field and all the other fields you'll need. Review SQL DB auditing tables to figure out what you need to gather from the Audits. Not all columns are important in all environments.

Create a Stored Procedure that "inserts" the data you want into that table, like "INSERT INTO dbo.MyAuditTable (event_time, blah blah, blah) select from dbo.fn_get_audit_file("filename and path with wildcards"). We put in a "where event_time > ( select max(event_time) from dbo.ourAuditTable"

Create a job to run on some schedule (ours is hourly, it's good enough for us, your mileage may vary) that runs the above Stored Procedure to update the table regularly.

NOW you can use Splunk's DB connect to directly suck out the data you are schlepping into that table as if it's any other table. This is fully specified elsewhere and will have some minor dependencies on what exactly you did when you set up the above, but should be relatively easy - the hard part is making the SQL Audit tables available in the first place.

[mbenwell]

If audit logs are configured to go to the windows event log, I dont think it would be possible with DB Connect. If audit logs were sent to file, I think it would be possible as you can construct queries the same way you would using something like sql management studio. The catch would be in identifying what is new in the file (rising column is the reference in documentation), otherwise you would have to grab the whole file. It might not be the most efficient way of getting your audit data into Splunk though.

For audit logs, as you would end up reading directly off the filesystem/event logs, maybe using the Universal Forwarder to monitor the file/directory/event logs would be the best choice. If installing the Universal Forwarder isn't an option on the SQL server, you could send the logs to a remote filesystem that Splunk can read directly.

[cgisplunk]

Turns out SQL Express 2008 does not have the option for Security Audit., Hmm, a wild goose chase. Thank you though in any case.

[jcoates_splunk]

Yeah, sad to say.

[mbenwell]

Ah, well if the data is in the database then yes you can use DB connect. I assumed you meant SQL Server audit logs not Sharepoint audit logs.

As long as you have credentials with access to the data, you should be able to query it. As for SQL express, by default is only available to the local machine so you might need to reconfigure it so you can query the database across the network.

[cgisplunk]

mbenwell,
That'd be too easy. UF is already installed on the host but the audit trail is kept inside SQL. The front-end app is SharePoint 2012. We recently lost our DBA, so you can understand my difficulty now, I am not a DBA, just a Splunk admin.
Thank you.
S.