Passive security refers to the practice of logging user activity in order to avoid the threat of non-repudiation. This is important, because if an attack is launched by a privileged user, it allows for appropriate disciplinary or even legal action to be taken. SQL Server provides SQL Server Audit to assist with implementing passive security. In this chapter, we will discuss the concepts involved in auditing, before demonstrating how to implement SQL Server Audit, including the creation of custom audit event.
Understanding SQL Server Audit
SQL Server Audit offers DBAs the ability to capture granular information about activity at both the instance level and database level. Audit logs can be saved to a file, the Windows Security log, or the Windows Application log. The location that the audit logs are saved to is known as the target. There will be exactly one target associated with each audit.
The SQL Server Audit resides at the instance level and defines the properties of the audit and the target. It is possible to create multiple server audits in each instance. This is useful if you have to audit many events in a busy environment, as it allows you to distribute the IO by using file targets and placing each target file on a separate volume.
Choosing the correct target is an important security consideration. If you choose the Windows Application log as a target, then any Windows user who is authenticated to the server is able to access it. The Security log is a lot more secure than the Application log but also more complex to configure as a target for SQL Server Audit logs.
When using the Security log as a target, the service account that is running the SQL Server service requires the Generate Security Audits user rights assignment. This can be assigned from local security policy of the server but ideally will be configured at a group policy level to avoid the risk of a GPO (Group Policy Object) change overriding the setting. Application-generated auditing also needs to be enabled.
Another consideration for the target is size. If you decide to use the Application log or Security log, then it is important that you consider, and potentially increase, the size of these logs before you begin using them for your audit. Also, work with your Windows administration team to decide on how the log will be cycled when full and if you will be archiving the log by backing it up to tape.
The SQL Server Audit can be associated with one or more Server Audit Specifications and Database Audit Specifications. Specifications define the activity that will be captured by the audit at the instance level and the database level, respectively. It is helpful to have multiple server and/or database audit specifications if you are planning to log many actions, because you can categorize them to make administration easier, while still associating them with the same server audit. Each database within the instance must have its own Database Audit Specification, if you plan to audit activity across multiple databases.
SQL Server Audit Actions and Action Groups
SQL Server Audit events are based on the SQL Server Event Classes. Related actions are grouped together, into Audit Action Groups. These Audit Action Groups map to SQL Server Event Class Categories. When creating a Server Audit Specification or Database Audit Specification, you will configure the audit specification to capture Audit Action Groups, which contain the events that you wish to capture.
Audit Groups are available at three distinct layers: Server (meaning instance), Database, and Audit. Providing the ability to audit changes to audits avoids the threat of non-repudiation caused by a privileged user launching an attack and attempting to cover their tracks, by changing the audit information that is logged.
Server Level Action Groups
Action Group | Description | Actions Contained |
---|---|---|
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP | The event is raised when an Application Role’s password is changed. | APPLICATION_ROLE_CHANGE_PASSWORD_GROUP |
AUDIT_CHANGE_GROUP | The event is raised when Audit is created, dropped, or altered. | CREATE ALTER DROP AUDIT SHUTDOWN ON FAILURE CREATE ALTER DROP AUDIT_CHANGE_GROUP |
BACKUP_RESTORE_GROUP | The event is raised when a BACKUP command or RESTORE command is issued. | RESTORE BACKUP_RESTORE_GROUP BACKUP BACKUP LOG |
BROKER_LOGIN_GROUP | The event is raised when Service Broker security events occur. | BROKER LOGIN BROKER_LOGIN_GROUP |
DATABASE_CHANGE_GROUP | The event is raised when a database is created, dropped, or altered. | DATABASE_CHANGE_GROUP CREATE ALTER DROP |
DATABASE_LOGOUT_GROUP | The event is raised when a user without a login logs out of a database. | DATABASE LOGOUT DATABASE_LOGOUT_GROUP |
DATABASE_MIRRORING_LOGIN_GROUP | The event is raised when Database Mirroring related security events occur. | DATABASE MIRRORING LOGIN DATABASE_MIRRORING_LOGIN_GROUP |
DATABASE_OBJECT_ACCESS_GROUP | The event is raised when non-schema bound database objects are accessed. | DATABASE_OBJECT_ACCESS_GROUP |
DATABASE_OBJECT_CHANGE_GROUP | The event is raised when non-schema bound database objects are created, dropped, or altered. | DATABASE_OBJECT_CHANGE_GROUP |
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a database object is changed. | DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP |
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked from a database object. | DATABASE_OBJECT_PERMISSION_CHANGE_GROUP |
DATABASE_OPERATION_GROUP | The event is raised when SQL Server background operational tasks, such as a CHECKPOINT, occur. | VIEW DATABASE STATE CONNECT DATABASE_OPERATION_GROUP CHECKPOINT SUBSCRIBE QUERY NOTIFICATION AUTHENTICATE SHOW PLAN |
DATABASE_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a database is changed. | TAKE OWNERSHIP DATABASE_OWNERSHIP_CHANGE_GROUP |
DATABASE_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to a principal within a database. | DATABASE_PERMISSION_CHANGE_GROUP REVOKE DENY GRANT GRANT WITH GRANT REVOKE WITH GRANT REVOKE WITH CASCADE DENY WITH CASCADE |
DATABASE_PRINCIPAL_CHANGE_GROUP | The event is fired when a principal is created, dropped, or altered within a database. | DATABASE_PRINCIPAL_CHANGE_GROUP |
DATABASE_PRINCIPAL_IMPERSONATION_GROUP | The event is fired when the impersonation of a database-level principal occurs. | DATABASE_PRINCIPAL_IMPERSONATION_GROUP |
DATABASE_ROLE_MEMBER_CHANGE_GROUP | The event is raised when the membership of a database role is changed. | DATABASE_ROLE_MEMBER_CHANGE_GROUP |
DBCC_GROUP | The event is raised when a DBCC statement is run. | DBCC DBCC_GROUP |
FAILED_DATABASE_AUTHENTICATION_GROUP | The event is raised when a user attempts to authenticate to a contained database, but the authentication fails. | FAILED_DATABASE_AUTHENTICATION_GROUP DATABASE AUTHENTICATION FAILED |
FAILED_LOGIN_GROUP | The event is raised when an attempt to authenticate to the instance fails. | LOGIN FAILED |
FULLTEXT_GROUP | The event is raised when full-text events occur. | FULLTEXT FULLTEXT_GROUP |
LOGIN_CHANGE_PASSWORD_GROUP | The event is raised when a Login’s password is changed. | RESET PASSWORD RESET OWN PASSWORD CHANGE OWN PASSWORD CHANGE PASSWORD UNLOCK ACCOUNT MUST CHANGE PASSWORD |
LOGOUT_GROUP | The event is raised when a principal logs out of the instance. | LOGOUT |
SCHEMA_OBJECT_ACCESS_GROUP | The event is raised when an object permission is used for a schema. | SELECT INSERT UPDATE DELETE REFERENCES EXECUTE RECEIVE VIEW CHANGETRACKING SCHEMA_OBJECT_ACCESS_GROUP |
SCHEMA_OBJECT_CHANGE_GROUP | The event is raised when a schema is created, dropped, or altered. | SCHEMA_OBJECT_CHANGE_GROUP |
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a schema-bound object is changed. | SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP |
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to a schema-bound object. | SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP |
SERVER_OBJECT_CHANGE_GROUP | The event is raised when an instance-level object is created, dropped, or altered. | ALTER BACKUP CREATE CREDENTIAL MAP TO LOGIN DROP NO CREDENTIAL MAP TO LOGIN RESTORE |
SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of an instance-level object is changed. | TAKE OWNERSHIP |
SERVER_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to an instance-level object. | DENY DENY WITH CASCADE GRANT GRANT WITH GRANT REVOKE REVOKE WITH CASCADE REVOKE WITH GRANT |
SERVER_OPERATION_GROUP | The event is raised when instance configuration changes are made. | ALTER ALTER RESOURCES CREATE DROP |
SERVER_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to instance-level permissions. | DENY DENY WITH CASCADE GRANT GRANT WITH GRANT REVOKE REVOKE WITH CASCADE REVOKE WITH GRANT SERVER_PERMISSION_CHANGE_GROUP |
SERVER_PRINCIPAL_CHANGE_GROUP | The event is fired when instance-level principals are created, dropped, or altered. | ALTER CHANGE DEFAULT DATABASE CHANGE DEFAULT LANGUAGE CHANGE LOGIN CREDENTIAL CREATE DISABLE DROP ENABLE NAME CHANGE PASSWORD EXPIRATION PASSWORD POLICY |
SERVER_PRINCIPAL_IMPERSONATION_GROUP | The event is raised when impersonation of an instance-level principal occurs. | IMPERSONATE |
SERVER_ROLE_MEMBER_CHANGE_GROUP | The event is raised when the membership of a server role is changed. | ADD MEMBER DROP MEMBER |
SERVER_STATE_CHANGE_GROUP | The event is raised when the state of the instance is modified. | SERVER CONTINUE SERVER PAUSED SERVER SHUTDOWN SERVER STARTED SERVER_STATE_CHANGE_GROUP |
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP | The event is raised when a principal successfully authenticates to a contained database. | DATABASE AUTHENTICATION SUCCEEDED SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP |
SUCCESSFUL_LOGIN_GROUP | The event is raised when a principal successfully authenticates to the instance. | LOGIN SUCCEEDED |
TRACE_CHANGE_GROUP | The event is raised if a trace is modified. | ALTER TRACE TRACE AUDIT C2OFF TRACE AUDIT C2ON TRACE AUDIT START TRACE AUDIT STOP TRACE_CHANGE_GROUP |
TRANSACTION_GROUP | The event is raised when a transaction begins, commits, or rolls back. | STATEMENT_ROLLBACK_GROUP TRANSACTION_BEGIN_GROUP TRANSACTION_COMMIT_GROUP TRANSACTION_GROUP TRANSACTION_ROLLBACK_GROUP |
USER_CHANGE_PASSWORD_GROUP | The event is raised when a user with password’s password is changed. | USER_CHANGE_PASSWORD_GROUP |
USER_DEFINED_AUDIT_GROUP | The event is triggered when the sp_audit_write procedure is executed. | USER DEFINED AUDIT USER_DEFINED_AUDIT_GROUP |
Database-Level Audit Action Groups
Action Group | Description | Actions Contained |
---|---|---|
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP | The event is triggered when an application role’s password is changed. | APPLICATION_ROLE_CHANGE_PASSWORD_GROUP |
AUDIT_CHANGE_GROUP | The event is raised when an audit is created, dropped, or altered. | CREATE ALTER DROP AUDIT SHUTDOWN ON FAILURE CREATE ALTER DROP AUDIT_CHANGE_GROUP |
BACKUP_RESTORE_GROUP | The event is triggered when a database is backed up or restored. | RESTORE BACKUP_RESTORE_GROUP BACKUP BACKUP LOG |
DATABASE_CHANGE_GROUP | The event is raised when a database is created, dropped, or altered. | DATABASE_CHANGE_GROUP CREATE ALTER DROP |
DATABASE_LOGOUT_GROUP | The event is raised when a user without a login logs out of a database. | DATABASE LOGOUT DATABASE_LOGOUT_GROUP |
DATABASE_OBJECT_ACCESS_GROUP | The event is raised when non-schema-bound database objects are accessed. | DATABASE_OBJECT_ACCESS_GROUP |
DATABASE_OBJECT_CHANGE_GROUP | The event is raised when non-schema bound database objects are created, dropped, or altered. | DATABASE_OBJECT_CHANGE_GROUP |
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a database is changed. | DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP |
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned or revoked to a principal within a database. | DATABASE_OBJECT_PERMISSION_CHANGE_GROUP |
DATABASE_OPERATION_GROUP | The event is raised when SQL Server background operational tasks, such as a CHECKPOINT, occur. | VIEW DATABASE STATE CONNECT DATABASE_OPERATION_GROUP CHECKPOINT SUBSCRIBE QUERY NOTIFICATION AUTHENTICATE SHOW PLAN |
DATABASE_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a database is changed. | TAKE OWNERSHIP DATABASE_OWNERSHIP_CHANGE_GROUP |
DATABASE_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned to or revoked from a principal within a database. | DATABASE_PERMISSION_CHANGE_GROUP REVOKE DENY GRANT GRANT WITH GRANT REVOKE WITH GRANT REVOKE WITH CASCADE DENY WITH CASCADE |
DATABASE_PRINCIPAL_CHANGE_GROUP | The event is fired when a principal is created, dropped, or altered within a database. | DATABASE_PRINCIPAL_CHANGE_GROUP |
DATABASE_PRINCIPAL_IMPERSONATION_GROUP | The event is fired when the impersonation of a database-level principal occurs. | DATABASE_PRINCIPAL_IMPERSONATION_GROUP |
DATABASE_ROLE_MEMBER_CHANGE_GROUP | The event is raised when the membership of a database role is changed. | DATABASE_ROLE_MEMBER_CHANGE_GROUP |
DBCC_GROUP | The event is raised when a DBCC statement is run. | DBCC DBCC_GROUP |
FAILED_DATABASE_AUTHENTICATION_GROUP | The event is raised when a user attempts to authenticate to a contained database, but the attempt fails. | FAILED_DATABASE_AUTHENTICATION_GROUP DATABASE AUTHENTICATION FAILED |
SCHEMA_OBJECT_ACCESS_GROUP | The event is raised when an object permission is used for a schema. | SELECT INSERT UPDATE DELETE REFERENCES EXECUTE RECEIVE VIEW CHANGETRACKING SCHEMA_OBJECT_ACCESS_GROUP |
SCHEMA_OBJECT_CHANGE_GROUP | The event is raised when a schema is created, dropped, or altered. | SCHEMA_OBJECT_CHANGE_GROUP |
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP | The event is raised when the owner of a schema-bound object is changed. | SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP |
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP | The event is raised when permissions are assigned to or revoked from a schema-bound object. | SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP |
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP | The event is raised when a principal successfully authenticates to a contained database. | DATABASE AUTHENTICATION SUCCEEDED SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP |
USER_CHANGE_PASSWORD_GROUP | The event is raised when a user with password’s password is changed. | USER_CHANGE_PASSWORD_GROUP |
USER_DEFINED_AUDIT_GROUP | The event is triggered when the sp_audit_write procedure is executed. | USER DEFINED AUDIT USER_DEFINED_AUDIT_GROUP |
Audit-Level Audit Action Groups
Action Group | Description | Actions Contained |
---|---|---|
AUDIT_ CHANGE_GROUP | The event is fired when a SQL Server Audit artifact is created, dropped, or altered. | CREATE SERVER AUDIT ALTER SERVER AUDIT DROP SERVER AUDIT CREATE SERVER AUDIT SPECIFICATION ALTER SERVER AUDIT SPECIFICATION DROP SERVER AUDIT SPECIFICATION CREATE DATABASE AUDIT SPECIFICATION ALTER DATABASE AUDIT SPECIFICATION DROP DATABASE AUDIT SPECIFICATION |
Implementing SQL Server Audit
The following sections will discuss how to create a Server Audit, a Server Audit Specification, and a Database Audit Specification.
Creating a Server Audit
Server Audit Options
Option | Description |
---|---|
FILEPATH | Specifies the file path, where the audit logs will be generated. Only applies if you choose a file target. |
MAXSIZE | Specifies the largest size that the audit file can grow to. The minimum size you can specify for this is 2MB. Only applies if you choose a file target. |
MAX_ROLLOVER_FILES | When the audit file becomes full, you can either cycle that file or generate a new file. The MAX_ROLLOVER_FILES setting controls how many new files can be generated before they begin to cycle. The default value is UNLIMITED, but specifying a number caps the number of files to this limit. If you set it to 0, then there will only ever be one file, and it will cycle every time it becomes full. Any value above 0 indicates the number of rollover files that will be permitted. So, for example, if you specify 5, then there will be a maximum of six files in total. Only applies if you choose a file target. |
MAX_FILES | As an alternative to MAX_ROLLOVER_FILES, the MAX_FILES setting specifies a limit for the number of audit files that can be generated, but when this number is reached, the logs will not cycle. Instead, the audit fails and events that cause an audit action to occur are handled based on the setting for ON_FAILURE. Only applies if you choose a file target. |
RESERVE_DISK_SPACE | Pre-allocate space on the volume equal to the value set in MAXSIZE, as opposed to allowing the audit log to grow as required. Only applies if you choose a file target. |
QUEUE_DELAY | Specifies if audit events are written synchronously or asynchronously. If set to 0, events are written to the log synchronously. Otherwise, specify the duration in milliseconds that can elapse before events are forced to write. The default value is 1,000 (1 second), which is also the minimum value. |
ON_FAILURE | Specifies what should happen if events that cause an audit action fail to be audited to the log. Acceptable values are CONTINUE, SHUTDOWN, or FAIL_OPERATION. When CONTINUE is specified, the operation is allowed to continue. This can lead to unaudited activity occurring. FAIL_OPERATION causes auditable events to fail, but allows other actions to continue. SHUTDOWN forces the instance to stop if auditable events cannot be written to the log. |
AUDIT_GUID | Because server and database audit specifications link to the server audit through a GUID, there are occasions when an audit specification can become orphaned. These include when you attach a database to an instance, or when you implement technologies such as AlwaysOn Availability Groups. This option allows you to specify a specific GUID for the server audit, as opposed to having SQL Server generate a new one. |
It is also possible to create a filter on the server audit. This will be demonstrated in the next example. Filters are useful when your Audit Specification captures activity against an entire class of object but you are only interested in a subset of this information. For example, you may configure a Server Audit Specification to log members being added to or removed from server roles, but really, you are only interested in members being added to or removed from the sysadmin server role. In this scenario, you can filter on the sysadmin role and reduce the amount of “noise” being recorded in the audit log.
Note
Please refer to Chapter 2 for further information on server roles.
The script in Listing 3-1 demonstrates how to create a Server Audit. The audit will use a file target and the target may consist of an unlimited number of files, although each file will be limited in size to 256 MB. The audit is configured, so that is the audit fails to log an operation, that operation will fail. We have also placed a filter on the audit, so that only activity where the object_name property is equal to sysadmin will be logged. This will allow us to create a Server Audit Specification, which checks for members being added to, or removed from, a server role, as discussed above.
Tip
If you are following along with the demonstrations, then you should change the file path to match your own configuration.
Create a Server Audit
Enabling an Audit
In this dialog box, we have given the Server Audit an appropriate name and left the default values for Queue delay and On Audit Log Failure. We have then ensured that File is selected in the Audit destination drop-down and entered a file path to our chosen location. Finally, we have specified the required maximum file size for each audit file.
Tip
Ensure the file path exists and that the Database Engine service account has appropriate permissions before creating the Server Audit.
Create a Server Audit Specification
Server Audit Specification Options
Argument | Description |
---|---|
audit_specification_name | The name to be assigned to the Server Audit Specification. |
audit_name | The name of the Server Audit, to which the specification will be associated. |
audit_action_group_name | The name of a group of related auditable actions at the instance level. |
STATE | Specifies if the Server Audit Specification should be started on creation. |
Create a Server Audit Specification
In this dialog box, we have specified an appropriate name for the Server Audit Specification and then selected the Server Audit that it should be linked to by choosing it from the Audit drop-down box. Finally, we have selected the appropriate Audit Action Type from the drop-down list.
Create a Database Audit Specification
Creating a Database Audit Specification is similar to creating a server audit specification but provides more flexibility, as you can specify filters, such as the securable or principal to be audited.
Database Audit Specification Options
Argument | Description |
---|---|
audit_specification_name | The name to be assigned to the Database Audit Specification |
audit_name | The name of the Server Audit, to which the specification will be associated |
action | The granular action to be audited |
audit_action_group_name | The name of a group of related auditable actions at the database level |
class | The class name of the securable |
securable | The name of the securable to be audited |
principal | The name of the principal to be audited |
STATE | Specifies if the Server Audit Specification should be started on creation. |
Create a Database Audit Specification
Enabling An Audit Specification
In this dialog box, we have given the Database Audit Specification an appropriate name and selected the Server Audit it should be linked to from the Audit drop-down list. We have then used the Audit Action Type and Object class drop-down boxes to specify that we want to audit data deletion, against a specific object.
The Object Name ellipse invokes a dialog box, which allows us to specify the table to audit. The Principal Name dialog ellipse invokes a similar dialog box, which allows us the select the security principal that will be audited.
Creating Custom Audit Events
There may be times when you want to use SQL Server Audit to capture very specific events that it is not possible to capture using the out-of-the-box functionality of SQL Server Audit. If this is the case, then you can create a Server Audit Specification, or Database Audit Specification, that is configured to capture the USER_DEFINED_AUDIT_GROUP Audit Action Group and then manually fire the event in your application code. The following sections demonstrate how to create the Server Audit and Database Audit Specification required to log sales orders, where more than five different items are ordered.
Creating the Server Audit and Database Audit Specification
Create the Server Audit and Database Audit Specification
Raising the Event
sp_audit_write Parameters
Parameter | Description |
---|---|
@user_defined_event_id | Specifies the ID of the user defined event |
@succeeded | Specifies if the event was successful. • 0 indicates that the event failed • 1 indicates that the event succeeded |
@user_defined_information | Specify the description of the event |
The sp_audit_write procedure can be called from a code module, such as a stored procedure or trigger. In our scenario, the table is updated from ad-hoc SQL, within the Sales application, so we will call the sp_audit_write procedure from inside a DML (Data Manipulation Language) trigger. Listing 3-7 demonstrates how to create the trigger.
Caution
DML triggers can cause a negative performance impact if they are created against a table that has many writes. They should be used with caution, and performance characteristics assessed, before being implemented in a production environment.
Create a Trigger to Fire the Event
Summary
SQL Server Audit provides DBAs with a flexible and lightweight auditing mechanism. This is important to avoid issues of non-repudiation when privileged users perform unauthorized actions.
An Audit object is used to configure the target. It is also used to specify the behaviors of the audit, such as what should happen if SQL Server Audit fails to write an event to the audit log. Multiple Audits can exist on an instance.
Server Audit Specifications and Database Audit Specifications are used to define what events should be audited. Multiple Server Audit Specifications and Database Audit Specifications can be associated with a single Audit.
SQL Server Audit is made extensible by the Audit Action Group USER_DEFINED_AUDIT_GROUP This action group enables custom events to be fired. Custom events are triggered by calling the sp_audit_write system stored procedure. This procedure can be called from a code module, such as a stored procedure or trigger, and allow DBAs to capture events that are specific to their environments and that cannot be captured through out-of-the-box functionality.