In the previous two articles of this series, we looked at Change Tracking and Change Data Capture. In this article we will take a look at another technique available in SQL Server 2008 and higher. This new technique is SQL Audit.

SQL Audit, What is it?

SQL Audit is one of the technologies introduced by Microsoft SQL Server that answers the question “who and when”. SQL Audit is least intrusive in terms of performance of all the techniques covered in this series. This is because SQL Audit uses extended events to create audits.  (If you want to know more details about extended events, please click here.)

SQL Audit is flexible enough that we can configure where the audit information can be written. It can write to a binary file, the Windows Security log or the Windows Application log. SQL Audit can be set up with high granularity, for specific users at the object level if necessary.

SQL Audit can be set up using the GUI or T-SQL. In this article we will look at how to write to a binary file and how to write to an event log using T-SQL.

Set up SQL Audit

To get enough information regarding SQL Audit, we will try out two types of Auditing. First we will learn how to set up Auditing for an object (Table) and then we will Audit Security. We will also look at how to write to a file and how to write to the event log of the server.

How difficult is it to set up a SQL Audit? Let us take a look. As usual we will create a new database to test the set up of SQL Audit. The main reason why it is better to set up a test database is that when testing a new functionality and something goes wrong, the problem is contained within that database.

USE master;
go
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestDBAudit')
    DROP DATABASE TestDBAudit;
go
CREATE DATABASE TestDBAudit;
--Creates a DB with default settings
go

When we execute the above command, a databse will be created with default values.

Now we have a test database to play around with. For us to audit the DML statements on a table, we will have to create a table. After creating the table, we will populate this new table with some rows of data. To accomplish that, we just need to run the below shown script.

USE TestDBAudit
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'tbProducts')
    DROP TABLE dbo.tbProducts
GO
CREATE TABLE dbo.tbProducts (
    ProductID INT NOT NULL CONSTRAINT PK_tbProducts PRIMARY KEY,
    ProductName VARCHAR(30) NOT NULL,
    ProductCost MONEY NOT NULL
)
GO
INSERT INTO dbo.tbProducts( ProductID, Productname, Productcost)
VALUES(1, 'DVD Player-Samsung', 1000.00);
GO
INSERT INTO dbo.tbProducts( ProductID, Productname, Productcost)
VALUES(2, 'TV-Samsung', 1500.00);
GO
INSERT INTO dbo.tbProducts( ProductID, Productname, Productcost)
VALUES(3, 'Camera-Canon', 150.00);
GO

When we execute the above query, it will create a table and insert three rows of data.

Let us start setting up SQL Audit. We will take a look at how to create the key objects of SQL Audit one by one.

Server Audit

Server Audit is one of the base objects that has to be created to set up SQL Audit. Server Audit is used to collect server level or database level actions. When you create the server audit, you will have to specify the location where you will be storing the audit information. The output location is otherwise known as Target. This can be a file, or the windows event log (Application or Security log).

For this test, we will write to a text file. We can create the Server audit by executing the below mentioned script.

USE [master]
GO
CREATE SERVER AUDIT [Audit-TestDBAudit]
TO FILE
(    FILEPATH = N'C:\Audit-Test\' -- Directory should exist
    ,MAXSIZE = 5 MB -- maximum size the file can grow, Default is unlimited
    ,MAX_ROLLOVER_FILES = 20 --maximum number of files to keep in the sustem
    ,RESERVE_DISK_SPACE = OFF -- Allocates the file on the disk to the maxsize specified
)
WITH
(    QUEUE_DELAY = 1000 --To specify the maximum time in millisec
    ,ON_FAILURE = CONTINUE --Specifies to continue or shutdown if audit cannot be processed
)
GO

Before we execute this query, let us take a look at the command and its parameters. As you can see we are creating a server audit with a target as file. When the target is a file, we have to specify the FILE PATH. The path we specify must exist on the server. The file names are generated by the audit based on the audit name and the audit GUID. Let us look at the other parameters one by one.

MAXSIZE: As the name implies, it is the maximum size the file can grow. The size can be specified in MB, GB or TB. The default value is unlimited growth.

MAX_ROLLOVER_FILES: This parameter specifies the maximum number files to retain in the system. The default value is unlimited.

RESERVE_DISK_SPACE: The two values that can be set for this parameter are OFF or ON. When it is ON, the audit file is pre-allocated to the maximum size previously set. This is applicable only when the MAXSIZE value is not set as unlimited.

QUEUE_DELAY: This parameter specifies the maximum time in milliseconds that can elapse before an audit action is forced to be processed. If the value is set as zero then the audit action is synchronized.

ON_FAILURE: The parameter ON_FAILURE can be specified to control what action the audit should take if there is a failure to write to disk. The two values that can be set are CONTINUE or SHUTDOWN.

AUDIT_GUID: This parameter is used when the there is a need to support database mirroring.

When we execute the above query, we get the message as shown below

With this script, we have created a server level base object for auditing. You can view the details of the Server audit that was created using the SQL Server management studio (SSMS). Expand the Security/Audits option on the server level and you will be able to see the newly created server audit as shown in the figure below.

If you look at the figure, you might have noticed that there is a small red down arrow on the Audit. This is because when a server audit is created, it is created in a disabled state.  This audit information will be written to a file in the specified folder. Now let us try to audit an object in the database we just created.

Database Audit Specification

Database audit specification is set up to audit several database level auditing. This object belongs to a SQL Server Audit. As stated earlier in the article, all audit actions are collected from the extended events feature. This makes the auditing less intrusive on the database performance. To test how this works, let us try to create an audit to find out if any DML statements are executed against the table dbo.tbProducts.

USE [TestDBAudit]
GO
Alter database AUDIT SPECIFICATION [DatabaseAuditSpecification-test]
WITH (STATE = OFF)

Drop database AUDIT SPECIFICATION [DatabaseAuditSpecification-test]
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-test]
FOR SERVER AUDIT [Audit-TestDBAudit]
ADD (DELETE ON OBJECT::[dbo].[tbProducts] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[tbProducts] BY [public]),
ADD (SELECT ON OBJECT::[dbo].[tbProducts] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[tbProducts] BY [public])
WITH (STATE = ON)
GO

With the script shown above, what we are doing is to create an audit on the table tbProducts. This will audit any SELCT, UPDATE, INSERT or DELETE done by anyone against the table mentioned. Once this script is executed, any SELECT statement against this table should be audited. Let us check if this is the case by running a Select statement against the table tbProducts. On your SSMS execute the below shown script.

Select ProductID
    , Productname
    , Productcost
from [dbo].[tbProducts]
Go

This script when executed should be audited. Since our auditing mechanism for this test was writing to file in C:\Audit-Test, let us check on the OS level if auditing was done. If we navigate to C:\Audit-Test directory, we should be able to find a file in that directory. In this case, you will find the directory empty. Why did that happen? Easy, the Server Audit was not enabled till now. If you recollect when we ran the Server Audit, it was created but in a disabled state. To enable the server audit we have to alter the server audit that was created.

Use master
GO
Alter Server AUDIT [Audit-TestDBAudit]
WITH (STATE = ON) ;
GO

By executing the above given query we can enable the Server Audit. As soon as you execute this command, it will create an empty audit file in the directory C:\Audit-Test. Once this is done if we do any DML statement on the table dbo.tbProducts it will be audited to the file. Let us execute the select statement on the table dbo.tbproducts once again.

USE [TestDBAudit]
GO
Select ProductID
    , Productname
    , Productcost
from [dbo].[tbProducts]
Go

Once this script is executed you might notice that the audit file is not empty anymore.

You’ll find a file named like this: [name used with AUDIT SPECIFICATION]*.sqlaudit

If we open the file using textpad or notepad, you will see that there is some contents in the file. The only problem is that it is not easy to figure out what is written to that file. This entry is so garbled that it does not make any sense to you. In order to read this file and getting it in a proper format that makes sense, we can use one of the functions provided by Microsoft. The function provided by Microsoft is sys.fn_get_audit_file

There are three arguments for this function:

1. file_pattern: file_pattern specifies the path and file name for the audit file that is to be read. This can include a wild card as file name.
2. initial_file_name: using this parameter we can specify which the first file to read from is. We can pass this value as DEFAULT. This means that the function will read from the oldest file that is in that directory.
3. audit_file_offset: specifies the offset from where the function has to start reading from. This value can also be set as DEFAULT.

To get more details of the output provided by the function sys.fn_get_audit_file can be found here.

To get the basics of how to use the function we will concentrate on a subset of the columns provided by this function.

SELECT event_time
    , action_id
    , succeeded
    , session_id
    , session_server_principal_name
    , object_name
    , statement
    , file_name
    , audit_file_offset
 FROM sys.fn_get_audit_file ('C:\Audit-Test\Audit-TestDBAudit_*.sqlaudit',default,default);
GO

If you look at the above query, you will see the parameters that is passed through to the function sys.fn_get_audit_file. The first parameter is the file_pattern. Here we have specified the path and partial file name. The second parameter initial_filename the value provided is “default” and the third parameter audit_file_offset is also set as “default”. This means give me all audit information in all files that matches the file name specified. When we execute this query, we will get the below show output.

If you look at the figure, you will see two rows of data returned. The first row indicates the details of when the Audit was set enabled. The second row shows the details of the DML statement that was done against the table tbProducts. This gives almost all the data that is needed to do an audit.

Now let us execute the same select statement on the table dbo.tbProducts once again.

USE [TestDBAudit]
GO
Select ProductID
    , Productname
    , Productcost
from [dbo].[tbProducts]
Go

This will be another entry in the audit file. The audit file will have the below shown details.

Even though this select gives all the information this is hard to keep track of if there are lots of action on the table dbo.tbProducts. What if we could see only the statements that we have not seen till now? That will be easier to keep an eye on.  To get that info we need to give the right values for the two parameters initial_file_name and audit_file_offset. We have this information at our hands. If you look at the previous result set, you might have noticed the last two columns. The last two columns contain the file_name and the audit_file_offset. If we provide that information to the function sys.fn_get_audit_file, we will get the latest changes. The query will look something like I have provided below.

SELECT event_time
    , action_id
    , succeeded
    , session_id
    , session_server_principal_name
    , object_name
    , statement
    , file_name
    , audit_file_offset
 FROM sys.fn_get_audit_file ('C:\Audit-Test\Audit-TestDBAudit_*.sqlaudit','C:\Audit-Test\Audit-TestDBAudit_1DC57D65-F844-4035-8C9A-3ED07E21F3C5_0_129694753864910000.sqlaudit',2048);
GO

Please keep in mind that the file name might differ from what is shown.  Let us see if the above query works like we intended. Before we execute the above query, let us update data in the table dbo.tbProducts. We can do this by executing the below statement.

update dbo.tbProducts
Set ProductCost = 2750
where ProductID = 1
GO

This will update one record. Now let us try executing the select statement using the function. The result will be as shown below.

There are two things that are different from our previous select.

1. This query shows the details of the DML statement after the last offset we have already seen. This is what we expected.
2. There are two entries for one single update. When ever an update is done, there will be two entries. One will have an action as a SELECT and the next one will have the action as UPDATE.

You might have also noted that the Statement does not show exactly what the values were used. It shows the statement as shown below.

UPDATE [dbo].[tbProducts] set [ProductCost] = @1  WHERE [ProductID]=@2

The problem with this is that we do not know what row was updated. We only know that who and when the manipulation of data occurred. Please keep in mind that the version I used for testing the Audit functionality is SQL 2008, SP1. If you are using SP2 or higher, there is a slight change in behavior. If you use a parametrized update, you will be able to get the right information. For example if you use the below script to update, then you will notice that the audit file will show the values as well.

Declare @1 int = 2751, @2 int = 1 ;
UPDATE [dbo].[tbProducts]
set [ProductCost] = @1  
WHERE [ProductID]=@2
GO

We have seen how to write to a file while auditing. Now let us try to write the application log. For that let us clean up what ever we have done. We will first disable the database level audit specification and then drop it. Once that is done, we will drop the Server Audit after disabling. We can run the below shown scripts to do all that.

Alter database AUDIT SPECIFICATION [DatabaseAuditSpecification-test]
WITH (STATE = OFF)
GO
Drop database AUDIT SPECIFICATION [DatabaseAuditSpecification-test]
GO
USE master
GO
IF  EXISTS (SELECT * FROM sys.server_audits
  WHERE name = N'Audit-TestDBAudit')
BEGIN
 ALTER SERVER AUDIT [Audit-TestDBAudit] WITH (STATE = OFF)
 DROP SERVER AUDIT [Audit-TestDBAudit]
END
GO

By executing the statements provided above, we revoked auditing completely. We have looked at Server Audit, Database audit specification and how to write the Auditing information to a flat file. Now let us take a look at how to set a Server Audit specification and how to write the audit info to the event log.

Server audit specification

In simple terms, server audit specification just tells SQL Server what to Audit. Server audit specification is an object that collects many server lever action groups raised by the extended events feature. So what is this action group? The server level action group is like the security audit event class of SQL Server. These predefined action groups are then send to the Audit. The audit will then record them to the output or target that you have defined. The point to note is that the Server audit specification is on the instance level and not database level. There fore you can have only one server audit specification for each Server Audit.

You can have multiple action groups in one Server audit specification. If you would like to know more about the action groups in detail, please click here

Let us take a look at how this works. When we did the clean up in the previous section, we dropped Server Audit that we created when we were testing Database audit specification. Therefore we will start with creating Server Audit and then we will create the Server audit specification.

Create Server Audit

Previously when we created the Server audit, the target we used was a binary file. This time we will change the target to Application log. Let us see how that can be done. The T-SQL to create a Server Audit is as shown below.

Create Server Audit [WriteToApllicationLog]
    TO APPLICATION_LOG -- TARGET
    With
    (
        QUEUE_DELAY = 1500 --DELAY SET in ms
        , ON_FAILURE = CONTINUE --Action to be taken when Server Audit Fails
    )

We saw previously that when we create a Server Audit object, it is created in disabled state.  Let us enable this server audit that we just created. The T-SQL command to do this is as shown below.

ALTER SERVER AUDIT [WriteToApllicationLog]
WITH (STATE=ON);

Now we have set up a Sever audit object. We will look at how to create a Server audit specification next. For this we will try to create an audit to log all new logins created or dropped.

Create Server Audit Specification [AuditDropCreateLogin]
FOR Server Audit [WriteToApllicationLog] --Server Audit we created
ADD (SERVER_PRINCIPAL_CHANGE_GROUP) -- GROUP for logging Login changes
WITH (STATE = ON) -- To enable

By executing the above T-SQL we have are set to watch for all Login changes. Let us see if it worked. For this we will create a new login, Alter a login and then drop the Login we created. When we do this, we should see entries in the application log.

Create login testlogin
With password = 'StrongPassword',
check_policy = OFF --Dont use this in production
GO
ALTER login testlogin WITH
  DEFAULT_DATABASE = TEMPDB
Drop login testlogin
GO

The first script creates a test login account. As you might have noticed, there is a comment on the first script. Please take a good note of it. The second script alters the login that was just created and set the default database as tempDb. The last script drops the login that was created. Once we have finished running the three scripts, let us look if the Auditing actually worked.

For this test we specified that we would like write the auditing information in the application log, we should be able to see all the info in the event viewer option of the OS. You can view the application log using two methods. You can view it through the SSMS (if you have the needed Windows authority) or through the Event viewer option in the OS.

My personnel preference is through the OS. It is easy to set up a filter to view the application log that we are interested. The EventID that we are interested in is 33205. We can set a filter to see only EventID 33205. Here is the snap of how the eventide 33205 looks like.

If you look at this image, you will see almost all the details you want to see. You can see the statement that was executed, what the object that was affected was and which database.

The two methods we have discussed till now shows how informative SQl Audit is. Now let us clean up all the things that we did for this test.

Alter Server Audit Specification [AuditDropCreateLogin] 
WITH (STATE = OFF);
Drop Server Audit Specification [AuditDropCreateLogin] ;
/* You have to disable audit specifications before you'll be able to drop it ! */ALTER SERVER AUDIT [WriteToApllicationLog] 
WITH (STATE=OFF) ;
Drop Server Audit [WriteToApllicationLog] ;

Pros and Cons of SQL Audit

We have seen what SQL Audit is and how it works.

Now let us take a look at the pros and cons of using SQL Audit.

• One of the main advantages of using SQL Audit is that it is the least intrusive of all the auditing technology.
• SQL Audit can be configured as granular as you need it to be. That is: from Server level auditing to object level auditing.

Now let us look at the cons of using SQL Audit.

• Even though SQL Audit can be set up very granular, it does not tell us WHAT data changed in almost all cases. It also does not specify how the data changed.
• Another disadvantage is that it is difficult to maintain. Since the audit information is written into a flat file or the application log, we need to make sure that we gather the data from these targets and store it into a table so that it can be analyzed when ever we want.

Conclusion

We have seen the three methods provided by Microsoft to audit in this series of articles. One thing that we were able to conclude was that there was no one method that does it all. We will have to choose the best option available for the needs of the company. This could mean using two of the methods to get all the details that you are expecting from auditing.

Acknowledgements

I would like to thank Johan (@Alzdba)  for going through my article and giving important tips and editing this article.