Creating a Honey Token on a Microsoft SQL Server

This walk-through is meant to provide DBAs or system admins working with databases with a method of implementing a honey token for security purposes. The method outlined in this walk-through is not the only method of implementing a honey token, and no guarantees will be made that this method is the best out of all options. It is simply the method that was used based on the DBA’s knowledge of the system and the alternatives.

A honey token is a piece of information – or a collection of pieces of information – that serves no purpose other than to alert stakeholders of possible unauthorized access to sensitive data. In this walk-through the honey token is a fake table in a production database. The table was created and populated with information by the DBA and unlike the other hundreds of tables in the same database it is not used for any legitimate purpose. The program that relies on the database will never hit the honey token table. Therefore, any time the table is accessed it can be assumed that someone has obtained unauthorized access to the database and is trying to find out what information is contained within all of the tables.

Implementing the honey token was not as easy as originally expected, which is why this walk-through was created – to help anyone else out there looking to accomplish the same. Creating a fake table is easy enough but how do you know when someone accesses it? At first the idea was to have a trigger send out an email alert any time the table is used. The problem with this is that a trigger can only be set for events that alter the table, meaning when someone inserts, deletes, or modifies information. A trigger cannot be set for a simple select statement, which is most likely to occur after an attacker gains unauthorized access to the database.

After some Googling I found that while a trigger cannot be set for select statements, the desired effect can be obtained by setting up a server audit. The Microsoft Knowledge Base has a resource up that describes the process of setting up a server audit and contains an example script that is easily customized for any environment.

Setting up a Table Audit

The first step is to create a fake table in your database – the honey token. Make sure that the table name doesn’t give away that it is a honey token. It has to follow the naming structure of the rest of the database so that it appears generic. It also needs to be populated with data. While you will still get a notification if an attacker queries an empty table, the idea is to not tip off the attacker that anything is out of the ordinary. Make sure that it contains only data that is pretty much useless to any attacker – you wouldn’t want to accidentally give them anything they can use.

After the Honey Token table has been created you can set up the audit on it. The T-SQL commands that I ended up using were as follows. Make sure to create the folder structure for the audit file before running this script or the commands will fail.


 

USE master;

GO

CREATE SERVER AUDIT <Enter Server Audit Name>

TO FILE ( FILEPATH = ‘<path to audit file>’ );

GO

ALTER SERVER AUDIT <Enter Audit Name>

WITH (STATE = ON);

GO

USE <Enter DB Name>;

GO

CREATE DATABASE AUDIT SPECIFICATION <Enter Specification Name>

FOR SERVER AUDIT <Enter Audit Name>

ADD (SELECT ON <Enter Table Name> BY PUBLIC)

WITH (STATE = ON);

GO

SELECT *

FROM <Enter Table Name>;

GO

SELECT *

FROM fn_get_audit_file(‘<path to audit file>’, NULL, NULL);

GO


 

Now the audit has been set up. Any select statement executed on the audited table will produce a new entry in the audit file, which can be confirmed by executing the last two select statements from the script once or twice.

Automatic Notification

Of course, the goal is to get notified automatically if someone without authorized access is looking around in your database. To accomplish this, a stored procedure in combination with an automated job can be used. The stored procedure queries the audit file for any new entries since the last time the job was ran and if it finds any, an email is sent out. The automated job simply executes the stored procedure on a schedule. To get alerts as quickly as possible the job needs to be run often, such as every minute. The code for the stored procedure that was used is as follows. Note that the audit time the stored procedure will look at has been adjusted to -7 hours. This is because the audit time is always recorded in UTC and -7 hours represents a correction for this. Make sure to adjust this correction to what is appropriate for your time zone.


 

CREATE PROCEDURE <Enter SP Name>

@recipients varchar(max) = ‘<enter recipients’ email addresses>’

AS

 

Declare @results Table

(

event_time datetime,

action_id varchar(5),

session_server_principle_name varchar(100),

server_instance_name varchar(100),

database_name varchar(100),

[object_name] varchar(100),

[statement] varchar(max),

additional_information varchar(max)

)

 

— find last time stored procedure ran – save datetime as variable

declare @lastrun datetime =

(

Select Top 1

Convert

(

DateTime,

Stuff(Stuff(Convert(VarChar, run_date), 7, 0, ‘-‘), 5, 0, ‘-‘)

+ ‘ ‘

+ Right(‘0’ + Convert(VarChar,(run_time % 1000000) / 10000), 2)

+ ‘:’

+ Right(‘0’ + Convert(VarChar,(run_time % 10000) / 100), 2)

+ ‘:’

+ Right(‘0’ + Convert(VarChar,(run_time % 100) / 1), 2)

) As Lastrun

From msdb.dbo.SysJobHistory With (NoLock)

Where step_name = ‘Server Audit’

Order By instance_id Desc

)

 

— query audit file for any new entries since last time stored procedure ran

Insert Into @results

(

event_time,

action_id,

session_server_principle_name,

server_instance_name,

database_name,

[object_name],

[statement],

additional_information

)

 

Select

dateadd(hh, -7,event_time) as event_time — -7 hours bc event_time is logged in UTC

,action_id

,session_server_principal_name

,server_instance_name

,database_name

,[object_name]

,[statement]

,additional_information

FROM fn_get_audit_file(‘<Enter path to Audit file>’, NULL, NULL)

Where dateadd(hh,-7,event_time) > @lastrun

 

select [statement] from @results

 

— if there are new results, send out email

DECLARE @body NVARCHAR(MAX) =

‘<html><body><H3>Alert</H3>’

+ ‘</br>’

+ ‘A honey token table was accessed. Someone might be trying to access your database without permission!’ + ‘<br>’

 

If exists(select 1 from @results)

begin

SELECT @body = @body

+ ‘<br>’

+ ‘Time: ‘ + Convert(VarChar,event_time) + ‘<br>’

+ ‘Server: ‘ + server_instance_name + ‘<br>’

+ ‘Database: ‘ + database_name + ‘<br>’

+ ‘Table: ‘ + [object_name] + ‘<br>’

+ ‘Username: ‘ + session_server_principle_name + ‘<br>’

+ ‘Query: ‘ + [statement] + ‘<br>’

from @results

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘<Enter Profile Name>’,

@body = @body,

@body_format =’HTML’,

@recipients = @recipients,

@subject = ‘Honey Token Alert!’ ;

end


 

Now simply create an automated job that executes this stored procedure every minute and you will be notified by email any time the Honey Token table is queried.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

fifteen − twelve =