1 / 22

Building a DDL Audit Solution using SQL Server 2005

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

sherlock_clovis
Download Presentation

Building a DDL Audit Solution using 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

    Slide 1:Building a DDL Audit Solution using SQL Server 2005 Jasper Smith, MVP

    Slide 2:Agenda DDL Audit Mechanisms Default Trace DDL Triggers Event Notifications Event Notifications in Detail Setting up Remote Event Notifications Putting it all together DDL Audit Admin DDL Audit Reports DDL Audit Viewer

    Slide 3:Why ? Simple Audit – who did what and when Change management – database schema history Change auditing – do effects of change match change description Troubleshooting – performance issues can occur when schema or stored procedures are changed

    Slide 4:DDL Audit Mechanisms – 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 The system function fn_trace_gettable has been enhanced to allow the reading of running server side traces SMO Trace API Microsoft.SqlServer.Management.Trace

    Slide 5:DDL Audit Mechanisms – DDL Triggers React to CREATE,ALTER,DROP DDL statements Server or Database scoped Within a DDL trigger you can rollback the DDL that caused it to fire Use the eventdata() function to return data as xml Synchronous

    Slide 6:DDL Audit Mechanisms – Event Notifications Leverage Service Broker infrastructure Asynchronous Target local or remote service React to DDL and subset of Trace events Integrate with WMI and SQL Agent alerts

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

    Slide 8: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' }

    Slide 9: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)

    Slide 10: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

    Slide 11: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

    Slide 12:Demonstration 1 Event Notification Basics

    Slide 13:Remote Event Notifications – Endpoint In order for Service Broker messages to pass between instances we need to create a Service Broker Endpoint Define the TCP port and encryption the endpoint will support and who can connect to it

    Slide 14:Remote Event Notifications – Routes Need to specify Remote Service Name Remote Broker Instance Remote Address CREATE ROUTE ExpenseRoute WITH SERVICE_NAME = '//Adventure-Works.com/Expenses', BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89C1E315', ADDRESS = 'TCP://SERVER02:1234'

    Slide 15:Remote Event Notifications - Security Can configure full dialog security as described in BOL however it is overkill for DDL Audit and hard to automate (requires certificate management) We will use Windows Authentication based on the SQL Server Service account with no encryption Grant connect on the Service Broker endpoints to the SQL Service accounts (local and remote) to allow communication NOTE guest user must have access to msdb (all object permissions can be denied)

    Slide 16:Remote Event Notifications – DMV sys.transmission_queue in msdb holds outbound messages from audited instance. Very useful for troubleshooting sys.conversation_endpoints describes service broker conversations and their states sys.server_event_notifications and sys.server_events hold metadata about event notifications sys.services and sys.routes hold service broker metadata

    Slide 17:Demonstration 2 Remote Event Notifications

    Slide 18:DDL Audit Application Requirements Need a central data warehouse to store events Need a deployment mechanism Need an interface for viewing audit events Need to monitor event queues and health* Need to be able to alert on specific events* Need to be able to filter known common DDL* e.g. index drops/creates during ETL processes

    Slide 19:DDL Audit Application Technologies SQL Server 2005 Event Notifications Reporting Services 2005 Windows Forms 2.0 Windows Forms Report Controls

    Slide 20:Demonstration 3 Putting it all Together

    Slide 21:Additional Resources More on Auditing DDL in SQL 2005 Auditing Events in SQL Server 2005 Slides and Demo Applications on PASS site and also http://www.sqldbatips.com Further questions jas@sqldbatips.com

    Slide 22:Thank you! Thank you for attending this session and the 2006 PASS Community Summit in Seattle

More Related