Transcript sql server auditing - Central New Jersey SQL User Group

SQL SERVER AUDITING
Jean Joseph
DBA/Consultant
Contact Info:
Email: [email protected]
Blog:
https://tsqlhelp.wordpress.com/
Tweeter: @garella79
What is SQL Server Auditing?
An audit is the combination of several elements into
a single package for a specific group of server actions
or database actions. The components of SQL Server
audit combine to produce an output that is called an
audit, just as a report definition combined with
graphics and data elements produces a report.
Tools To Audit SQL Server
 Server Level




SQL Server Auditing.
Third Party Tools.
Extended Events.
Profiler
 Database Level






SQL Server Change Tracking.
SQL Server Change Data Capture.
DML Triggers.
DML OUTPUT Clause(SP or Hard Hoc Queries).
Third Party Tools(Idera, Red-gate, ApexSQL …).
Database Audit Specification.
SQL Server auditing
 Three Types of audit:
 Server Audit
 Server Audit Specification
 Database Audit Specification
Server Audit
The Server Audit is the parent component of a SQL Server audit and can contain both
 Server Audit Specifications.
 Database Audit Specifications
 It resides in the master database, and is used to define where the audit information will be
stored, file roll over policy, the queue delay and how SQL Server should react in case auditing is
not possible
 The Server Audit Configuration Required
 The Server Audit name
 The action to take in
 Continue and ignore the log issue
 Shut down the server
 Fail the operation
 The audit destination

Permissions required:
ALTER ANY SERVER AUDIT.
 CONTROL SERVER.

DEMO
Server Audit Specifications
Server Audit Specifications can be audited individually, such as auditing a select event on a
table. This is referred to as an Audit Actions. In most cases audit actions are grouped
together resulting in Audit Action Groups. This facilitates audit specification configuration
since actions which form a logical unit are included in a single group saving you from having
to specify each one individually
 have 3 categories of actions:
 Server level actions.
 Database level actions.
 Audit level actions which audits actions on the auditing process itself. Some audit actions
are automatically audited such as changing the state of an audit to on or off.
 To create a Server Audit Specification, three things need to be specified:




Name of the audit specification. (optional, default name will be assigned)
Server Audit (which defines the target the selected events should be logged to)
Audit Action Type. (Events which should be audited)
Examples
 SUCCESSFUL_LOGIN_GROUP
 FAILED_LOGIN_GROUP
 DBCC_GROUP
Permissions required:
ALTER ANY SERVER AUDIT.
 CONTROL SERVER.

DEMO
Database Audit Specification
This is at the database level. Using more granular auditing can minimize the performance
impact on your server. This is done by using a Database Audit Specification which is
unfortunately only available in Enterprise edition. Using the Database Audit Specification,
auditing can be done at object or user level
 The Database Audit Specification name (Optional, default name will be assigned)
 The Server Audit that the specification must be linked to.
 The Audit Action Type. There are both.



Audit Actions
Audit Action Groups (which may be selected ,INSERTED and UPDATE D or DELETED)
The Object Name of the object to be audited when an Audit Action has been selected
 The Schema of the selected object
 The Principal name. In order to audit all users, use the keyword public in this field

Unfortunately it cannot be done at column level as of yet
Permissions required:
ALTER ANY DATABASE AUDIT SPECIFICATION.
 ALTER or CONTROL (permission for the database to which they would like to add the audiT)

DEMO
TAKE AWAY





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

Write to a file, the SQL Server Service Account must have both Read and Write permission.

Membership in the sysadmin fixed server role.




CONTROL SERVER.
VIEW SERVER STATE.
ALTER ANY AUDIT.
VIEW AUDIT STATE
Troubleshoot SQL Server Users Action
Without Audit Trail
DEMO
THANK YOU!
LINK:
https://msdn.microsoft.com/en-us/library/cc280386.aspx