Auditing SQL Server Instances
- SQL Audit enables us to track specific actions on the instance or database level.
- SQL Audit can write audit data to the Windows Security or Windows Application log.
- Audit data can also be written to a normal file.
- An audit can be configured so that the instance shuts down in the event of an audit failure.
- Action groups and actions determine which activity is audited.
- We can create a server or database audit specification only after a server audit has been configured.
- SQL Server Audit provides sophisticated auditing at the instance and database levels.
- SQL Server Audit forwards audit results to a target, which can be either a flat file or the Windows Security or Windows Application event logs on the host computer.
Creating a Server Audit
CREATE SERVER AUDIT [MY-AUDIT] TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = SHUTDOWN)
ALTER SERVER AUDIT [MY-AUDIT] WITH (STATE=ON)
- If the On Audit Log Failure option is set to Shutdown, an audit failure will cause the Database Engine instance to shut down.
- After the instance is shut down, it is possible that the Database Engine instance will be unable to start unless you start it using the -f option from the command line, which enables you to override the audit-triggered shutdown.
- You can also start an instance that has been shut down by SQL Server Audit in Single User mode. This is because, in single user mode, a server audit configured with the Shutdown option functions as though you had set the Continue option.
- When an audit is bypassed in this manner, the Database Engine writes a MSG_AUDIT_SHUTDOWN_BYPASSED message to the error log. Start SQL Server in Single-User Mode
If we are using SQL Server 2012 Enterprise edition, it can also configure audit specifications at the database level. The advantage being that we can configure auditing for specific databases rather than auditing for all databases.
We can configure certain audit action groups at the instance level, SQL Server records actions performed on all databases, not just on specific databases.
Creating a Server Audit Specification
Before we can create a server audit specification the server audit must be present and we need to specify the following items:
- Audit Name – A name for the server audit
- Queue Delay – The delay in milliseconds before audit actions must be processed. Default value is 1,000 milliseconds
- On Audit Log Failure – What to do when there is an audit log failure. The options are that operations continue, the Database Engine instance is shut down, or the audit simply fails and no event is written
- Audit Destination – Enables us to specify the Application log, Security log, or a file destination. If we specify a file destination, we must specify the file path, maximum number of files, and the maximum file size
The general process of using SQL Server Audit to audit activities at the instance level involves the following steps:
- Verify that a server audit is present.
- Create a server audit specification that maps to the audit.
- Enable the audit specification.
Use the Audit Action Type list to specify the server-level audit action groups you want to audit.
CREATE SERVER AUDIT SPECIFICATION [MY-SPECIFICATION] FOR SERVER AUDIT [MY-AUDIT] ADD (DATABASE_CHANGE_GROUP)
ALTER SERVER AUDIT SPECIFICATION [MY-SPECIFICATION] ADD (DATABASE_LOGOUT_GROUP)
Creating a Database Audit Specification
CREATE DATABASE AUDIT SPECIFICATION [Audit_Spec_1]
FOR SERVER AUDIT [Srv_Audit_1]
ADD (INSERT ON OBJECT::[dbo].[Paradigm] BY [Exemplar])
Example of how to use the select into get table spec
select * INTO SQLAudits from sys.fn_get_audit_file ( '<file_location>'<file_name_prefix>*.sqlaudit' , Default , Default )
To create, alter, or drop a database audit specification, security principals must have permission to connect to the database and be assigned either the ALTER ANY DATABASE AUDIT permission or the ALTER or CONTROL permissions on the database.
Viewing SQL Server Audit Views and Functions
- sys.dm_audit_actions - Stores information on each audit action and each audit action group that you can use with SQL Server Audit
- sys.server_audits – Enables you to see information about each SQL Server audit configured on an instance
- sys.dm_server_audit_status - Enables you to see information about currently configured audits
- sys.server_audit_specifications - Enables you to see information about server audits
- sys.server_audit_specifications_details - Enables you to see information about actions that are audited at the server level
- sys.database_audit_specifications - Enables you to see information about currently configured database audit specifications
- sys.database_audit_specifications_details – Enables you to see information about actions that are audited at the database level
- fn_get_audit_file - Enables you to query a file-based target for audit information
Using c2 Audit Mode
c2 audit mode configures the instance to record both successful and failed attempts to access statements and objects as defined by the c2 security standard.
sp_configure "show advanced options", 1; GO RECONFIGURE; GO sp_configure "c2 audit mode", 1; GO RECONFIGURE; GO
Common Criteria Compliance supersedes c2 audit mode and enables certain security options on a Database Engine instance, including login auditing information.
We can access this login information by querying the sys.dm_exec_sessions dynamic management view and enabling common criteria compliance on the Security page of the Server Properties dialog box.
We can also enable common criteria compliance by executing the following Transact-SQL statement:
sp_configure "show advanced options", 1; GO RECONFIGURE; GO sp_configure "common criteria compliance enabled", 1; GO RECONFIGURE; GO