Audit on SQL Server (Server Audit)

Views: 6.570
This post is the 2 part of 15 in the series. Security and Audit
Reading Time: 4 minutes

Hello guys!
Good night!

In this post I will demonstrate how to create Server Audit in SQL Server. This feature is very interesting for auditing DDL and / or DML (Database Audit Specification) actions performed by a user or application or even the instance itself (Server Audit Specification).

Creating an interface audit (GUI)

Open SQL Server Management Studio, select the Security category> Audits> Right Click> Select “New Audit…”

SQL Server - Server Audit 2

When you set auditing, recorded data can be saved in 3 ways:

  • File: A physical file is generated on disk containing the data collected by the audit.
  • Security Log: Data collected by the audit is stored in the server security log.
  • Application Log: Data collected by the audit is stored in the server application log.

In this post, I will choose to burn to physical file on disk. By selecting this option you can still specify the file name where it will be saved, the maximum size and choose whether you already want to pre-reserve this maximum disk space (ie if you choose the maximum size of 10 GB, SQL Server will already create this file with allocated space 10 GB)

SQL Server - Create Audit

After the Audit is created, you must enable it by right-clicking it in Object Explorer and then selecting the “Enable Audit” option.

SQL Server - Enable Audit

SQL Server - Enable Audit 2

Creating a server-level audit (Server Audit Specification)

Now that the audit object has been created, we can create the audit definition object (Server Audit Specification).

Select instance and select Security category> Server Audit Specification> Right Click> Select “New Server Audit Specification”

SQL Server - Server Audit

In this screen, you can define what you want to monitor, as shown below:

SQL Server - Server Audit Specification

View all types of server level audits

Action Group Name

Description

APPLICATION_ROLE_CHANGE_PASSWORD_GROUP

This event is raised whenever a password is changed by an application role. Equivalent to Audit App Role Change Password Event Class.

AUDIT_CHANGE_GROUP

This event is raised whenever an audit is created, modified, or deleted. This event is raised whenever any audit specification is created, modified, or deleted. Any change to an audit is audited in that audit. Equivalent to Audit Change Audit Event Class.

BACKUP_RESTORE_GROUP

This event is raised whenever a backup or restore command is issued. Equivalent to Audit Backup / Restore Event Class.

BROKER_LOGIN_GROUP

This event is raised to report Service Broker transport security-related audit messages. Equivalent to Audit Broker Event Class Login.

DATABASE_CHANGE_GROUP

This event is raised when a database is created, altered, or dropped. This event is raised whenever a database is created, altered, or dropped. Equivalent to Audit Database Management Event Class.

DATABASE_MIRRORING_LOGIN_GROUP

This event is raised to report audit messages related to database mirroring transport security. Equivalent to Audit Database Mirroring Event Class Login.

DATABASE_OBJECT_ACCESS_GROUP

This event is raised whenever database objects such as message type, assembly, contract are accessed.

It is generated for any access to any database.

Observation
This could potentially generate large audit records.

Equivalent to Audit Database Object Access event class.

DATABASE_OBJECT_CHANGE_GROUP

This event is raised when a CREATE, ALTER, or DROP statement is executed on database objects, such as schemas. It is generated whenever a database object is created, altered, or dropped.

Observation
This could lead to very large volumes of audit records.

Equivalent to Audit Database Object Management Event Class.

DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP

This event is raised when there is an owner change for objects within the scope of the database. It is generated for any object ownership change in any database on the server. Equivalent to Audit Database Object Take Ownership Event Class.

DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

This event is raised when GRANT, REVOKE, or DENY is raised for database objects such as assemblies and schemas. It is generated for any object permission changes in any database on the server. Equivalent to Audit Database Object GDR event class.

DATABASE_OPERATION_GROUP

This event is raised when operations against a database occur, such as a checkpoint or subscription query notification. This event is raised on any database operation in any database. Equivalent to Audit Database Operation event class.

DATABASE_OWNERSHIP_CHANGE_GROUP

This event is raised when the ALTER AUTHORIZATION statement is used to change the owner of a database and the permissions required to do so are marked. It is generated for any database ownership change on any database on the server. Equivalent to Audit Change Database Owner Event Class.

DATABASE_PERMISSION_CHANGE_GROUP

This event is raised whenever GRANT, REVOKE, or DENY is issued for a statement permission by any entity in SQL Server (This applies to database-only events, such as granting permissions on a database, for example.).

This event is raised for any database permission change (GDR) of any database on the server. Equivalent to Audit Database Scope GDR event class.

DATABASE_PRINCIPAL_CHANGE_GROUP

This event is raised when entities, such as users, are created, changed, or dropped from a database. Equivalent to Audit Database Principal Management Event Class. (Also equivalent to the Audit Add DB Principal event class, which occurs in the replaced sp_grantdbaccess, sp_revokedbaccess, sp_addPrincipal, and sp_dropPrincipal stored procedures.)

This event is raised whenever a database role is added or removed through the sp_addrole and sp_droprole stored procedures. This event is raised whenever any database entity is created, altered, or dropped from any database. Equivalent to Audit Add Role Event Class.

DATABASE_PRINCIPAL_IMPERSONATION_GROUP

This event is raised when there is a database scope impersonation operation such as EXECUTE AS <entity> or SETPRINCIPAL. It is generated for representations made in any database. Equivalent to Audit Database Main Event Class Impersonation.

DATABASE_ROLE_MEMBER_CHANGE_GROUP

This event is raised whenever a login is added or removed from a database role. This event class is generated for the sp_addrolemember, sp_changegroup, and sp_droprolemember stored procedures. This event is raised on any change to the Database role member in any database. Equivalent to Audit Add Member to DB Role Event Class.

DBCC_GROUP

This event is raised whenever an entity issues any DBCC commands. Equivalent to Audit DBCC event class.

FAILED_LOGIN_GROUP

Indicates that an entity attempted to log on to SQL Server and failed. Events in this class are generated by new connections or reused connections from a connection pool. Equivalent to Audit Login Failed Event Class.

FULLTEXT_GROUP

Indicates that a full text event has occurred. Equivalent to Audit Fulltext Event Class.

LOGIN_CHANGE_PASSWORD_GROUP

This event is raised whenever a login password is changed by the ALTER LOGIN statement or the sp_password stored procedure. Equivalent to Audit Login Change Password Event Class.

LOGOUT_GROUP

Indicates that an entity has logged off to SQL Server. Events in this class are generated by new connections or reused connections from a connection pool. Equivalent to Audit Logout Event Class.

SCHEMA_OBJECT_ACCESS_GROUP

This event is raised whenever an object permission is used on the schema. Equivalent to Audit Schema Object Access event class.

SCHEMA_OBJECT_CHANGE_GROUP

This event is raised when a CREATE, ALTER, or DROP operation is performed on a schema. Equivalent to Audit Schema Object Management event class.

This event is raised on schema objects. Equivalent to Audit Object Derived Permission Event Class.

This event is raised whenever any schema in any database is changed. Equivalent to Audit Statement Permission Event Class.

SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP

This event is raised when permissions to change the schema object owner (such as a table, procedure, or function) are checked. This occurs when the ALTER AUTHORIZATION statement is used to assign an owner to an object. This event is raised for any schema property changes in any server database. Equivalent to Audit Schema Object Take Ownership Event Class.

SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

This event is raised whenever a grant, deny, or revoke is performed against a schema object. Equivalent to Audit Schema Object GDR event class.

SERVER_OBJECT_CHANGE_GROUP

This event is raised for CREATE, ALTER, or DROP operations on server objects. Equivalent to Audit Server Object Management Event Class.

SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP

This event is raised when the owner changes to server-scoped objects. Equivalent to Audit Server Object Take Ownership Event Class.

SERVER_OBJECT_PERMISSION_CHANGE_GROUP

This event is raised when GRANT, REVOKE, or DENY is issued for a server object permission on any entity in SQL Server. Equivalent to Audit Server Object GDR event class.

SERVER_OPERATION_GROUP

This event is raised when security audit operations such as changing settings, features, external access, or authorization are used. Equivalent to Audit Server Operation Event Class.

SERVER_PERMISSION_CHANGE_GROUP

This event is raised when GRANT, REVOKE, or DENY is issued for server scope permissions, such as creating a login. Equivalent to Audit Server Scope GDR event class.

SERVER_PRINCIPAL_CHANGE_GROUP

This event is raised when server entities are created, changed, or dropped. Equivalent to Audit Server Principal Management Event Class.

This event is raised when an entity issues sp_defaultdb or sp_defaultlanguage stored procedures or ALTER LOGIN statements. Equivalent to Audit Addlogin Event Class.

This event is raised in the sp_addlogin and sp_droplogin stored procedures. Also equivalent to Audit Login Change Property Event Class.

This event is raised for the sp_grantlogin, sp_revokelogin, or sp_denylogin stored procedures. Equivalent to Audit Login GDR Event Class.

SERVER_PRINCIPAL_IMPERSONATION_GROUP

This event is raised when there is a representation within the scope of the server, such as EXECUTE AS <logon>. Equivalent to Audit Server Principal Impersonation Event Class.

SERVER_ROLE_MEMBER_CHANGE_GROUP

This event is raised whenever a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures. Equivalent to Audit Event Class Add Login to Server Role.

SERVER_STATE_CHANGE_GROUP

This event is raised when the state of the SQL Server service is modified. Equivalent to Audit Server Starts and Stops Event Class.

SUCCESSFUL_LOGIN_GROUP

Indicates that the entity was successfully logged on to SQL Server. Events in this class are generated by new connections or reused connections from a connection pool. Equivalent to Audit Login event class.

TRACE_CHANGE_GROUP

This event is raised for all statements that check the ALTER TRACE permission. Equivalent to Audit Server Alter Trace event class.

After choosing the audit types (in this example, I want to audit database creation / deletion), click OK and enable the audit specification:

SQL Server - Server Audit Specification Enable

Creating a database audit (Database Audit Specification)

A very nice feature that can often replace the use of triggers is the creation of database level audit routines. In this example, I will create an audit to record changes made to a specific table.

To create a database level audit, select the instance and then the database to be audited, expand the Security category, select the Database Audit Specifications subcategory, right-click and select the “New Database” option. Audit Specification ”

SQL Server - Database Audit

In this screen we can define what we want to audit in this database.

SQL Server - Database New Audit

SQL Server Database Audit Specification

In the above configuration, I set to audit the following operations:
- DELETEs performed in the database Tests by the user “Usuario_Teste”
- INSERTs made in the Customers table made by any user.
- INSERTs into any dbo schema object made by any user.

After configuring, be sure to enable auditing by right-clicking on it and selecting “Enable Database Audit Specification” and your auditing is enabled!

Viewing collected data

To view the data that has been collected by the Audit, simply right click on the Audit (not Specification) and select the option “View Audit Logs”

SQL Server - View Audit Logs

SQL Server Database Audit Specification View Log

Creating a command line audit

After all this post explaining how to create database and server audits using the SQL Server Management Studio screens, I will briefly explain how to create these same audits via command line, which is a much more practical way to replicate across multiple servers and fix more. knowledge of the DBA.

With the commands below, I already create auditing (and active), server and database auditing (both already enabled as well).

Viewing the logs

Using simple queries, we can easily read the monitored data and work with this information:

And the visualized data looks like this:
SQL Server - View SQL Query audit logs

Thank you and see you next time!