Downloaded 23 times
Uploaded byAntonios Chatzipavlis
Auditing Data Access in SQL Server
This document provides an overview of auditing data access in SQL Server. It discusses various methods for auditing such as using common criteria, SQL Trace, DML triggers, temporal tables, and implementing SQL Server Audit. SQL Server Audit is described as the primary auditing tool in SQL Server that can track both server and database level events. Considerations for implementing and managing SQL Server Audit are also covered.
More Related Content
Similar to Auditing Data Access in SQL Server
Auditing Data Access in SQL Server
- 3. Auditing Data Access inSQL Server 29th SQL Night - Oct 1st ,2016
- 4. PresenterInfo 1982 I startedworking with computers 1988 I started my professional career in computers industry. 1996 I started working with SQL Server 6.0 1998 I earned my first certification at Microsoft as Microsoft Certified Solution Developer (3rd in Greece) I started my career as Microsoft Certified Trainer (MCT) with more than 25.000 hours of training until now! 2010 I became for first time Microsoft MVP on SQL Server I created the SQL School Greece www.sqlschool.gr 2012 I became MCT Regional Lead by Microsoft Learning Program. 2013 I was certified as MCSE : Data Platform & Business Intelligence Antonios Chatzipavlis Database Architect, SQL Server Evangelist MCT, MCSE, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA, ITIL-F
- 5.
- 6.
- 7.
- 8.
- 9. StayInvolved Sign upfor a free membership today at sqlpass.org. Linked In: http://www.sqlpass.org/linkedin Facebook: http://www.sqlpass.org/facebook Twitter: @SQLPASS PASS: http://www.sqlpass.org
- 10. Presentation Content AuditingData Access, why? Use Common Criteria for auditing Use SQL Trace for auditing Use DML Triggers for auditing Use Temporal tables for auditing Implementing SQL Server Audit Managing SQL Server Audit
- 11.
- 12. Why is auditingrequired? What methods have you used for auditing? What are the limitations of the methods you have used? Which standards that require auditing does your organization need to comply with? Audit Data Access, why? Questions
- 13.
- 14. Ratified as aninternational standard by more than 20 nation in 1999. Supersedes C2 rating ISO standard 15408 Common Criteria Auditing Common Criteria Compliance
- 15. Common Criteria complianceenabled option Available in Enterprise Edition Additional script must be run to comply with Common Criteria Evaluation Assurance Level 4+ (EAL4+) Use Common Criteria for auditing SQL Server and Common Criteria Compliance
- 16.
- 17. Is used totrace commands sent to the server Can be heavy on resources Is run interactively Can trace commands executions Marked for deprecation in the future version of SQL Server Use SQL Trace for auditing SQL Server Profiler
- 18. Event-driven monitoring tool Configuredthrough system stored procedures Can be configured to capture user activity Marked for deprecation in the future version of SQL Server Use SQL Trace for auditing SQL Trace
- 19.
- 20. DML triggers fordata modification Logon triggers for tracking logons DDL triggers for schema modification Use DML Triggers for auditing Triggers can provide part of an auditing solution
- 21. Performance impact Ability todisable triggers Lack of SELECT triggers Trigger nesting issues Complexities around trigger firing orders Use DML Triggers for auditing Limitations
- 22.
- 23. The Database Engineautomatically records the valid from/to dates of records in the database as they are changed Configured as part of the table definition; no additional code required Is SQL Server 2016 feature Use Temporal tables for auditing Auditing with Temporal Tables
- 24. Cannot audit SELECTstatements INSERT, UPDATE and DELETE statements all audited in the same way History table will be in the same database User tracking requires adding a column to the table to hold SUSER_SNAME Use Temporal tables for auditing Limitations
- 25.
- 26. SQL Server Auditis the primary auditing tool in SQL Server Track server and database level events on an SQL Server instance All editions of SQL Server supports server level auditing Database level auditing is available on Enterprise, Developer and Evaluation edition Based on an event-driven monitoring engine called Extended Events Implementing SQL Server Audit Overview
- 27. Server Audit Server AuditSpecification Database Audit Specification Actions Action Groups Target Implementing SQL Server Audit Terminology
- 28. Defines where andhow audited events are logged Each server audit specify: Target Queue delay Action on failure Server Audit Definition
- 29. Server level Database Level Auditlevel http://aka.ms/bak8rw Audit Actions and Action Groups Actions and action groups are linked to an audit with an audit specification
- 30. Audit Action groups tobe included State Server Audit Specifications Specify
- 31. Audit Action Group Actions onspecific securable object Can be filtered by specific database principals State Database Audit Specifications Specify
- 32.
- 33.
- 34. Only database eventsmay be audited Plain SQL Parameterized SQL Stored Procedures Login Transaction Management Audit logs are written to Store Tables Auditing in Azure SQL Database Configured through Azure Portal or Azure Powershell
- 35. They cannot beused to audit business logic Add USER_DEFINED_AUDIT_GROUP to audit specification Call sp_audit_write strored procedure from T-SQL code EXEC sp_audit_write @user_defined_event_id = 12, @succeeded = 1, @user_defined_information = N'User defined message'; Custom Audit Events Allows you to create custom audit entries
- 36.
- 37. Windows Event LogTarget Event Viewer Binary File Target sys.fn_get_audit_file Retrieving Audit Data The method to retrieve audit data depends on the target that specified in audit definition
- 38. This function takesthree parameters: the file pattern, <path>* <path><audit name>_{GUID} <path><file name> the initial file name, and the audit record offset SELECT * FROM sys.fn_get_audit_file('X:AuditFiles*',default,default); Binary File Targets Audit files created by SQL Server Audit can be opened with the sys.fn_get_audit_file system table-valued function
- 39. Because of thisrequirement, the record format is limited in size by the rules related to Windows event logging systems. Character fields will be split into 4,000- character chunks that may be spread across a number of entries. This means that a single event can generate multiple audit entries and a sequence_number column is provided to indicate the order of multiple row entries. Large Audit Records The audit records produced by SQL Server Audit must be formatted to fit in system event logs, and in files
- 40. You must disableaudits and audit specifications before you drop them, or make any other changes to configuration. Enabling and Disabling Auditing Change the STATE property to ON or OFF
- 41. Audit GUID inrestore scenarios Audit GUID in mirroring scenarios Performance impact of audit writes If audit configuration prevents the instances from starting, use the –f switch If a database is restored to an instance that does not support database audits, the audit is ignored Considerations for SQL Server Audit There are several potential issues to consider with SQL Server audit
- 42.
- 45. SELECT KNOWLEDGE FROMSQL SERVER Copyright © 2016 SQLschool.gr. All right reserved. PRESENTER MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION