Introduction
With the advent of the Information Era, data is being collected on a massive scale. Information Technology systems have made access to this data faster and easier. It has also made it easier for data to be misused.
We have all heard of instances where hospital employees have taken a sneak peak at a celebrity’s medical record. In many cases these hospital employees have legitimate reasons to access patient information, which means their access cannot be revoked or in some cases, even restricted, without hindering their ability to perform their duties efficiently.
This is just one of a plethora reasons why governments are implementing standardized auditing requirements such as HIPAA, SOX, PCI, GLBA, FERPA and Basel.
If we cannot prevent people from accessing data, we need to keep track of how it is being used. This will then enable us to investigate any suspicious activities to determine if a breach has occurred and the nature of the breach, which will allow us to take appropriate action.
To this end, Microsoft have added the Auditing feature to SQL Server 2008 onwards.
Auditing in SQL Server prior to 2008
Before SQL Server 2008, auditing had to be done by using a combination of features such as:
- Login Auditing and C2 auditing
- Triggers and event notifications
- SQL Trace could be used in conjunction with SQL Profiler
Utilizing the above mentioned features for auditing purposes can be quite cumbersome as it involves a significant amount of setup. The data accumulated by these methods are logged in different ways to a variety of locations which made it hard to assimilate. Potential performance impact can also be associated with some of these actions which makes it less than desirable.
Auditing in SQL Server 2008 onwards
SQL Server auditing is a new feature which makes use of extended events to allow you to audit everything that happens in your server, from server setting changes all the way down to who modified a value in a specific table in the database. This information is then written the Windows security log, the Windows application log or to a flat file.
In SQL Server 2008, Auditing was an enterprise only feature. In SQL server 2012, server auditing has now been made available to all editions, however database auditing remains for use by enterprise customers only.
Extended Events
Extended events are a highly configurable architecture used to handle events occurring in SQL Server. Extended events are built into the SQL Server code and as such it has a minimal impact on performance.
Extended events makes use of packages to group objects together. One of these packages is the SecAudit package which is used by SQL Audit. The events in this package are private and used internally by the SQL Audit feature. This package is unfortunately not accessible so none of its objects are available externally.
Auditing Components
The SQL Server auditing feature encompasses three main components:
- The Server Audit
- The Server Audit Specification
- The Database Audit Specification
The Server Audit
The Server Audit is the parent component of a SQL Server audit and can contain both Server Audit Specifications and\or Database Audit Specifications.
The Server Audit resides in the master database, and is used to define where the audit information will be stored, file roll over policy, the queue delay and how SQL Server should react in case auditing is not possible.
In the audit configuration the following is configured:
- The Server Audit name
- The queue delay which is the maximum amount in milliseconds that the system may wait before processing any audit. Basically an audit can be processed synchronously or asynchronously. To process synchronously set the queue delay to 0. For asynchronous processing, the lowest possible value is 1000 milliseconds.
- The action to take in the event that audit logging is unable to continue for any reason.
The options are:- Continue and ignore the log issue
Shut down the server
It may seem quite severe to shut down the server if it’s not able to write to the audit log. But it all comes down to how important it is for auditing to happen. Is it more important to have a complete audit trail or is it more important for the database to remain online. Shutting down the server is one of the requirements of common compliance.
To be able to configure this option, the user who creates or modifies the Audit needs to have SERVER SHUTDOWN permissions.
Fail the operation
This is a good alternative to shutting down the server entirely. The server will remain online but if an action takes place which requires auditing, the action will fail if the target is not available, ensuring that no audit information is missing on transactions which need to be audited.
In SQL Server 2012 auditing has become more robust by now allowing SQL Audit to recover should the target become unavailable temporarily.
The audit destination
The logging can be done to:
File
The recommended approach is to store audit logs to a network location off of the server
The file name used is automatically generated by SQL Server. This is done to ensure that the file names are always unique. The audit file name is made up of the following:
- The Audit name
- The Audit GUID
- Partition Number
- Time Stamp
- File Extension
Security log
One of the requirements of most compliancy regulations are that the audited data itself needs to be secured. This can be achieved in a variety of ways, but typically access to the security log is more restricted than access to the application log, and as such offers a good way to keep the logged information secured
Application log
Keep in mind that the default setting for the application log is to over-write events when it reaches the maximum size. This could result in audit information being los
- The file path to specify the path if the previous option selected to log to a file
- The limit of the size and the number of audit files
The maximum number of audit files to be used without rollover
In SQL Server 2008 it was only possible to set the number of files to have in addition to the current file before starting to rollover. An additional option has been added to SQL Server 2012 to allow DBAs to specify the number of audit files without running the risk of auditing data being over-written when the roll over starts.
Whether or not to reserve disk space specifically for the audit logs
A Server Audit is automatically assigned a uniquely identifying GUID. This GUID can be explicitly assigned. This GUID is static and cannot be changed after the audit has been created.
In SQL Server 2012, the audit now also allows for a filter to be specified. This is basically a WHERE clause for the audit which is evaluated before an audit event is written to the target audit destination. This is applied to all audit specifications linked to an audit. Any of the fields returned by the sys.fn_get_audit_file function except for file_name and audit_file_offset may be used as a filter expression.
An audit can be created either by using SQL Server Management Studio, by using transact SQL or SQL Server Management Objects (SMO).
In SQL Server Management Studio an audit can be created under Audit node which resides under the Security node in the Object Explorer.
An Audit can also be created by using the CREATE SERVER AUDIT Transact SQL command.
NB: All audits and audit specifications are created in a disabled state. Enabling an audit does not automatically enable all audit specifications linked to it. Each audit specification needs to be enabled individually. An audit or audit specification cannot be modified when it is enabled, it first needs to be disabled, then modified and re-enabled.
Both the audit and the audit specification need to be enabled for an event to be logged.
Permissions required:
To CREATE, ALTER or DROP an audit a user requires the ALTER ANY SERVER AUDIT permission. This is also included in the CONTROL SERVER permission.
In order to write to a file location the SQL Server service account will need to have write permissions on the network share. In order to read the file all users which belong to the Audit Reader role and Audit Administrators role need to have read permissions to that share as well.
Additional security is required when writing to the Windows Security Log, this is addressed later in this article.
Audit Specifications
Audit specifications can have 3 categories of actions:
- Server level actions
- Database level actions or
- Audit level actions which audits actions on the auditing process itself. Some audit actions are automatically audited such as changing the state of an audit to on or off
Some actions can be audited individually, such as auditing a select event on a table. This is referred to as an Audit Actions.
In most cases audit actions are grouped together resulting in Audit Action Groups. This facilitates audit specification configuration since actions which form a logical unit are included in a single group saving you from having to specify each one individually.
The Server Audit Specification
The Server Audit Specification which is available in all editions of SQL Server, is used to define what needs to be audited at a server level.
The Server Audit Specification is found under the security node in SQL Server. There can be only one Server Audit Specification per audit.
To create a Server Audit Specification, three things need to be specified:
- The Name of the audit specification. This is optional, a default name will be assigned if you do not enter one
- The Server Audit which defines the target the selected events should be logged to
The Audit Action Type. This is the events which should be audited
For the Server Specification all events are grouped into Audit Action Groups. The following are examples of Server Level Audit action groups:
SUCCESSFUL_LOGIN_GROUP
FAILED_LOGIN_GROUP
DBCC_GROUP
The full list of Server Level Audit Action Groups can be found here: Audit Actions and Audit Action Groups
When a Server Audit Specification is created via SSMS it is disabled by default. When creating it with T-SQL there is an optional parameter to create it in an enabled state
Permissions required:
In order to create a Server Audit Specification a user needs to have permission to connect to the database and have ALTER ANY SERVER AUDIT, the CONTROL SERVER permission allows the audit to be viewed by the user.
The Database Audit Specification
The Database Audit Specification audits events at a database level. Using more granular auditing can minimize the performance impact on your server. This is done by using a Database Audit Specification which is unfortunately only available in Enterprise edition. Using the Database Audit Specification, auditing can be done at object or user level.
Unfortunately it cannot be done at column level as of yet.
The Database Audit Speciation is created under the Security node of the relevant database.
It can also be created with Transact SQL and SMO
The following needs to be configured to create a Database Audit Specification:
- The Database Audit Specification name. A default name will be assigned if none is provided
- The Server Audit that the specification must be linked to
The Audit Action Type. There are both Audit Actions and Audit Action Groups which may be selected in this field.
INSERT and UPDATE is some of the Audit Actions which may be selected in this field.
A complete list of Audit Actions and Audit Action Groups applicable to the Database Audit Specification can be found here: Audit Actions and Audit Action Groups
- The Object Name of the object to be audited when an Audit Action has been selected
- The Schema of the selected object
- The Principal name. In order to audit all users, use the keyword public in this field
- User defined event id
This an id specified by the user, which is written to the user_defined_event_id column of the audit log. The datatype is smallint - Succeeded
Indicates if the procedure succeeded in writing to the audit log. The datatype is bit - User defined information
This is an optional parameter which allows the user to specify additional information regarding the event. This information is logged to the user_defined_information column of the audit log. The datatype is nvarchar(4000) File pattern
This is a nvarchar(260).The path or the path and filename of the file to read should be provided. To read all the files in a folder, specify the path to the folder using the asterisk (*) as wildcard. If an invalid file is specified the MSG_INVALID_AUDIT_FILE error message will be displayed
Initial file name
This is the path and file name of the file in an audit set where the reading should start. The datatype is nvarchar(260)Audit record offset
This can be used to specify the start location in the initial file. The datatype is bigint- Write the audit logs to a file server share on a different server to which the sysadmin does not even have permission. Only allow permission to the auditor
- Write to the Windows Security log is also a good alternative. If you want to write to the Windows Security log you will need to do the following:
Add the SQL Server Service account to the Generate Security Audits policy in your Edit Group Policy Editor
Change the Audit Object Access policy to include both Success and Failure
Keep in mind that when writing to the Windows logs, the Windows audit policy could potentially cause audit data to be lost. The windows logs usually roll over and as such can start over-writing older events which could cause some SQL Audit data to be lost.
In Windows 8 the plugin is called gpedit.msc. In order to access it, you need to type gpedit.msc in the search box. Remember to include the .msc extension or you might not find it
Unfortunately if you do only have the basic edition of Windows 8, you may not be able to access this application
- Write audit logs to a centralized location
- To facilitate processing of the audited data, load the logs into a database
- Use a file as a target for optimal performance
- Use targeted auditing to minimize the collected data and better performance
- When writing to the Windows logs, ensure that the roll-over policy of the Windows Logs, coincides with that of your audit strategy
- The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018
- 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017
- Introduction to Azure SQL Data Warehouse - August 29, 2017
Even though SQL Server will allow you to specify an audit action on server scope objects , such as system views, the objects will not be audited but no error will be raised either.
If you want to audit server scope objects, you need to create a database audit specification in the master database.
Permissions required:
In order to create a database audit specification a user needs to have permission to connect to the database and have ALTER ANY DATABASE AUDIT SPECIFICATION or the ALTER or CONTROL permission for the database to which they would like to add the audit.
User defined audit events
One of the new 2012 features is the ability to create User Defined Audit Events. User defined audit events can be used to integrate third party applications to SQL Server Audit.
A user defined audit event is created by using the sp_audit_write procedure. This procedure accepts 3 parameters:
In order for a user defined event to be audited, the USER_DEFINED_AUDIT_GROUP needs to be selected for audit in either the database or server audit specification.
If this has not been selected as an audit event, all events generated by the sp_audit_write procedure will be ignored.
Reading audit file data
When auditing information is written to a file target it is done in binary. The table-valued function fn_get_audit_file() need to be used to read it.
This function accepts 3 parameters:
Example:
|
1 2 3 |
SELECT * FROM fn_get_audit_file('D:\Audits\*',default,default) |
The file can contain any of the 26 available elements. A complete list of the available elements can be found here: SQL Server Audit Records
Audit information written to the Windows Security Log or the Application Log can we read using event viewer. This information can also be read through the SQL Server Management studio by expanding the security node, then expanding the Audit node, right click on an Audit and select the option View Audit Logs
Securing the audit logs
The audit logs themselves need to be protected from unauthorized access and modification.
There are two ways to increase the security of the audit logs:
Restarting a SQL Server after a forced shutdown
If SQL Server was shut down by SQL audit, it will not start up normally. It needs to be restarted in single user mode using the –m trace flag. Alternatively, it can also be started in minimal configuration mode using the –f flag.
This will then allow the DBA to make modifications to the audit if it is required.
SQL Server will write the MSG_AUDIT_SHUTDOWN_BYPASSED message to the error log if auditing was bypassed in this way.
Best Practices
Conclusion
SQL Server Auditing is a powerful feature, but should not be used without careful planning and consideration. In order to use Auditing successfully, you need to have a very clear idea of what you hope to achieve. Which actions need to be audited? Who needs access to this information? How will it be accessed? A large part of successful auditing depends on how the audit data is stored, processed and monitored.
In addition to planning, more work may be required to create reports which can be used for auditors to make sense of this information.
References:
See more
To audit SQL database and security activities, consider ApexSQL Audit, an enterprise level SQL Server auditing tool.
Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.
View all posts by Minette Steynberg