1 / 21

Building a DDL Audit Solution using SQL Server 2005

DBA-402S? Building a DDL Audit Solution using SQL Server 2005. Agenda. DDL Audit MechanismsDefault TraceDDL TriggersEvent NotificationsEvent Notifications in DetailSetting up Remote Event NotificationsPutting it all togetherDDL Audit AdminDDL Audit ReportsDDL Audit Viewer. DBA-402S? Buildi

ellie
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

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

    2. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    3. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    4. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    5. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    6. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    7. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 Service Broker Service Broker provides queuing and reliable messaging as part of the Database Engine Event Notification Service is built in to all databases

    8. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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' }

    9. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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)

    10. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    11. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    12. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 Demonstration 1 Event Notification Basics

    13. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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 CREATE ENDPOINT SSBENDPOINT STATE = STARTED, AS TCP (LISTENER PORT = 4022) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS, ENCRYPTION = SUPPORTED)

    14. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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'

    15. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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)

    16. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    17. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 Demonstration 2 Remote Event Notifications

    18. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    19. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 DDL Audit Application Technologies SQL Server 2005 Event Notifications Reporting Services 2005 Windows Forms 2.0 Windows Forms Report Controls

    20. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 Demonstration 3 Putting it all Together

    21. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 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

    22. DBA-402S• Building a DDL Audit Solution using SQL Server 2005 Thank you! Thank you for attending this session and the 2006 PASS Community Summit in Seattle

More Related