topic Re: Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually? in All Apps and Add-ons

Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually?

hochit — Wed, 18 May 2016 08:50:34 GMT

http://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/SQLServerconfiguration

I'm following this guide to create table audit for each table. However, I have a question: What if we have >100 tables. Is there any short hand that can create audit table for ALL tables in one step?

Re: Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually?

jkat54 — Wed, 18 May 2016 16:02:05 GMT

Yes, enable it for the entire database instead:

http://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/SQLServerconfiguration#Example_of_creating_an_audit_object_at_the_database_level

Or the entire server:
http://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/SQLServerconfiguration#Example_of_creating_an_audit_object_at_the_server_level

These are from the link you posted.

Re: Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually?

hochit — Wed, 01 Jun 2016 07:58:12 GMT

My understanding database/server level audit can only capture actions like login, permission change, etc, but not table access (CRUD). Am I correct?

I wonder in such case we should run a short script retreiving all tables and create audit spec on each of them.

(presudo)
foreach

CREATE DATABASE AUDIT SPECIFICATION MSSQL_Table_Specification 
FOR SERVER AUDIT MSSQL_Table_Audit 
ADD (UPDATE ON <table> BY <schema> ) WITH (STATE = ON) ;

Is it possible? Thanks

Re: Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually?

hochit — Fri, 03 Jun 2016 03:51:42 GMT

Another hard question from customer, would database audit above impact DB performance?

Re: Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually?

jkat54 — Fri, 03 Jun 2016 12:28:15 GMT

Auditing absolutely will impact performance, especially if its a heavily used database/table/etc and then even more so if you're writing audits to a slow disk.

Best Practices
-Write audit logs to a centralized location
-To facilitate processing of the audited data, load the logs into a database
-Use a file as a target for optimal performance
-Use targeted auditing to minimize the collected data and better performance
-When writing to the Windows logs, ensure that the roll-over policy of the Windows Logs, coincides with that of your audit strategy
- See more at: http://www.sqlshack.com/understanding-sql-server-audit/#sthash.71JR77T3.dpuf

As for your previous question, it's best to go to a SQL forum to ask that as I'm not a DBA.

Re: Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually?

hochit — Sat, 04 Jun 2016 06:32:26 GMT

I verified it myself. We can indeed create audit spec on schema insteawd of each table to quickly add auditing on all tables. Defining all actions including select, insert, update, delete are still required.

ALTER DATABASE AUDIT SPECIFICATION MSSQL_Table_Specification  
FOR SERVER AUDIT MSSQL_Table_Audit 
ADD (select ON SCHEMA::dbo BY dbo),
ADD (insert ON SCHEMA::dbo BY dbo),
ADD (update ON SCHEMA::dbo BY dbo),
ADD (delete ON SCHEMA::dbo BY dbo)
WITH (STATE = ON) ;

Re: Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually?

hochit — Sat, 04 Jun 2016 06:45:01 GMT

It's chicken and egg problem... With audit logging (and also Splunk), we can have more insight what impact DB performance on the other hand. So probably the impact of introducing audit is a positive trade-off. I hope my customer would understand this.

Thanks, appreicated!