A Walk Around the SQL Server 2012 Audit Feature Timothy P. McAliley - - PowerPoint PPT Presentation

▶
a walk around the sql server 2012 audit feature
SMART_READER_LITE
LIVE PREVIEW

A Walk Around the SQL Server 2012 Audit Feature Timothy P. McAliley - - PowerPoint PPT Presentation

Feb 10, 2024 378 likes •942 views

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

slide-1
SLIDE 1

A Walk Around the SQL Server 2012 Audit Feature

Timothy P. McAliley Microsoft Premier Field Engineer | SQL Server www.NetComLearning.com

slide-2
SLIDE 2

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
slide-3
SLIDE 3

Objective and Scope of this Presentation

  • Overview of SQL Server 2012 Server and Database Auditing
  • Demonstration of SQL Server Audit Configuration
slide-4
SLIDE 4

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

slide-5
SLIDE 5

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
slide-6
SLIDE 6

Introduction to SQL Server Audit

slide-7
SLIDE 7

Introduction to SQL Server Audit

  • SQL Server Audit compared/in relation to –
  • Change Data Capture
  • DML/DDL Audit Triggers
  • C2 Auditing
  • SQL Trace
slide-8
SLIDE 8

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
slide-9
SLIDE 9

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
slide-10
SLIDE 10

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
slide-11
SLIDE 11

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 Event Engine SQL Engine

slide-12
SLIDE 12

Configuring SQL Server Audit

slide-13
SLIDE 13

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
slide-14
SLIDE 14

Configuring SQL Server Audit

Create Server Audit

Configure Server

  • r Database Audit

Specification Enable Audit Specification Monitor and Review

slide-15
SLIDE 15

Audit Actions and Action Groups

slide-16
SLIDE 16

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
slide-17
SLIDE 17

Defining Audit Targets

slide-18
SLIDE 18

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

slide-19
SLIDE 19

Creating Audits

slide-20
SLIDE 20

Creating Audits

Create Server Audit

Configure Server

  • r Database Audit

Specification Enable Audit Specification Monitor and Review

slide-21
SLIDE 21

Creating Audits

Configuration Comment Audit name Name for the audit Queue delay (in milliseconds) 0 = Synchronous Amount in time before audit actions 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

slide-22
SLIDE 22

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

slide-23
SLIDE 23

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.

slide-24
SLIDE 24

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

slide-25
SLIDE 25

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

  • therwise 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.

slide-26
SLIDE 26

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
  • nly available if Unlimited is unchecked.
slide-27
SLIDE 27

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.
slide-28
SLIDE 28

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.
slide-29
SLIDE 29

Creating Server Audit Specifications

slide-30
SLIDE 30

Creating Server Audit Specifications

Create Server Audit

Configure Server Audit Specification Enable Audit Specification Monitor and Review

slide-31
SLIDE 31

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);

slide-32
SLIDE 32

Creating Server Audit Specifications

  • Choose a server audit if you want to monitor the following:
  • Actions that impact the entire server
  • Actions that monitor changes across all databases
  • Actions that monitor changes to schemas to all databases
  • Server Audit Specifications reside in Master DB
slide-33
SLIDE 33

Creating Database Audit Specifications

slide-34
SLIDE 34

Creating Server Audit Specifications

Create Server Audit

Configure Database Audit Specification Enable Audit Specification Monitor and Review

slide-35
SLIDE 35

Creating Database 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 DATABASE AUDIT SPECIFICATION BackupRestoreSpec FOR SERVER AUDIT Audit-20121222-171544 ADD (BACKUP_RESTORE_GROUP);

slide-36
SLIDE 36

Creating Database Audit Specifications

  • Choose database audit specifications if you want to monitor:
  • Actions specific to a database, object, or schema
  • Specific actions of a principal within a database
  • Specific actions (SELECT, DELETE, UPDATE, and other Data Manipulation

Language [DML] statements) within a database

  • Database Audit Specifications Reside in Audited Database
slide-37
SLIDE 37

Audit-related DMVs and System Views

slide-38
SLIDE 38

Audit-related DMVs and System Views

  • SQL Server provides a set of DMVs and system views for

managing SQL Server Audit

Audit-related System Views sys.server_audits sys.server_file_audits sys.server_audit_specifications sys.server_audit_specification_details sys.database_audit_specifications sys.database_audit_specification_details Audit-related DMVs sys.dm_server_audit_status sys.dm_audit_actions sys.dm_audit_class_type_map

slide-39
SLIDE 39

Demonstration Using SQL Server Audit

slide-40
SLIDE 40

Demonstration Using SQL Server Audit

  • In this demonstration you will see how to:
  • Create a SQL Server Audit and define its target
  • Create and enable a database audit specification
  • Create an auditable event and view the event in the Windows Event

Viewer

  • View the results of a file-based audit
slide-41
SLIDE 41

Demonstration Using SQL Server Audit

Create Server Audit

Configure Server

  • r Database Audit

Specification Enable Audit Specification Monitor and Review

slide-42
SLIDE 42

Other Issues and Considerations

  • Audit Volume
  • Auditing and Encryption
  • Features, including auditing, behave the same with or without

Transparent Data Encryption (TDE)

  • Multi-Server Auditing with System Center 2012 -Operations

Manager, Audit Collection Services (ACS)

  • Integration with 3rd Party Auditing Tools
  • Enforce with SQL Server Policy Management
slide-43
SLIDE 43

Other Issues and Considerations

  • Attaching
  • Attaching a database that has an audit specification and specifies a GUID

that does not exist on the server will cause an orphaned audit

  • specification. Because an audit with a matching GUID does not exist on

the server instance, no audit events will be recorded.

  • To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION

command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.

  • You can attach a database that has an audit specification defined on it to

another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.

slide-44
SLIDE 44

Other Issues and Considerations

  • Mirroring/AG
  • A database that has a database audit specification defined and that uses

database mirroring will include the database audit specification. To work correctly on the mirrored SQL instance, the following items must be configured:

  • The mirror server must have an audit with the same GUID to enable the database audit

specification to write audit records. This can be configured by using the command CREATE AUDIT WITH GUID= <GUID from source Server Audit>.

  • For binary file targets, the mirror server service account must have appropriate

permissions to the location where the audit trail is being written.

  • For Windows event log targets, the security policy on the computer where the mirror

server is located must allow for service account access to the security or application event log.

slide-45
SLIDE 45

Other Issues and Considerations

  • Permissions -
  • Any authenticated user can read records sent to the Windows

Application Log

  • Writing to the Windows Security log requires the SQL Server service

account to be added to the Generate security audits policy

  • Access to Files For Auditors
slide-46
SLIDE 46

Other Issues and Considerations

  • Permissions -
  • To Create/Alter/Drop Server Audit:
  • the ALTER ANY SERVER AUDIT or the CONTROL SERVER
  • To Creat/Alter/Drop Database Audit:
  • ALTER ANY DATABASE AUDIT permission or the ALTER or CONTROL permission on the

database

  • To View Catalogs (one of the following):
  • Membership in the sysadmin fixed server role.
  • The CONTROL SERVER permission.
  • The VIEW SERVER STATE permission.
  • The ALTER ANY AUDIT permission.
  • The VIEW AUDIT STATE permission (gives only the principal access to the sys.server_audits

catalog view).

  • To Use DMVs:
  • VIEW SERVER STATE or ALTER ANY AUDIT
slide-47
SLIDE 47

Resources for More Information

slide-48
SLIDE 48

Resources for More Information

  • SQL Server Common Criteria Site:

– http://www.microsoft.com/sqlserver/en/us/common-criteria.aspx

  • Introducing SQL Server Extended Events

– http://go.microsoft.com/fwlink/?LinkID=237197

  • Understanding SQL Server Audit

– http://go.microsoft.com/fwlink/?LinkID=233566

  • SQL Server Audit Action Groups and Actions

– http://go.microsoft.com/fwlink/?LinkID=233440

  • SQL Server Audit (Database Engine)

– http://go.microsoft.com/fwlink/?LinkID=233568

slide-49
SLIDE 49

SQL Server 2012 Certifications

  • Microsoft Certified Solutions Associate (MCSA) for SQL Server

2012

  • Microsoft Certified Solutions Expert: Data Platform
  • Microsoft Certified Solutions Expert: Business Intelligence
slide-50
SLIDE 50

Summary

  • 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
slide-51
SLIDE 51

MCSA SA SQ SQL Se Server er 2012

  • MCSA: SQL Server

er 2012

  • 10774: Querying Microsoft SQL Server 2012
  • 10775: Administering Microsoft SQL Server 2012 Databases
  • 10777: Implementing a Data Warehouse with Microsoft SQL Server 2012

Querying Microsoft SQL Server 2012 Exam 70-461 (10774) Administering Microsoft SQL Server 2012 Databases Exam 70-462 (10775)

MCSA: SQL Server 2012

Implementing a Data Warehouse with Microsoft SQL Server 2012 Exam 70-463 (10777)

www.netcomlearning.com

Course Outline for MCSA: SQL Server 2012

slide-52
SLIDE 52

MCSE SE Data ta Platfo tform rm

www.netcomlearning.com

Course Outline for MCSE: Data Platform

slide-53
SLIDE 53

MCSE SE Bu Busines ness s Intelligence igence

Course Outline for MCSE: Business Intelligence

slide-54
SLIDE 54

Q Q & A A

1-888-5-NETCOM (563-8266) info@netcomlearning.com www.NetComLearning.com