A Walk Around the SQL Server 2012 Audit Feature Timothy P. McAliley Microsoft Premier Field Engineer | SQL Server www.NetComLearning.com
Speaker Introduction: Timothy P. McAliley 14+ years in IT Currently work for Microsoft • Premier Field Engineer – SQL Server, Washington, DC. Previously Worked for: • Symantec • Football Fanatics (Jacksonville, FL) (High Volume e-Commerce) • ASM Research, Inc. (Fairfax, VA) (Defense Contractor) • MCSA: SQL Server 2012, Windows Server 2012 • MCSE: Data Platform, Server Infrastructure
Objective and Scope of this Presentation • Overview of SQL Server 2012 Server and Database Auditing • Demonstration of SQL Server Audit Configuration
Whom Would Hopefully Find This Most Useful • Working in a an environment where auditing is a requirement • Researching options for audit solutions • Looking a for an overview to help get you started on trying the SQL Server Audit feature
Agenda • Introduction to SQL Server Audit • Configuring SQL Server Audit • Audit Actions and Action Groups • Defining Audit Targets • Creating Audits • Creating Server Audit Specifications • Creating Database Audit Specifications • Audit-related DMVs and System Views • Demonstration Using SQL Server Audit • Other Issues and Considerations • Resources for More Information
Introduction to SQL Server Audit
Introduction to SQL Server Audit • SQL Server Audit compared/in relation to – • Change Data Capture • DML/DDL Audit Triggers • C2 Auditing • SQL Trace
Introduction to SQL Server Audit • SQL Server Audit - A note on Performance Impact • % increase in memory utilization • % increase disk i/o • % increase in cpu • What is the Impact of Auditing? • “It Depends” • Synchronous /Asynchronous • Scope of Audit Specifications • Best Practice • Test in Non-Production Environment • Initiate/Sample with a Narrow Scope
Introduction to SQL Server Audit • SQL Server Audit • First introduced in SQL Server 2008 • Event tracking and logging system based on Extended Events • Full operation in Enterprise edition of SQL Server 2014 • Fine-grained auditing (Database level) • Basic auditing in other editions of SQL Server 2014 (Server level) • Comprised of: • Audits • Server and Database Audit Specifications • Actions and Action Groups • Targets
Introduction to SQL Server Audit • Improved Resilience • On Audit Shut Down Server • On Audit Log Failure: Continue • On Audit Log Failure: Fail Operation • Custom User Events • sp_audit_write • Ability to Determine T-SQL Method Used
Introduction to SQL Server Audit • Leveraged by Extended Events - Is a lightweight operating system level eventing engine for servers outside of the SQL Engine • Is designed to be able to process any type of event (auditable events) • Can be integrated with Event Tracing for Windows (ETW) Extended SQL Engine Event Engine
Configuring SQL Server Audit
Configuring SQL Server Audit • Configuring SQL Server Audit is a process: • Create an audit and define the target • Create an audit specification (server or database) • Enable the audit and the audit specification • Read the output events
Configuring SQL Server Audit Configure Server Create Server Enable Audit Monitor and or Database Audit Specification Audit Review Specification
Audit Actions and Action Groups
Audit Actions and Action Groups • Audit actions are additional tasks that can be performed when events occur. Action groups are predefined sets of events that can be used instead of defining individual events. • Categories of actions • Server • Database • Audit • Server audit state changes are always audited • Action Groups • Large number of predefined action groups for each audit category are provided • Simplify setup and management of audits
Defining Audit Targets
Defining Audit Targets • Results of an audit are sent to a target • File • 1 MB Minimum • 2,147,483,647 TB Max Size or Disk Capacity if Unlimited is Configured • Windows Application Event Log • Windows Security Event Log • Results must be reviewed and archived periodically • Security of audit targets • Be cautious with application log as any authenticated user can read it • Writing to security event log requires the SQL Server service account to be added to "Generate Security Audits" policy
Creating Audits
Creating Audits Configure Server Create Server Enable Audit Monitor and or Database Audit Specification Audit Review Specification
Creating Audits Configuration Comment Audit name Name for the audit Queue delay (in milliseconds) Amount in time before audit actions 0 = Synchronous must be processed On Audit Log Failure Continue Shut Down Server Fail Operation Audit destination Audit Target; File or Event Log Maximum rollover files Maximum number of files to retain (only for files) Maximum file size (MB/GB/TB) Maximum size of each audit file Reserve disk space Indicates whether disk space for the audit files should be reserved in advance Maximum files Caps the number of audit files
Creating Audits • Queue Delay - A bit more on configurations specifics: • Specifies the amount of time in milliseconds that can elapse before audit actions are forced to be processed. • A value of 0 indicates synchronous delivery. • The default minimum value is 1000 (1 second). • The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds).
Creating Audits • On Audit Log Failure - Continue - A bit more on configurations specifics: • SQL Server operations continue. Audit records are not retained. The audit continues to attempt to log events and will resume if the failure condition is resolved. Selecting the Continue option can allow unaudited activity which could violate your security policies. Select this option when continuing operation of the Database Engine is more important than maintaining a complete audit. This is the default selection.
Creating Audits • On Audit Log Failure – Shut Down Server - A bit more on configurations specifics: • Forces a server shut down when the server instance writing to the target cannot write data to the audit target. The login issuing this must have the SHUTDOWN permission. If the logon does not have this permission, this function will fail and an error message will be raised. No audited events occur. Select this option when an audit failure could compromise the security or integrity of the system
Creating Audits • On Audit Log Failure – Fail Operation- A bit more on configurations specifics: • In cases where the SQL Server Audit cannot write to the audit log this option causes database actions to fail if they would otherwise cause audited events. No audited events occur. Actions which do not cause audited events can continue. The audit continues to attempt to log events and will resume if the failure condition is resolved. Select this option when maintaining a complete audit is more important than full access to the Database Engine.
Creating Audits • Audit File Maximum Limit -A bit more on configurations specifics: • Maximum rollover files • Specifies that, when the maximum number of audit files is reached, the oldest audit files are overwritten by new file content. • Maximum files • Specifies that, when the maximum number of audit files is reached, any action that causes additional audit events to be generated will fail with an error. • Unlimited check box • When the Unlimited check box under Maximum rollover files is selected, there is no limit imposed on the number of audit files that will be created. The Unlimited check box is selected by default and applies to both the Maximum rollover files and Maximum files selections. • Number of files box • Specifies the number of audit files to be created, up to 2,147,483,647. This option is only available if Unlimited is unchecked.
Creating Audits • Maximum File Size -A bit more on configurations specifics: • Specifies the maximum size for an audit file in either megabytes (MB), gigabytes (GB), or terabytes (TB). • You can specify between 1024 MB and 2,147,483,647 TB. • Selecting the Unlimited check box does not place a limit on the size of the file. • Specifying a value lower than 1024 MB will fail, returning an error. • The Unlimited check box is selected by default.
Creating Audits • Reserve Disk Space - A bit more on configurations specifics: • Specifies that space is pre-allocated on the disk equal to the specified maximum file size. • This setting can only be used if the Unlimited check box under Maximum file size is not selected. • This check box is not selected by default.
Creating Server Audit Specifications
Creating Server Audit Specifications Configure Create Server Enable Audit Monitor and Server Audit Specification Audit Review Specification
Creating Server Audit Specifications • Define the actions that should be audited and the Audit that the results should be sent to • Can be configured in GUI or T-SQL CREATE SERVER AUDIT SPECIFICATION FailedLoginSpec FOR SERVER AUDIT Audit-20121222-171544 ADD (FAILED_LOGIN_GROUP);
Recommend
More recommend
