1 / 32

Auditing Events in SQL Server 2005

Agenda. Trace EnhancementsDDL TriggersEvent Notifications. Trace Enhancements. Default Trace. On by default (controlled by sp_configure 'default trace enabled?)Stored in the same folder as the SQL Server Error Log Location can be changed by modifying the ?e start up parameter using the SQL Serv

julinka
Download Presentation

Auditing Events in SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Auditing Events in SQL Server 2005 Jasper Smith SQL Server MVP

    2. Agenda Trace Enhancements DDL Triggers Event Notifications

    3. Trace Enhancements

    4. Default Trace On by default (controlled by sp_configure 'default trace enabled‘) Stored in the same folder as the SQL Server Error Log Location can be changed by modifying the –e start up parameter using the SQL Server Configuration Manager Captures mainly audit type trace events

    5. Reading a running trace The system function fn_trace_gettable has been enhanced to allow the reading of running server side traces e.g. select * from :: fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL.1\ MSSQL\LOG\log_15.trc',-1) order by StartTime desc

    6. SMO Trace API In the Microsoft.SqlServer.Management.Trace namespace Can read and write Trace Files and Trace Tables Trace sources include SQL Server, Analysis Services and SSIS log tables. Can be used to manipulate trace data programmatically

    7. Trace Demo

    8. DDL Triggers

    9. SQL 2005 Trigger Types Instead Of Triggers (DML) After Triggers (DML) DDL Triggers React to DDL CREATE , ALTER , DROP

    10. DDL Trigger Syntax CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option>] { FOR | AFTER } { event_type | event_group } AS { sql_statement [ ...n ] | EXTERNAL NAME < method specifier > }

    11. DDL Trigger Scope DATABASE Applies the scope of a DDL trigger to the current database. If specified, the trigger fires whenever event_type or event_group happens in the current database ALL SERVER Applies the scope of a DDL trigger to the current server. If specified, the trigger fires whenever the server level event_type or event_group happens anywhere in the current server

    12. Event Groups DDL_DATABASE_LEVEL_EVENTS DDL_TABLE_VIEW_EVENTS DDL_TABLE_EVENTS (CREATE_TABLE,ALTER_TABLE,DROP_TABLE) DDL_VIEW_EVENTS (CREATE_VIEW,ALTER_VIEW,DROP_VIEW) DDL_INDEX_EVENTS (CREATE_INDEX,ALTER_INDEX,DROP_INDEX) DDL_STATISTICS_EVENTS (CREATE_STATISTICS,ALTER_STATISTICS,DROP_STATISTICS)

    13. eventdata() Function eventdata() returns a value of type xml The base XML schema returned by the eventdata() function depends on the scope and event type

    14. eventdata() output <EVENT_INSTANCE> <PostTime>2004-05-26T21:10:36.393</PostTime> <SPID>55</SPID> <EventType>CREATE_TABLE</EventType> <ServerName>WIN2003</ServerName> <LoginName>foo</LoginName> <UserName>foo</UserName> <DatabaseName>test</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>table1</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" AN...../> <CommandText>create table foo(bar int)</CommandText> </TSQLCommand> </EVENT_INSTANCE>

    15. Rollbacks and DDL Triggers If a DDL action is rolled back, so is the logging of the event in an audit table BEGIN TRAN CREATE TABLE TEST(a int) ROLLBACK TRAN Within a DDL trigger you can rollback the DDL that caused it to fire CREATE TRIGGER DDLDEMO_ROLBACK ON DATABASE FOR CREATE_TABLE AS PRINT 'Create Table is not allowed‘ ROLLBACK

    16. DDL Trigger Demo

    17. Event Notifications

    18. Event Notifications Objects that sends messages about a database or server event to a service broker service To create an event notification, you must complete the following steps: Create a target service to receive event notifications. Create the event notification.

    19. Service Broker Service Broker provides queuing and reliable messaging as part of the Database Engine Event Notification Service is built in to all databases

    20. Event Notification Syntax CREATE EVENT NOTIFICATION name ON { SERVER | DATABASE | QUEUE } [ WITH FAN_IN ] FOR { event_type | event_group } [ ,...n ] TO SERVICE broker_service { 'broker_instance_specifier' | 'current database' }

    21. Trace Events In addition to the DDL events available in DDL Triggers, Event Notifications also allow a subset of Trace events to be captured Audit_Login Audit_Login_Failed Lock_Deadlock Data_File_Auto_Grow Blocked_Process_Report

    22. Creating Event Notifications Create a QUEUE Create a SERVICE on a QUEUE Create a ROUTE for the SERVICE Create an EVENT NOTIFICATION to a SERVICE Create a SERVICE PROGRAM to process notification events in the QUEUE

    23. Event Notifications Demo

    24. WMI Integration WMI Provider for Server Events (SQLWEP) SQL 2005 is a managed WMI object Consume events based on an Event Notification Query WMI Query Language (WQL) – simplified form of SQL with WMI specific extensions Easily accessible via the System.Management namespace in the .NET Framework

    25. WQL Examples SELECT * FROM DDL_DATABASE_LEVEL_EVENTS WHERE DatabaseName = 'AdventureWorks‘ SELECT * FROM ALTER_TABLE WHERE DatabaseName = "AdventureWorks" AND SchemaName = "Sales" AND ObjectType="Table" AND ObjectName = "SalesOrderDetail" SELECT * FROM DEADLOCK_GRAPH

    26. SQL Agent alerts can react to WMI events NOT limited to SQL Server Events

    27. WMI Events Demo

    28. Server Trace Summary Lowest overhead If using standard trace files there can be an increased administration overhead Need to recreate trace after server restart Limited notification ability

    29. DDL Triggers Summary DDL Triggers are synchronous DDL Triggers are tightly coupled to the event that caused them to fire DDL Triggers can respond only to DDL events DDL Triggers can issue a rollback

    30. Event Notifications Summary Event Notifications are asynchronous Target local or remote service React to DDL and subset of Trace events Integrate with WMI and SQL Agent alerts Some additional processing overhead

    31. Questions ? Slides will be available soon on sqlpass.org and sqldbatips.com Email jas@sqldbatips.com

    32. Thank You! Thank you for attending this session and the 2005 PASS Community Summit in Grapevine! Please help us improve the quality of our conference by completing your session evaluation form. Completed evaluation forms may be given to the room monitor as you exit or to staff at the registration desk.

More Related