Auditing in Microsoft SQL Server 2012

1. DBI407 Auditing in MicrosoftSQL Server 2012 Il-Sung Lee Program Manager Microsoft Corporation

2. Agenda • What’s changed since SQL Server 2008? • What is the performance impact? • Can I protect the Audit log from the DBA? • What happens if Audit fails to write? • What do I do if the server fails to start because of SQL Server Audit? • Anything else I should know?

3. What’s changed sinceSQL Server 2008?

4. Lots. We’ve made SQL Server Audit more flexible and reliable.

5. SQL Server Audit Enhancements

6. Audit Supported on All SKUs • Basic Audit on all SKUs • Server Audit Specs only • DB Audit Specs for Enterprise • No longer need SQLTrace • Enjoy advantages of Audit • Performance • Multiple Audits and multiple targets • Persist state • Audit Resilience SQL ServerExpress

7. Improved Resilience • Before: • Write failures may silently lose Audit records • Use ON_FAILURE = SHUTDOWN • Now: • Automatically recover from most file or network errors • Added “ON_FAILURE = FAIL_OPERATION” • Added “MAX_FILES” option Select… Rollback

8. T-SQL Stack Information select salary from hr.payroll exec hr.viewsalary hr.viewsalary hr.payroll Audit Log

9. demo T-SQL Stack Information

10. User-Defined Audit Event • sp_audit_write() exec sp_audit_write 1234, 1, N‘Hello World’ @user_defined_event_id @succeeded @user_defined_info Audit Log

11. demo User-Defined Audit Event

12. Record Filtering • Tightly constrain info written to Audit log • Audit record generated but not written • Leverages Xevent filtering CREATE SERVER AUDIT audit_name TO { [ FILE (<file_options> [ , ...n ]) ] | APPLICATION_LOG | SECURITY_LOG } [ WITH ( <audit_options> [ , ...n ] ) ] [ FILTER = <predicate_expression> ] } … <predicate_expression> ::= {    [ NOT ] <predicate_factor> | {( <predicate_expression> ) }     [ { AND | OR } [ NOT ] { <predicate_factor> | ( <predicate_expression> ) } ]     [ ,...n ] }

13. demo Record Filtering

14. What is the performance impact?

15. Depends…

16. Audit Performance • Depends upon: • The workload • What’s being audited • Comparison of SQL Server Audit against SQL Trace for 5 different typical customer workloads…

17. SQL Server Audit vs SQL Trace

18. Can I protect the Audit log from the DBA?

19. Yes.

20. Protecting Audit Data

21. What happens if Audit fails to write?

22. Depends again…

23. Audit Write Failure (Shutdown) • Server shuts down • Buffered audit events lost

24. Audit Write Failure (Continue)

25. Audit Write Failure (Fail Operation)

26. What do I do if the server fails to start because of SQL Server Audit?

27. Start the server in single-user mode

28. Starting the Server

29. demo Using SQL Server Audit with Policy-Based Management

30. Anything else I should know?

31. Just a few things.

32. Other Things You Should Know • Parameterized queries • Audit Xevent Sessions may not be manipulated by Xevent DDL. • Audit logs are not encrypted or compressed • Audit events are fired with permission checks • Writing to files are much faster than to event log • No auditing of result sets

33. Other Things You Should Know • Both Audit and Audit Specifications have STATE parameters. • Can only change state outside user transaction. • All other audit changes can be done in a transaction, but with Audit or Audit Specification OFF.

34. Securely and Easily Track DB Activity • Consider SQL Server Audit for all security auditing requirements and leverage the 2012 enhancements • Carefully devise a strategy for what needs to be audited and where to send the audit information based on security and performance needs • Monitor administrator activity and prevent tampering of the logs.

35. Session Resources • Books Online: • Security Enhancements (Database Engine), http://msdn.microsoft.com/en-us/library/cc645578(v=sql.110).aspx • SQL Server Audit (Database Engine), http://msdn.microsoft.com/en-us/library/cc280386(v=SQL.110).aspx • Whitepaper: • Auditing in SQL Server 2008, http://msdn.microsoft.com/en-us/library/dd392015(v=SQL.100).aspx • SQL Server Security Forum: • http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/threads/ • SQL Security Blog: • http://blogs.msdn.com/b/sqlsecurity/

36. Related Content Bare Metal Microsoft SQL Server 2012 Deployment and Management (S. Hall B WRK Rm1) Microsoft SQL Server: Mission Critical Confidence - Organizational Security and Compliance Demo Station (S. Hall A) Find Me Later At The Mission Critical Booth In The Expo

37. http://blogs.msdn.com/b/sqlsecurity/ ilsung@microsoft.com I’m not a tweeter Il-Sung Lee

38. Track Resources Hands-On Labs @sqlserver @ms_teched SQL Server 2012 Eval Copy Get Certified! mva Microsoft Virtual Academy

39. Resources Learning TechNet • Connect. Share. Discuss. • Microsoft Certification & Training Resources http://northamerica.msteched.com www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet http://microsoft.com/msdn

40. Required Slide Complete an evaluation on CommNet and enter to win!

41. Please Complete an Evaluation Your feedback is important! Multipleways to Evaluate Sessions Be eligible to win great daily prizes and the grand prize of a $5,000 Travel Voucher! Scan the Tag to evaluate this session now on myTechEdMobile

42. © 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.