Honeycombing a Database

In this article, taken from Chapter 9 of his new book, Protecting SQL Server Data, John describes how to set a "honey trap" for would-be data thieves, allowing the DBA to identify the precursors of an attack and respond quickly and also to better understand the techniques being used to breach existing security measures.

In the world of network servers, the term “honeypot” refers to a server that is placed in an environment for the sole purpose of attracting those who are snooping around, and capturing their activities within the honeypot server. Honeycombing a database is a very similar approach and involves creating “decoy” tables within a database that appear to contain valid, and unprotected, sensitive data. When unauthorized activity occurs on the decoy table, it is captured in an audit table and a notification is sent to the appropriate parties.

Once the notification is received by the Database Administrator, immediate termination of the violating user account can occur. Also, the data that is captured during the unauthorized activity can be reviewed to gain a better understanding of how unauthorized activities are occurring and identify ways to prevent them from occurring on the real data.

Until the release of SQL Server 2008, the process of honeycombing a SQL Server database was very difficult. Triggers could be used to capture the occurrences of UPDATE, INSERT and DELETE statements; but nothing was available to capture SELECT statements, beyond running SQL Server Profiler. With SQL Server 2008’s auditing feature, a much wider array of events, including SELECT statements, are available, with the added advantage that we don’t need to use triggers to capture these events.

This article will demonstrate how to create a honeycomb table, audit activity on it, and send notifications of this activity to the relevant parties.

Implementing a Honeycomb Table

The process of honeycombing a database begins with the creation of a decoy table. The script in Listing 1 creates a honeycomb table in the default Database Object Schema of our HomeLending database. It has the mouthwatering name of Customer_Information and the column names, including First_Name, Social_Security_Number and Address_Street, are equally likely to attract the attention of the data thief.

Listing 1: The Customer_Information Honeycomb table.

One of the goals in honeycombing a database is to capture the casual exploration of data by users who have limited authorized access to the database. To open this decoy table to all levels of curiosity, we will grant SELECT, INSERT, UPDATE and DELETE permissions to the public database role, using the GRANT statement, as shown in Listing 2. All database users are members of the public database role by default.

Listing 2: Open season on the Honeycomb table.

Simply having an empty decoy table in your database will not be sufficient to draw activity its way. It must be filled with alluring, but bogus, data. The first rule in populating this decoy table is obviously never to use actual data.

Data scrambling techniques can be applied (I describe how to do this in Chapter 8 of my book, Protecting SQL Server Data), or you can generate decoy data using a data generator, such as Red Gate’s SQL Data Generator. It is recommended that you populate the decoy table with a number of rows that is consistent with the non-decoy tables. In our HomeLending database, SQL Data Generator was used to populate five thousand rows of data, a sample of which are shown in Figure 1.

Figure 1: Screenshot from Red Gate SQL Data Generator

Creating a Server Audit

In order to capture the activity of would-be data thieves on our honeycomb table, we need to implement the auditing feature of SQL Server 2008. The first step in this process is to create a Server Audit object, which allows us to monitor a collection of actions that might occur on the target table, and record this activity in a file, typically the Windows Application log file.

The syntax of the CREATE SERVER AUDIT method is as follows:

The arguments to this method are:

  • Audit Name – the textual reference to the server audit.
  • Output Location – the options for this argument are:
    • FILE: write to a binary file. The file path is required in parenthesis after “File” is specified.
    • APPLICATION_LOG: write to the Windows Application Log.
    • SECURITY_LOG: write to the Windows Security Log.

In Listing 3, we create in the Master database a Server Audit object called Honeycomb_Audit, which will write to the Windows Application Log.

Listing 3: Creating the Server Audit object.

  • Note that the Server Audit object is created at the instance level so the reference to the database in which the method was executed is not required.
  • When a Server Audit is created, it is disabled by default and will need to be enabled in order for it to begin collecting information. Listing 4 activates the Server Audit.

Listing 4: Activating the Honeycomb_Audit.

Creating a Database Audit Specification

A Database Audit Specification is a member of the Server Audit and collects specific information about the database-level events on which the Server Audit reports. The CREATE DATABASE AUDIT SPECIFICATION method is executed in SSMS to create a Database Audit Specification. The following is an example of the syntax of this method:

The arguments to this method are:

  • Specification Name – the textual reference to the Database Audit Specification.
  • Server Audit – the textual reference to the Server Audit of which the Database Audit Specification is a member.
  • Action – the action or comma delimited list of actions to be monitored.
  • Securable – the database object that is to be monitored.
  • Principal – the Database User, Database Role, or Application Role that is being monitored.
  • With State – defines whether the Database Audit Specification is active (ON) or inactive (OFF).

In the HomeLending database, we will create a Database Audit Specification with the name of Customer_Information_Spec and capture any SELECT, INSERT, UPDATE, and DELETE events that are performed by the public database role, as shown in Listing 5.

Listing 5: Creating the Database Audit Specification object.

All database users are members of the public database role; therefore we will know when any user executes any of these methods on our decoy table.

Reviewing the Windows Application Log

By executing a simple SELECT statement against our decoy table, in the HomeLending database, the Server Audit is initiated. Many pieces of information are captured in the Windows Application Log, but the most critical in identifying the event that occurred are shown in Table 1.

Log Item

Captured Value

Description

Source:

MSSQL$SQLINSTANCEA

The Instance Name

Date:

5/28/2009 6:16:30 AM

The Time of the Event

Keywords:

Classic,Audit Success

Indicates An Audit Event

Computer:

SERVER1

The Server Name

Session_id:

52

The SPID

Server_Principal_Name:

SERVER1\John

The SQL Server Login

Database_Principal_Name:

dbo

The Database User

Server_Instance_Name:

SERVER1\SQLINSTANCEA

The SQL Server Instance

Database_Name:

HomeLending

The Database

Schema_Name:

dbo

The Database Object Schema

Object_Name:

Customer_Information

The Table Name

Statement:

Select * from customer_information

The Statement That Was Executed.

Table 1: Critical auditing information captured the Windows Application Log.

The Windows Application Log can be located by navigating to the Windows Control Panel on the Start Menu and selecting Administrative Tools followed by Event Viewer. Within the event log, to the upper left, is an icon for the Windows Application Log.

It is useful to review the Windows Application Log to identify any events that have occurred on the decoy table, but it is a passive tool and depends upon the intentional review of the logs at a given point in time. Unless the DBA is constantly checking the logs, hours or days could pass before an event on the decoy table is identified. Instead, the DBA will need to create an alert that will notify the appropriate parties, through an e-mail or pager, when an event occurs.

Creating an Operator for Notification

The first step in creating a SQL Server alert is to create an Operator. An Operator is the person, or people, who will receive an alert when one is raised. We create an operator by executing the sp_add_operator system stored procedure in Management Studio. The following is an example of the syntax of this system stored procedure:

  • Operator Name – the textual reference to the Operator.
  • Enabled – indicates whether the Operator can receive notifications.
  • Email Address – the e-mail address to which notifications are sent for this Operator. This argument is only necessary when notifying through e-mail.
  • Pager Address – all pager notifications are sent through the e-mail system. The value of this argument will need to be the e-mail account of the pager that will receive notifications. This argument is only necessary when notifying through a pager.
  • Weekday Pager Start/End – the time of day during the weekday that notifications can be received. The value must be in the format of HHMMSS (Hour, Minute, Second). 0 indicates midnight. Despite this argument having the word “Pager” in it, it applies to e-mail notifications as well. This argument is only necessary when the Operator is active on weekdays.
  • Saturday Pager Start/End – the time of day on Saturday that notifications can be received. This argument is only necessary when the Operator is active on Saturdays.
  • Sunday Pager Start/End – the time of day on Sunday that notifications can be received. This argument is only necessary when the Operator is active on Sundays.
  • Available Pager Days – this indicates the days on which the Operator is available to receive notifications. This value ranges from 0 to 127. This value is determined by adding the assigned values of the days available. These assigned values are: Sunday (1), Monday (2), Tuesday (4), Wednesday (8), Thursday (16), Friday (32) and Saturday (64). For example: if an Operator is only available on Monday, Wednesday and Friday, this value would be 42 (2+8+32).
  • Netsend Address – the network address to which a notification is to be sent. This argument is only necessary when notifying through net send.
  • Category – the category of the Operator. This argument is optional.

Listing 6 shows how to create an operator for the DBA, called DBA1, in the msdb database. Of course, our intrepid DBA is always on call and so they are available everyday at all hours. Not that long ago, the DBA would have a pager strapped to them to receive very basic notifications. These days the availability of email, along with the multi-functional benefits of the cell phone, provide a means to receive a detailed email message quickly; therefore, the notification in our example will be sent via email.

Listing 6: Creating an operator to receive notifications

Creating an Alert for Notification

Once the operator has been created, we are ready to create our Alert. An Alert monitors the database for events. When an event occurs, a notification is sent to the Operators that are assigned to the Alert.

Alerts are dependent upon the SQL Server Agent, which must be running. If the SQL Server Agent is not running when an Alert is created, a message will be presented stating that it is not running and that the Alert will not function.

We can create alerts using the sp_add_alert system stored procedure, example syntax for which is as follows:

  • Alert Name – the textual reference to the Alert.
  • Message ID – the value that identifies the message that is sent. In our case, our messages from the Server Audit are being captured in the Windows Application Log; therefore we can use the Error ID that is found in the sysmessages system table.
  • Severity – the value that indicates the severity of the message sent. If the Message ID is used, this value must be 0.
  • Enabled – indicates whether the Alert is active.
  • Delay Between Responses – indicates the wait time for a notification to be sent after a previous notification. The value of 0 indicates that there is no delay.
  • Notification Message – additional text that is sent with the event message. This is optional.
  • Include Event Description In – identifies where the SQL Server event message should be provided. A value of 0 indicates that the SQL Server event message is not to be sent. A value of 1 indicates that it should be included in an e-mail. The other options that are available for this argument are noted to be removed in later versions of SQL Server and should be avoided.
  • Database Name – the database where the event message will occur.
  • Event Description Keyword – the pattern of characters that will occur in an event that will trigger a notification. This is necessary only when filtering events.
  • Job ID – the id reference to the job that will be launched in response to the event. This is necessary only when launching a job in response to an event.
  • Job Name – the textual reference to the job that will be launched in response to the event. This is necessary only when launching a job in response to an event.
  • Raise SNMP Trap – indicates whether a Simple Network Management Protocol (SNMP) trap is raised in response to the event. This is optional and the default value is 0.
  • Performance Condition – defines the performance conditions that will trigger a notification. This is necessary only when using performance events to raise the Alert.
  • Category Name – the category of the Alert. This is optional.
  • WMI Namespace – the Windows Management Instrumentation (WMI) namespace that is referenced by the WMI query. This is necessary only when using WMI events to raise the Alert.
  • WMI Query – the query that identifies a WMI event that will trigger the alert. This is necessary only when using WMI events to raise the Alert.

Listing 7 creates a “Honeycomb Alert” in the msdb database.

Listing 7: Creating the Honeycomb Alert.

We are capturing our Server Audit events in the Windows Application Log; therefore we can use the message id of 33205 to identify that a Server Audit event has occurred.

Creating a Notification

Having created the Alert, Operators will need to be assigned to the Alert to receive notification messages. This can be accomplished by executing the sp_add_notification system stored procedure in Management Studio. The syntax of this system stored procedure is relatively straightforward:

This system stored procedure’s arguments are:

  • Alert Name – the textual reference to the Alert that will send a message.
  • Operator Name – the textual reference to the Operator that will receive a message.
  • Notification Method – identifies the method by which the message will be sent to the Operator. The values are: 1 (e-mail), 2 (pager), 4 (net send).

Listing 8 adds the Operator named DBA1 to the Honeycomb Alert Alert, and specifies that notification should be sent via e-mail.

Listing 8: Creating the notification

For this alert to be successful, the SQL Server Agent must be configured to send mail. This can be accomplished using the Database Mail Setup Wizard.

Note: Avoid SQL Mail
Please note that SQL Mail is a feature of SQL Server that is scheduled for retirement; therefore it is recommended to use Database Mail instead.

The Database Mail Setup Wizard is accessible in Management Studio within the Management folder in Object Explorer; simply right-click on the “Database Mail” option and select “Configure Database Mail”.

In order to make sure that everything is working as expected, it’s worth sending a test e-mail; simply right-click the Database Mail option and select the “Send Test E-Mail…” option.

Summary

By creating a honeycomb table, setting up auditing on that table, and enabling a means to be notified of an audit event, the Database Administrator can identify the precursors of an attack and respond quickly.

The audit feature of SQL Server 2008 is not limited to monitoring honeycomb tables. It also offers the ability to capture the occurrence of a wide array of events that occur on the database and instance giving the Database Administrator the proverbial “eyes in the back of the head” when identifying suspicious activities in the database that threaten the security of the sensitive data that is contained within it.