Inside Microsoft: Building a Database Structure Auditing Solution with DDL Triggers in SQL Server 2005

Building a Database Structure Auditing Solution with DDL Triggers in SQL Server 2005

Mar 1, 11:00 pm

Article Author: Brian Myers

Introduction

Have you ever made a series of changes to a database structure during development, testing or even in the production environment and then much later realized one of these changes has caused a problem? This scenario is made even more complicated during development and testing when any one of many developers could have made the problematic change. Worse – Enterprise Manager in SQL Server 2000 and now SQL Server Management Studio in SQL Server 2005 allow developers or DBAs to make structural changes using the GUI instead of by writing scripts. If you have ever found yourself in this situation you know that it can take some time to determine what change caused an application issue and when that change was implemented.

A new feature in SQL Server 2005 called DDL Triggers will help solve this problem. DDL Triggers are triggers that are fired when various DDL actions such as adding a table, dropping a table, changing a column, or deleting a column occur. The DDL triggers do not fire for events that affect local or global temporary tables or stored procedures. With this new feature you can audit most changes to a database. (Note however that there are some DDL events that can not have DDL Triggers defined for them. Those are events that are intended to be non transactional and asynchronous).

This article will show you how to deploy a solution that uses a central administrative database to hold the auditing results of all other databases on all other database servers. The solution begins by creating the database structure that will hold the auditing information and then creating the necessary DDL Triggers to gather the information. Finally this solution will include a report suitable for SQL Server Report Server, which will allow the user to enter a server name to view a list of all changes to all databases on that server.

Solution Requirements

You will need the following to setup an auditing solution similar to the one described in this article:

Microsoft SQL Server 2005

Microsoft .NET Framework version 2.0

Microsoft SQL Server Reporting Server installed

SQL Server Business Intelligence Development Studio

Microsoft Internet Information Server

Installing and Compiling the Sample Code

Unzip the downloaded file and place within a folder. Copy the Trigger_Auditing_Reports folder to your local hard drive. Copy the Scripts folder to a location that you want to pull the SQL scripts from. You can find the solution and project files to create the report in the Trigger_Auditing_Reports folder. You will need to open this solution in the SQL Server Business Intelligence Development Studio to make any changes or to add new reports.

Database Structure Auditing Solution

The basic architecture for the Database Structure Auditing Solution is this: A DDL trigger is created on the server to gather all changes that are made at the server level, and a further DDL trigger is created on each database to capture changes made at the database level. A database for holding the auditing information is created on a database server. Finally, a linked server may be necessary to connect the database server with the triggers to the auditing database server.

A linked server is a connection between two SQL Server database servers. This allows you to reference databases and tables that exist on one SQL Server from another. This will give you an enterprise solution by allowing all database servers to feed their auditing data to one database server, making a data repository for the auditing data.

Database Design

This solution starts out with a database called Admin. This database will contain the table that is necessary to hold the audited data. Within that database you should create a table called tblAuditData using the script CreateTblAuditData.sql that is provided with this article.

Creating the DDL Triggers

The first thing that must be decided is what actions need to be audited. There are two different scopes of DDL triggers – these scopes define the actions that will cause the trigger to be fired.

Server scope, these are actions taken at the server level and the DDL triggers are created at the server level in the master database. Some examples of these actions are alter_database, drop_database, alter_login, and drop_login.

Database scope, these are actions taken at the database level and the DDL triggers are created within the database. Some examples of these actions are alter_table, drop_table, and create_statististics. For a list of all of the possible actions for each scope search for "DDL Events" + "DDL Triggers" in the MSDN books on line search and find the topic "DDL Events for Use with DDL Triggers".

For this article I am showing the triggers being created within the AdventureWorks database. However, the scripts can be run in any database or on any SQL Server 2005 server. There are a few additional steps necessary to make this an enterprise solution, to gather data from all database servers into one server. This will be shown later in the article.

Triggers can be created generically to include all possible database events, and that is the approach I will take in this article. The solution will audit all changes made to the database (or at least all those that can be captured) and place that information into the audit table. If necessary, each individual action can have a DDL trigger created for it, but this will take much more time to set up. Therefore, this solution includes the trigger Database_Level_Logging. The script, Database_Level_Logging.sql, (downloadable with this article) is as follows – note that for ease of reference I’ve added line numbers to this and other code quoted in this article – obviously, these line numbers are not present in the actual source code:

1 CREATE TRIGGER DATABASE_LEVEL_LOGGING
2 ON DATABASE
3 FOR DDL_DATABASE_LEVEL_EVENTS
4 AS
5 Declare Data XML 6 setData=EVENTDATA
7 insert into Admin.dbo.tblAuditData(
8 StrServerName,StrDatabaseName,StrComputerName,
9 StrSchema,StrObjectType,StrObjectName,StrAction,
10 StrUserName,StrLoginName,StrTSQLCommand)
11 values (
12 ‘[Server Name]’,
13 data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)'), 14data.value(’(/EVENT_INSTANCE/ComputerName)¹‘,‘varchar(100)’),
15 data.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(100)'), 16data.value(’(/EVENT_INSTANCE/ObjectType)¹‘,‘varchar(100)’),
17 data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)'), 18 data.value('(/EVENT_INSTANCE/EventType)[1]','varchar(100)'), 19 CONVERT(VARCHAR(100),CURRENT_USER), 20data.value(’(/EVENT_INSTANCE/LoginName)¹‘,‘varchar(100)’),
21 data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(MAX)')) </pre><!--@END —>

Note that before running this, on line 12 you’ll need to replace [Server Name] with the name of the server this trigger will be in resides on.

The third line, FOR DDL_DATABASE_LEVEL_EVENTS, specifies the events the trigger will be fired for – in this case all events at the database level. Instead of specifying all events at the database level you can specify individual events. To specify individual events you can change this line to be FOR followed by the names of the specific events, for example FOR ALTER_TABLE,DROP_TABLE. Within a DDL trigger you can use a built in function called EVENTDATA. This function will return an XML data stream containing information about the DDL trigger just executed. The XML schema returned is this:

<SQLInstance>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ComputerName>name</ComputerName>
    <DatabaseName>name</DatabaseName>
    <UserName>name</UserName>
    <LoginName>name</LoginName>

</SQLInstance>

You can use this information to get – amongst other information – the database name, object name, and what type of action fired the trigger. The Declare Data XML line (line 5) declares a local variable of type XML which will hold the XML stream from the EVENTDATA() function. Line 6 calls the EVENTDATA() function and assigns the resulting XML stream to the Data local variable. Lines 7 through 11 create the beginning of the insert statement. Be sure to include the name of the database that you want the data to go to (in this case Admin). Without the database name present, the trigger will attempt to insert into a table within the same database, which may not exist.

Line 13 is where the EVENTDATA XML stream starts to be used. To access one of the XML elements within the stream use /EVENT_INSTANCE/ followed by the name of the element. Each of the lines that retrieve data from the stream uses a similar syntax. Notice line 19 is different. Instead of using information from the EVENTDATA function, it uses the keyword CURRENT_USER. This will provide the logged in user name.

To continue with this example, open the AdventureWorks database and choose Database Triggers within SQL Server Management Studio. To find Database Triggers, expand the database you want to create the trigger for. Then expand the Programmability node and find Database Triggers as shown in Figure 1:

Figure 1. Getting to the Database Triggers folder

There will already be a trigger there for logging – this is included with the AdventureWorks sample. Delete this trigger by right clicking on the trigger name and choosing Delete. Open the Database_Level_Logging.sql script, change the server name, and execute the script within the AdventureWorks database.

To test the trigger, open the AdventureWorks database within SQL Server Management Studio and open the list of tables. Modify the HumanResources.Department table. Add a column called TestTrigger and save the modification. When the modification is saved a record will be written to the tblAuditData table within the Admin database. Open the tblAuditData table within the Admin database and you will see the data that was captured.

The next part of this solution is to create a trigger that will capture most actions at the server level. The server level does not allow you to capture all transactions that occur. Therefore you must specify which actions you want to capture within the trigger. If you look back at the table of actions that are within the server scope, those are the actions that can be captured. After determining the actions you want to have the trigger respond to, you can create the trigger. The script (Server_Level_Logging.sql) is as follows (with line numbers added):

1 CREATE TRIGGER SERVER_LEVEL_LOGGING

2 ON ALL SERVER

3 FOR  

4 CREATE_LOGIN,GRANT_SERVER,ALTER_LOGIN,DROP_LOGIN,DENY_SERVER,
   REVOKE_SERVER

5 AS 

6 Declare Data XML
7 Set Data=EVENTDATA

8 insert into Admin.dbo.tblAuditData(

9 StrServerName,StrDatabaseName,StrComputerName,

10 StrSchema,StrObjectType,StrObjectName,StrAction,

11 StrUserName,StrLoginName,StrTSQLCommand)

12 values ([servername],

13 data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)'),
14 data.value(’(/EVENT_INSTANCE/ComputerName)¹‘,‘varchar(100)’),

15 data.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(100)'),
16 data.value(’(/EVENT_INSTANCE/ObjectType)¹‘,‘varchar(100)’),

17 data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)'),
18 data.value(’(/EVENT_INSTANCE/EventType)¹‘,‘varchar(100)’),

19 CONVERT,CURRENT_USER),

20 data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)'),
21 data.value(’(/EVENT_INSTANCE/TSQLCommand)¹‘,‘varchar(MAX)’))

The biggest difference between the two triggers (server level versus database level) is the FOR statement. In the server level logging trigger the actions that occur to fire the trigger must be specified, unlike with the database level logging trigger; the difference arises because not all actions can be captured. Again, you should replace the server name on line 12 with your server name before executing the script on the server that you want to include the trigger on.

To test the trigger, open the Security folder within the SQL Server Management Studio. Right click on Logins and choose New Login. Use Test as the user name and password. Save the new login. When the login is saved a record will be written to the tblAuditData table within the Admin database. Open the tblAuditData table within the Admin database and you will see the data that was captured.

One way to make sure that the database level logging trigger is added to each database that is created is to first add the trigger to the model database. To do this, open a new query on the model database (found under the System Databases folder). Open the Database_Level_Logging.sql script file. Change the server name within the file. Execute the script to create the Database_Level_Logging trigger within the model database. Close the script file and the query that was created. Make sure you have no open connections to the model database – otherwise, database creation will fail.

The model database is a template used by SQL Server to create a new database. Each time a new database is created this template is applied so all databases begin with the same structure.

Create a new database called TriggerTest. After the database is created, choose the database from within the Object Explorer and expand Programmability. Within the Programmability folder expand the Database Triggers folder and the Database_Level_Logging trigger will be there. By adding this to the model database you are guarenteed the database level logging trigger will be added to each new database on the server.

Viewing the Audit Report

The second part of this solution is a set of reports to allow administrators to view all audit records for a server along with a daterange. If you are unfamiliar with the new SQL Server Business Intelligence Development Studio you can read on to find out how to create your first report based on the auditing data. If you are familiar with the development studio, the solution, project, and rdl files have been included with this article for you to use.

To create the reports, first create a new Report Project by opening the Business Intelligence Studio and choose File, New Project. When the New Project window appears enter a name for the project and a location as shown in Figure 2:

Figure 2. Creating a new report server project.

When the new project opens, open the Solution Explorer and right click the Shared Data Sources folder. This folder will hold a data source that points to the Admin database. Since there will be multiple reports a shared data source is the best option. This shared data source can be used by more than one report. If you were just creating one report against a data source (database) then you would not need to use a shared data source. When the Shared Data Source form appears enter the name of the data source (in this case, Admin). Click the Edit button to build the connection string. The connection properties window is shown in Figure 3 below:

Figure 3. The connection properties window

After entering the connection information click Test Connection to verify the information was correct and then click OK. The Shared Data Sources window as shown below will appear again. Click OK on the Shared Data Sources window.

Figure 4. Shared Data Source dialog.

After the data source is created, right click the Reports folder and choose New Report. A window will appear to allow you to create a new report. Click Next on the first splash window that appears. When the Select Data Source window appears click Shared Data Source as shown in Figure 5 below:

Figure 5. Selecting the data source

Verify Shared Data Source is selected and click Next on the Select Data Source window. The first report will be a list of all audit records for a server. When the Design Query window appears enter the following:

Select *  from tblAuditData 

Where 

StrServerName = ServerName
and DteActionTaken&lt;dateadd(d,1,enddate)

and DteActionTaken>=@startdate

For ServerName, the user should provide the name of the server against which the report will be compiled. enddate is the end of the reporting period and @startdate is the beginning of the reporting period, again these two are parameters for the user to enter. If you choose Tabular for the Report Type you’ll see something like Figure 6:

Figure 6. Selecting a tabular report

Click Next to move to the Design Table window. Within this window click strDatabaseName on the left side and then click the Group button. This will move StrDatabaseName over to the Group area, as shown below. This will group the report by database name within the server.

Figure 7. Grouping by StrDatabaseName

Click next to move to the table layout window. Within the table layout window choose Stepped, as shown below, which will step the detail of each database below the database name.

Figure 8. Choosing a stepped table layout

Click Next to choose the table style as shown in Figure 9 below. Here I’ve selected Plain style.

Figure 9. Choosing the table style

Finally click Next and enter AuditingByServer as the report name and click Finish. The report will be created as an rdl file and will open in a design environment. The rdl file gives a visual representation of the report; more specificlly it is an XML file that holds all of the information about the report and the report designer uses that XML file to graphically show what the report will look like. You can see three different tabs at the top of the report. The first is data, that is where you enter the SQL statement into to create the report. The second is layout, which you use to lay the report out the way you want it. The final tab allows you to preview the report that you have created.

You can use the layout tab to clean up the appearance of the report by doing things like changing the column titles to be descriptive and to bold the database names. You can do things here to make the report more user-friendly.

After the report is created, you should upload it to the report server. First, you must create a new data source. You have two choices with a data source for a report within the report server. You can create a shared data source on the web server similar to what was created within the Business Intelligence Development Studio, or you can create a specific data source for each report. Since you will probably have multiple reports that use the same Admin database it’s best to create a shared data source.

To create a shared data source, you must have SQL Server Report Server installed along with IIS. Open the Report Manager web site, usually http://servername/Reports. Choose the New Data Source button from the menu bar (shown in Figure 10):

Figure 10. Choosing New Data Source from Menu Bar.

You should give the data source a name, enter the connection string, and provide the credentials you want to use to connect to the Admin database. After providing that information click the Ok button and the data source will be created.

Next the report file (with extension rdl) must be uploaded to the report server. To do this, click the Upload File button on the menu bar. Click the browse button to find the report definition file that was created for the report. After you find and choose the file, give it a name that will be displayed in the list of reports. This should be a friendly name so others know what the report is.

Finally, associate the report file with the new data source. To do this, click on the report from the list of reports on the main Report Manager page. The report will automatically go into view mode; at this point it’ll generate an error because the data source hasn’t yet been specified. Click the Properties tab above the Menu Bar. When the properties are displayed click the Data Sources link on the left (in the area shown in Figure 11):

Figure 11. Choosing data sources from the Report Manager

When the Data Sources properties page appears there will be red words that let you know the data source does not exist. Click the Browse button as shown in Figure 12.

Figure 12. Clicking the Browse button to find the data source

On the next page find the data source you just created and click it. After that make sure to click the Apply button at the bottom of the page or the data source will not be selected. After choosing the data source you will be able to view the report.

Building an Enterprise Solution

There are some additional steps that need to be taken to make this an enterprise solution, which would encompass all database servers within an enterprise.The enterprise solution that I have setup includes one database server receiving data from all of the other database servers. The receiving database server also has SQL Server Reporting Server installed and will be used to report on all servers.

Setting up the Solution

The first step is to create the database and the tblAuditData table on the receiving database server. Next create a linked server from the other database servers to the receiving server. To do this, open SQL Server Management Studio and connect to the database server that will hold the triggers. Expand the Server Objects and then Linked Servers as shown in Figure 13.

Figure 13. Expanding the server objects and linked servers.

Right-click LinkedServers, select the SQL Server and type the name of the receiving database server. Before clicking Ok, click the Security option on the left. I created a SQL Server login on the receiving database called AdminUser which has the proper permissions to the Admin database. To make the linked server always use this login, click the radio button, Be made using this security context at the bottom of the screen and enter the user name (in my case AdminUser) and password from the receiving server as shown in LinkServerSecurity.gif:

Figure 14. Setting up Security Context for the linked server.

Click OK to save the linked server. It’s a good idea to verify that a query can be run against tblAuditData from the linked server (not the receiving server). To do this use select * from [linked server name].tblAdmin.dbo.tblAuditData where [linked server name] is the name of your linked server just created. If you can run the select statement (there probably will be no rows) without a login failure the login information just provided is correct.

Next you will need to drop the database and server trigger on all other database servers. Then open each script and change the Insert statement. Originally the insert statement was Insert into Admin.dbo.tblAuditData. This needs to be changed to Insert into [linked server name].Admin.dbo.tblAuditData where [linked server name] is the name of the receiving server.

Now the triggers add their data to the receiving server via the linked server mechanism within SQL Server.

After this is completed, open the AdventureWorks database (or any database) on one of the database servers that has the triggers added. Modify the HumanResources.Department table again and add a new field called AnotherTriggerTest. Save the modification. You may receive an error message stating that the provider was unable to begin a distributed transaction. This is a gotcha that can be difficult to fix. I’ll quickly present a couple of workarounds.

Solving the Distributed Transaction Error

In this section I’ll explain four different possible causes of the error and the associated solutions/

Is the DTC Running?

The easiest solution is to verify that the Distributed Transaction Coordinator service is running on all database servers. You can check this in the Services configuration of the Administration Tools within the Control Panel or you can see this by expanding the Management node within the SQL Server Management Studio for each of the servers.

Is the DTC Disabled?

The second solution is only relevant if your receiving database server is on a Windows 2003 Server. By default network Distributed Transaction Coordinator access is disabled in Windows 2003 Server. For instructions on how to fix this issue see knowledge base article 816701 from Microsoft.

Are DTC Network Access Settings Disabled?

The third solution again is only relevant if your receiving database server is on a Windows 2003 Server. By default the network access settings of Distributed Transaction Coordinator are disabled on new installations of SQL Server on Windows Server 2003. For instructions on how to resolve this problem, see Knowledge Base article 329332 from Microsoft.

Is the Database Server Correctly Configured?

The fourth solution, and one that finally made my solution work, is that your database server that is not the receiving server may not be configured properly. This issue may only occur on desktops instead of servers. If you are setting up an example of this solution you may encounter this problem. Your Distributed Transaction Coordinator may not allow client access. To check this, open your Control Panel and Administrative Tools. Then open Component Services. Select Computers and then My Computer. Right click My Computer and choose Properties. When the My Computer Properties window appears click the MSDTC and click the Security Configuration button. Make sure Network DTC Access, Allow Remote Clients, Allow Inbound, and Allow Outbound are all checked as shown in Figure 15:

Figure 15. DTC security configuration

One of these solutions or a combination of them should fix your "unable to create distributed transaction" error message. Once this issue is corrected, all databases and servers with the triggers will feed their data into the receiving database. The report that was created in this solution should then point to the receiving database. This enterprise solution provides a way to dump audit data from all SQL Servers 2005 servers within a given enterprise into one database to report on.

Conclusion

This article provided a solution to audit all database structure and server-level SQL Server changes made. This solution provides a database administrator with plenty of information to track down any change that may have caused an issue. The database administrator can also use this information to keep up with changes that have been made in development and test environments without keeping a separate log. This solution provides the same functionality as a change log. With the reporting shown in this report and the ability to create other reports within the report server this solution can be extended to suit your needs.

Permanent Link

by Feature Articles