SQL 2005 DDL Audit Sample Applications (1.0.0.0)
© sqldbatips.com, 2006.
All rights reserved.
Contents
1.0 Introduction
2.0 DDL Audit Sample Installation
2.1 DDL Audit Sample Requirements
2.2 DDL Audit Sample Tested Environments
3.0 Using DDL Audit Admin
3.1 Creating the Central Audit Database
3.2 Deploying Auditing to Additional Instances
3.3 Deploying Audit Reports
3.4 Central Audit Database Archiving
4.0 Using DDL Audit Viewer
4.1 Connecting to an Audit Server
4.2 Managing Audit Servers List
4.2 DDL Audit Viewer Dependencies
5.0 Uninstalling DDL Auditing
6.0 Support and Feeback
7.0 Useful Links
8.0 Release History
1.0 Introduction
The SQL 2005 DDL Audit Sample is a collection of sample applications for configuring DDL (Data Definition Language) auditing on one or more SQL Server 2005 instances. These applications were first presented at the PASS 2006 Community Summit in my presentation Building a DDL Audit Solution using SQL Server 2005. They build on top of the Service Broker and Server Event Notification infrastructure available in SQL Server 2005 to provide a means of collecting all server and database DDL events in a central database for viewing and reporting. Full source code is provided for all sample applications to allow you to better understand the technologies and easily build on or modify these samples for use in your environment.
Key Components
- DDL Audit Admin application - used to configure central audit database and deploy auditing to additional instances
- DDL Audit Viewer application - used to view audited events across all monitored instances
- DDL Audit Reports - a selection of sample reports used in conjunction with DDL Audit Viewer
- Full source code for all applications and reports
2.0 DDL Audit Sample Installation
To install the SQL 2005 DDL Audit Sample simply unzip DDLAuditSample.zip to a folder on your local PC and run the enclosed setup application (DDL Audit Install.msi).
Note: This does NOT make any configuration changes to any SQL instances, it merely copies all the applications and source code to the following folder (by default) C:\Program Files\sqldbatips\DDL Audit Sample
The following folders will be created
- DDL Audit Admin - this contains the DDL Audit Admin application used to configure DDL Auditing
- DDL Audit Admin Source - this contains the source code for the DDL Audit application
- DDL Audit Reports - this contains the SQL 2005 Reporting Services Sample Audit Reports
- DDL Audit Viewer - this contains the DDL Audit Viewer application used for viewing audited events
- DDL Audit Viewer Source - this contains the source code for the DDL Audit Viewer application
2.1 DDL Audit Solution Requirements
In order to run the DDL Audit Sample applications you need to have the .NET Framework 2.0 and SQL Server Management Tools installed. To modify the source code you need to have a version of Visual Studio 2005 installed (other than SQL Server Business Intelligence Development Studio). Express versions of Visual Studio 2005 can be downloaded for free from here (Source code is C#).
2.2 DDL Audit Sample Tested Environments
SQL 2005 DDL Audit Sample applicationshas been tested with the following operating systems and SQL Server versions
- Windows XP Professional SP2
- Windows Server 2003 RTM/SP1/R2
- SQL Server 2005 RTM/SP1
- SQL Server 2005 Reporting Services RTM/SP1
SQL 2005 DDL Audit Sample applications are not localized and have only been tested against English versions of Operating Systems and SQL Server 2005.
You can launch DDL Audit Admin from the Start menu (Start>Program Files>sqldbatips.com>DDL Audit Sample>DDL Audit Admin) or by running C:\Program Files\sqldbatips\DDL Audit Sample\DDL Audit Admin\DDLAuditAdmin.exe.
Note: The user running the installation must have sysadmin rights on every SQL Server instance where auditing is to be deployed and local admin rights (or at least the right to enumerate services and service accounts) in Windows in order to be able to detect the SQL Server service account. All SQL Server instances to be audited must run under a domain user account unless auditing is being deployed to just one server (e.g. local development instance) in which case a local user account is sufficient.
3.1 Creating the Central Audit Database
In order to be able to collect DDL audit events from SQL 2005 instances the application creates a central audit database (DDLAuditDW).
- The first step is to supply a SQL 2005 instance name in the Central Audit Server textbox and click the Connect button. Note that this will simply connect to the instance and check a few prerequisites, nothing is installed at this stage.
- To create the audit database and deploy the audit infrastructure to the central server click on the Install Central Audit Database button.
- You will then be prompted to supply the path to a SQL 2005 Reporting Services instance. Don't worry if you don't have a Report Server instance, this information is only used by the DDL Audit Viewer application and does not affect the collection of DDL Audit Events. If you don't have a Report Server simply accept the default and click on the Install button. This will create the DDLAuditDW database.
- DDL Auditing is now installed on the central server. You may want to increase the size of the DDLAuditDW database as it is created at the default size.
3.2 Deploying Auditing to Additional Instances
In order to deploy DDL auditing to additional instances, switch to the Audited Servers tab of the application
- To deploy DDL auditing to an additional instance, right click on the Audited Servers treeview on the left of the tab and select Add Instance. Supply a SQL Server 2005 instance name and click the Add button. You should see that the new instance has been added to the Audited Servers treeview which indicates a successful installation.
- To deploy DDL auditing to multiple additional instances at the same time, create a text file (e.g. servers.txt) containing a SQL Server 2005 instance name on each line. Click on the Install from File button which will prompt for a text file. Select the file you created contating the instance names. A messagebox will list the instances on which auditing will be installed. If you are happy with the selection click Yes to proceed with the installation. The new audited instances will be added to the Audited Servers treeview which indicates a successful installation.
- This deployment will create a Service Broker Endpoint (if it doesn't exist), a server level Event Notification and Service Broker route (in msdb) on the target instance. It will also create a return Service Broker route in the central DDLAuditDW database to allow acknowledgment of messages from the remote instance. The SQL service accounts of the audited instance and the instance hosting the central audit database are granted login rights and connect permissions on the corresponding Service Broker endpoints. Note that the guest user must have access to the msdb database in order for server level event notifications to work and so it has been dropped it will be granted access to msdb however all object permissions will be denied. If the guest user already has acess to msdb (which is the default in SQL Server 2005) then no changes are made.
3.3 Deploying Audit Reports
To deploy the sample audit reports which are used by the DDL Audit Viewer application open the Report project (either Start>Program Files>sqldbatips.com>DDL Audit Sample>DDL Audit Reports or C:\Program Files\sqldbatips\DDL Audit Sample\DDL Audit Reports\DDL Audit Reports.sln)
- Select Project>Properties from the Visual Studio toolbar and check that the TargetServerURL reflects the SQL 2005 Reporting Services instance you specified when creating the central audit database.
- Right click on the AuditDDLDW.rds shared data source in Solution Explorer and select Open. Modify the Data Source parameter of the connection string to reflect the instance which hosts the central audit database.
- Right click on DDL Audit Reports in Solution Explorer and select Deploy to deploy the reports to your Report Server.
- In order for the reports to be able to access the DDLAuditDW database you can create a new login (or use an existing one) on the central server and add it to the DBAuditReaders database role in the DDLAuditDW database. This will give it execute permissions on the reporting stored procedures. Once the login is configured, use Report Manager or SQL Server Management Studio to modify the AuditDDLDW shared datasource in the DDL Audit Reports folder on the Report Server to use the login (Credentials stored securely on the Report Server)
3.4 Central Audit Database Archiving
The installation of the central audit database (DDLAuditDW) creates a SQL Agent job called DDLAuditDW Archive Job which runs the stored procedure dbo.DoArchive. This process can be configured by modifying the following Name/Value pairs in the dbo.AuditConfig table in the DDLAuditDW database.
Name
|
Values
|
Description
|
RetentionType
|
DELETE or ARCHIVE
|
The default value is DELETE which means that all events with an EventDate less than the RetentionMonths value are deleted from the AuditDDL table. If set to ARCHIVE then events with an EventDate less than the RetentionMonths value are moved to the AuditDDLArchive table |
RetentionMonths |
3 |
This value is how long (in months) events are to be kept in the AuditDDL table before being deleted or archived |
You can launch DDL Audit Viewer from the Start menu (Start>Program Files>sqldbatips.com>DDL Audit Sample>DDL Audit Viewer) or by running C:\Program Files\sqldbatips\DDL Audit Sample\DDL Audit Viewer\DDLAuditViewer.exe.
In order to connect to a central audit server in order to view DDL events
- Select File>Connect to Audit Server>New. This will display the Manage Servers dialog.
- Type in the name of the central audit instance you want to connect to and click Apply. This instance must host the DDLAuditDW database created by the DDL Audit Admin application
- Select File>Connect to Audit Server>Your instance (The Manage Servers dialog simply adds instances to the connection menu and persists them. This only has to be done once)
- You will now be presented with a Treview list of audited instances and a drillthrough report allowing you to view audit details. You can select an instance either in the Treeview or via the report.
- Note that if your Report Server is not "warmed up" it can take a little while for the summary report to render.
- On the Events by Database or Events by EventType reports you can click on the View Details icon (on the far left of the table next to the Event Date) to see the details and TSQL (Note that due to a bug in the Winforms ReportViewer control, tooltips don't show up)
The Manage Servers dialog (File>Manage Servers) allows you to specify one or more central audit servers to connect to (e.g. DEV/QA/PROD)
- Type in the name of one or more central audit instances you want to connect to and click Apply
- These instances will be persisted per user so only have to be added once
- These instances are then available via the Connect to Audit Server menu (File>Connect to Audit Server)
The DDL Audit Viewer application has a number of dependencies on Name/Value pairs in the dbo.AuditConfig table in the DDLAuditDW database on the central audit server
Name
|
Default Value
|
Description
|
AuditSummaryReport
|
All Audit Summary
|
This is the name of the initial report displayed in DDL Audit Viewer when connecting to a central audit server |
InstanceSummaryReport |
Instance Summary |
This is the name of the instance summary report displayed in DDL Audit Viewer when you select a specific instance. It takes an instance name as a report parameter |
ReportPath |
/DDL Audit Reports/ |
This is the full path to the audit reports used by DDL Audit Viewer. It should start and end with a forward slash |
ReportServer |
http://localhost/ReportServer |
The url of the Reporting Services ReportService web service. This is supplied during deployment of the central audit database by the DDL Audit Admin application |
To remove DDL Auditing from an instance (excluding the central server) use the following script
-
C:\Program Files\sqldbatips\DDL Audit Sample\DDL Audit Admin Source\SQL\CleanupRemote.sql
This script will remove the event notification and route on an audited instance and optionally drop the Service Broker endpoint.
To remove DDL Auditing from the central server (make sure you have uninstalled it from all other instances first) use the following script
-
C:\Program Files\sqldbatips\DDL Audit Sample\DDL Audit Admin Source\SQL\CleanupRemote.sql
This script will remove the event notification, routes, archive job and central database on the central server and optionally drop the Service Broker endpoint.
If you have any questions or feedback about the SQL 2005 DDL Audit Sample applications use the link below
Date
|
Build
|
Description
|
10/12/2006
|
1.0.0.0
|
Initial public release |
THIS APPLICATION AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE