SQL Server database auditing techniques

SQL Server database auditing is not used only to address auditing requirements for compliance. It has become necessary for the analysis of database actions, troubleshooting problems, investigating the suspicious and malicious activity. It can also help preventing users from inappropriate actions – as if you had a CCTV system on your databases

There are several SQL Server auditing techniques:

• Manual auditing – can be created to fulfill your specific requirements, but is time-consuming and error-prone
• Utilizing SQL Server Extended Events – easy to set, a wide range of actions can be audited, but offers neither information what was deleted/inserted nor old and new values for updates; detailed auditing can cause performance issues
• Using SQL Server triggers – easy to set, but can cause performance issues in high transaction databases
• Reading transaction logs – no additional data capturing as SQL Server already tracks these changes. More storage needed, some of the actions (such as EXECUTEs) are not audited
• Using SQL Server Profiler and SQL Server traces – flexible and complex. Difficult to read and filter records

Which one is right for you depends on your environment, what you need to audit, where you want to store captured actions and the way you want your reports to be

Manual auditing

SQL Server databases can be audited using specially developed stored procedures and functions for tracking data and object changes. It provides a flexible solution that requires a large amount of code and development, which increases the cost and timeframe of implementation

Utilizing SQL Server Extended Events

SQL Server Audit is a SQL Server feature, first introduced in the version 2008 that uses SQL Server Extended Events to audit SQL Server actions. It enables auditing different actions, providing much granularity in the setup process and covering a wide range of the SQL Server activity

To create a new SQL Server Audit object:

1. In SQL Server Management Studio, expand Security and right-click Audits

2. Select New Audit

   

3. Specify a name for the audit, whether to store audit data in an application event log, security event log or a file, and a location for the audit file

   
4. Click OK and your audit will appear in the Audits node in SQL Server Management Studio Object Explorer

5. By default, it’s disabled and thus shown with a red arrow. To enable it, right-click it and select Enable Audit

   
6. Choose whether you need Server Audit Specification or Database Audit Specification, depending on whether you want to audit the activity on a SQL Server instance or database level

7. To create a Database Audit Specification, expand the node of the database you want to audit, go to Security, right-click Database Audit Specifications and select New Database Audit

   

8. In the Create Database Audit Specification dialog, specify a specification name, associate the specification with the audit object created in the step #1, specify activity that will be audited in Audit Action Type. For database auditing, specify a database, object, or schema as Object Class, the name of the audited object, and the audited login

   

You can see all actions that can be audited using SQL Server Auditing in the drop-down list for Audit Action Type

The Principals you have to select in this dialog are actually user accounts that will be monitored

The same as audits, database audit specifications are by default disabled. To enable them, select this option in their context menu

Now, all DELETE statements executed against the Person.BusinessEntityAddress will be audited and inserted into files the names of which start with Audit-, such as Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-FCBDA77B431D_0_130161593310500000.sqlaudit, and stored in E:\

As there can be a lot of captured actions in a busy database, it’s recommended to save the audited info in a file. The sqlaudit file cannot be opened in a text or hex editor. Use Reporting Services or T-SQL fn_get_audit_file function for analyzing the data

For example:

SELECT event_time,action_id,statement,database_name,server_principal_name
  FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT);
      

shows the following results:

Even when you select all the columns, you won’t see what was actually deleted, just who and when made the deletion, which is one of the disadvantages of this method

Other drawbacks are:

• as SQL Server Audit uses SQL Server resources for detailed auditing, this can affect the overall SQL Server performance
• managing SQL Server Audit on multiple SQL Server instances cannot be centralized out of the box
• analyzing and archiving the audit data (in a file, or logs) involves manual importing, reporting and archiving
• being available only in SQL Server versions 2008 and later
• database level auditing is available only in Enterprise, Developer, and Evaluation editions

Using SQL Server triggers

SQL Server triggers are automatically fired when a certain event happens. Triggers are actually stored procedures executed automatically when a condition is fulfilled. Data Manipulation Language (DML) triggers can thus be used to track the INSERT, UPDATE, and DELETE statements. You can create these triggers one by one for each table and each statement you want to audit. You also need to make storage of the audited information – e.g. a SQL table where time of the transaction, user name, transaction type, etc. will be inserted. For tracking UPDATEs, it’s very useful to store both old and new values

For example, a trigger that is fired after a record was inserted into the Person.Person table inserts a table name, time and date when the record was inserted and the user name used to insert the record into a dbo. Repository table should look like this:

CREATE TRIGGER PersonPerson_I
ON Person.Person
AFTER INSERT 
AS
   INSERT INTO dbo.repository (
TABLE_NAME,
		TABLE_SCHEMA,
		AUDIT_ACTION_ID,
		MODIFIED_BY,
		MODIFIED_DATE,
		[DATABASE]
	)
	values(
		'Person',
		'Person',
		'Insert',			
		SUSER_SNAME(),
		GETDATE(),
		'AdventureWorks2012'
	) GO

Before such triggers are created, you should design and create the table(s) where the captured DML will be stored

This method is error-prone as there is a lot of manual work involved

ApexSQL Trigger is a database auditing tool that captures data and schema changes that have occurred on a database, including the information on who made the change, which objects were affected by it, when it was made as well as the information on the SQL login, application and host used to make the change. It stores all captured information in the central repository and exports them in print friendly formats. To create triggers, just select the tables and operation types you want to audit

1. Start ApexSQL Trigger

2. Connect to the database you want to audit

   

3. In the main grid, select the table you want to audit

   

4. In the Columns pane, select the columns to audit

   

5. Check the transactions to audit – INSERT, DELETE, UPDATE

6. Repeat the steps 3 to 5 for all tables you want to audit

7. In the menu, click Create triggers

8. The script that generates the specified triggers is shown in the Script dialog. Check it out and press F5 to execute it

   

Once the triggers are created, they are fired for every INSERT, DELETE and UPDATE executed against the table and the details of the operation are stored into AUDIT_LOG_DATA and AUDIT_LOG_TRANSACTIONS tables

You can easily see those using built-in ApexSQL Trigger reports, or creating SQL queries of your own

While triggers provide granular auditing and easy accessible storage, their biggest disadvantage is that the ones fired on a busy database can cause overhead

Reading transaction logs

As every schema and data change in a SQL Server database is added into an online transaction log as a log record, reading these records can be used as an auditing technique. Opening a database online transaction log file, detached transaction logs or transaction log backups and reading them is not quite simple. One of the options is to use undocumented functions such as fn_dblog, fn_dump_dblog, and DBCC PAGE

Besides complexity and problems with UPDATE/BLOB reconstruction, their biggest disadvantage is that they show hex values you have to decipher

To read transaction logs, use a SQL Server transaction log reader such as ApexSQL Log. It audits, reverts or replays data and object changes that have affected a database, including those that have occurred before ApexSQL Log installation. It also captures information on the user, application and host used to make each change

1. Start ApexSQL Log

2. Connect to the database you want to audit

   

3. In the Select SQL logs to analyze step, add the transaction log backups and detached transaction logs you want to read. Note that they have to form a full chain in order to provide successful auditing

   

4. Use the Filter setup options to narrow down the result set using the time, operation type, table’s name, user and other filtering options

   

5. Click Open

6. The results are shown in the main grid and you can easily create undo and redo scripts, or export them into CSV, HTML, XML or SQL files and save on the hard disk

   

The advantages of this method are that there are no triggers, and no additional processes for capturing the audit information that can affect SQL Server performance. Transaction history can be obtained for the period before the tool was installed, unlike with triggers and Extended Events
The disadvantages are:

• more space is required for sufficient data sources, as a database has to be in the full recovery model, and a full chain of transaction logs must exist
• not all actions that a user might want to audit are stored in a transaction log. For example, executed SELECT statements and queries aren’t

Using SQL Server Profiler and SQL Server traces

Using SQL Server Profiler and SQL Server traces for auditing is a very complex solution with a lot of manual work, and therefore quite error-prone

ApexSQL Audit is an auditing tool built on SQL Server traces that provides “who saw what” information, fault tolerant auditing, centralized reporting, user friendly GUI for setting auditing on more than 230 operations, and a temper-proof centralized repository for storing audit records and configuration. It configures traces according to the setting a user has specified or uses its default configuration that covers most common auditing requests

1. Start ApexSQL Audit

2. Click the ‘Add server’ in the Configure tab to select a server for audit

   

3. Click the ‘Add database’ button to select a database for auditing, and select server or database operations you want to audit.

   

   Another option is by using the Advanced filter type:

   

Now, whenever any operation you selected is performed on audited SQL Server, a record will be saved in the central repository database – ApexSQLCrd

To see auditing records, you can use a built-in local reports

The advantages of ApexSQL Audit are the easy auditing setting, a wide range of operation types audited, the temper-proof central repository, web reporting, so even remote users can easily access all auditing records, easy and centralized administration

As presented above, there are several SQL Server auditing techniques that utilize different SQL Server features. They start from manually written and thus error-prone code, to user-friendly GUI. Some of them can cause overhead and SQL Server performance issues, while others have almost no effect. Determine what type of operations you need to audit and select an ApexSQL auditing tool to set up auditing and create reports in just a few mouse clicks

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.