SQL Server database security auditing

The following auditing implementations are recommended on a database level as part of any database security auditing system:

1. Schema level auditing:
   • DDL activity
   • Changes made to stored procedures and triggers
   • Changes to privileges, users, and security attributes
2. Data level auditing:
   • Changes to sensitive data (DML activity)
   • SELECT statements
3. Any changes of the auditing settings

There are some native database security auditing solutions that can help fulfilling these requirements.

Schema level auditing

DDL commands are from the security standpoint have a high potential for malicious use and can be easily used to compromise any database system.

There are few ways to audit DDL activity:

• SQL Server Audit feature or SQL Server trace files
• DDL triggers
• Schema snapshot comparison

Fort the purpose of this article, we will not consider DDL triggers and schema snapshot comparison in favor of SQL Server Audit and trace files.

The SQL Server Audit feature is native SQL Server auditing based on SQL Server extended events. Introduced with SQL Server 2008, it is the least intrusive auditing method and thus generally recommended for DDL activity auditing. It can store the audit events whenever they occur into the security log or the application event log, but the recommended method, which will be described in this article, is storing of audited events in the audit file.

Note: The database level activity auditing, and thus database security auditing using SQL Audit is reserved for SQL Server Enterprise and SQL Server Developer editions only.

To establish database security auditing using SQL Audit, the first step is creating the SQL Audit object. This can be done using T-SQL or via SQL Server Management Studio.

To create an SQL Audit object that will be used for database security auditing, the following T-SQL creation script can be used.

USE [master]
GO

CREATE SERVER AUDIT [Audit ADW2014 DDL ]
TO FILE 
(	FILEPATH = N'C:\SQLAudits\'
	,MAXSIZE = 20 MB
	,MAX_FILES = 20
	,RESERVE_DISK_SPACE = ON
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '928b1094-b02b-437d-a5c7-8266af853b57'
)
ALTER SERVER AUDIT [Audit ADW2014 DDL ] WITH (STATE = ON)
GO

To use SQL Server Management Studio, locate the Security->Audits folder in the Object Explorer and chose the New Auditfrom the context menu.

In the Create Audit dialog that will be opened, select the Audit destination->File from the drop-down menu and determine the File path that will be used for storing the file where the audits will be logged.

All options in this dialog are self-explanatory and don’t require additional explanations, but for those who are interested, more details can be found here.

Once the SQL Server Audit object is created, the Database Audit Specifications object for the created audit object has to be set.

To create the Database Audit Specifications object for the above-mentioned auditing requirements, the following T-SQL could be used.

USE [AdventureWorks2014]

GO

CREATE DATABASE AUDIT SPECIFICATION [ADW2014 Auditing]
FOR SERVER AUDIT [Audit ADW2014 DDL]
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OPERATION_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)

GO

The same could be done via SQL Server Management Studio’s Object Explorer. Expand the database object tree, right click on the Security->Database Audit Specifications and select the New Database Audit Specification.

More details about the Create Database Audit Specifications object is available here. Follow the links for additional details about Database-Level Audit Action Groups.

Note: The DATABASE_OBJECT_CHANGE_GROUP audits only the ALTER permission check on the SCHEMA as part of the CREATE statement. To actually audit CREATE, ALTER, or DROP operations on schema, the SCHEMA_OBJECT_CHANGE_GROUP must be added to the audit specification.

Once all the requirements for auditing were set, SQL Audit will start to collect and log every database structure change defined in the auditing specifications.

Data level auditing

Creating the SQL Audit and Database Audit Specifications objects for data is the same as the above and the only difference consists in setting different Database Audit Specifications.

After creating the new SQL Audit object for DML level auditing, that will be named Audit ADW2014 DML for the purpose of this article, it should be associated with the new Database Audit Specification object. For the purpose of the DML auditing, Database-Level Audit Actions SELECT, INSERT, UPDATE, DELETE and if needed EXECUTE operations have to be set for auditing. For more information visit Database-Level Audit Actions.

Since it is possible to specify just one object/principal per audit event, creating all the necessary audit specifications can be quite strenuous and cumbersome. This is especially evident when creating specifications for databases with even a bit larger amount of tables and views and when more principals have to be included in auditing. The above image is just an example where only a few specifications are defined.

Note: The audit type can be configured on Schema and Database object class, which means that all objects that belongs to the specified schema will be audited, as well as all objects within the selected database, if the database is specified as object class. This should be used carefully as torrent of unnecessary data may be collected and stored in the .audit files.

Audit the audit

The easiest way for attacker to stay unnoticed is to temporarily change the definition of auditing settings or to change the collected data itself. In order to allow tracking of changes to audit and audits specifications objects, or better say to “audit the audit”, SQL Server Audit Action Group AUDIT_CHANGE_GROUP is introduced starting with the SQL Server 2012. Besides tracking changes to audit and audits specifications objects, this group also track failed auditing and changes made to audit sessions. Once set on the Database Audit Specifications level, it will audit all changes made to database audit specifications within that database.

The AUDIT_CHANGE_GROUP audit action type will log an auditing event when any of the following commands are executed.

• Create server audit
• Create server audit specification
• Create database audit specification
• Alter server audit specification
• Alter database audit specification
• Alter server audit
• Drop server audit
• Drop server audit specification
• Drop database audit specification

ApexSQL Audit

ApexSQL Audit is a SQL Server auditing and compliance tool capable to audit over 200 SQL events on SQL server and database level and to store them into a single tamper-evident central repository database. Collected information are easily available on request through eleven built-in reports with additional custom report that is designed around the advanced filtering which allows meeting even the most demanding user requirements.

With ApexSQL Audit, database security auditing of schema and data activity can be set in a single pass while all changes on the auditing settings will be tracked by default.

To do that in the ApexSQL Audit GUI using the simple filter:

1. Select the database(s) to be audited using Add database

   

2. Set the required auditing filter conditions for each database added for auditing

   

   Below is the list of all available database level auditing filter selections with an example configuration

   

3. Press Apply in the pop-up bar at the top of the tab and this is it… this is all that has to be done to set up the database security auditing

Alternatively, to set up the auditing via the advanced filter:

1. Check the Advanced radio button in the Filter type section

   

2. Press to add the condition
3. Select the Database name data field

   

4. Click on <empty>

   

5. Press again, select the Database operations data field and select the required operations, e.g. DDL, DML auditing etc.

   

6. Press Apply in the pop-up bar at the top of the tab

   

ApexSQL will now start to collect the audited events and to store the audited data into the central repository database

The requirements to track changes of the auditing settings is implemented by default in ApexSQL Audit and tracking the auditing settings cannot be disabled by user, making the auditing filters tamper evident. Every change in auditing setting along with precise information about the filter settings and who performed the change can be seen in the Audit settings history report

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.