This article first appeared in the SQLServerGeeks Magazine.
Author: Josephine Bush
Subscribe to get your copy.
In this article, you will learn how to setup SQL Server Audit with SQL scripts. For a summary of what auditing is and how to use SQL Server Management Studio to create SQL Server Audit via the GUI, please refer to the July edition of SQLServerGeeks magazine.
To audit SQL Server, you can use built-in functionality called SQL Server audit. It’s good for capturing pretty much anything that happens on the database server. It’s very flexible and pretty easy to set up. I primarily use this for seeing any permissions or schema changes. You access it via SSMS and can also use scripts to set up and query the audit data. This article will only cover setting it up via SQL scripts. For a quick review of when and how you can use SQL Server audit, let’s go over a few things first.
SQL Server Audit Availability
The first version that SQL Server audit was available in was 2008, and it was only in Enterprise edition. As of the 2012 version, Microsoft expanded it to be available at the server level with all editions, and then database auditing was still only in Enterprise. By 2016 and onwards, you can do server and database auditing with any edition, so much nicer since a lot of people don’t only use Enterprise. Just note these limitations if you are on older versions and wonder why you might not be able to do everything outlined in this article.
Audit Requirements
To make SQL Server auditing work you need two or three things depending on what you want to audit.
• You’re required to create an audit specification, and this will determine where you store audit data.
• You will also need one server and/or one database audit for audit data to write to the audit specification. Each audit specification can have one server and one database audit. Those server and database audits are not dependent on each other.
SQL Server Audit Use Cases
The server audit is generally good for:
• Auditing server-level changes and/or all the databases at the same time.
The database audit is good for:
• Auditing one database or a subset of activities in one database.
Let’s go through some examples so that you can see how that works.
SQL Server Audit Setup via SQL scripts
First, you need to create an audit specification. This is where your audit data will be stored. No audit data collects without this, and you can’t setup a server or database audit without first having an audit specification. You can setup an audit specification with the following script:
USE [master] CREATE SERVER AUDIT [AuditSpecification] TO FILE (FILEPATH = N'E:\sqlaudit\',MAXSIZE = 50 MB ,MAX_FILES = 4 ,RESERVE_DISK_SPACE = OFF ) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE) WHERE (server_principal_name <> 'monitoringserviceaccount' AND server_principal_name <> 'builtinsqlserveraccount' AND schema_name <> 'sys') ALTER SERVER AUDIT [AuditSpecification] WITH (STATE = ON)
Suggestions on how to configure your audit specification:
• Audit name: I tend to name it AuditSpecification or AuditSpecification_servername.
• QUEUE_DELAY: I always leave it at 1000. It’s just the wait time in milliseconds before it audits. I never see any lag. You can set this to 0 or 1000 and greater (i.e. 0 or 1000 or 1500 or 10000).
• On Audit Log Failure: I always set this to CONTINUE. I think fail operation and shut down server are too drastic. I’ll just have a bunch of people screaming at me that there’s something wrong with the server. You may want to choose fail operation or shut down server if auditing is of the utmost importance like on legal or financial reporting systems.
o CONTINUE – If the audit can’t capture the statement, it keeps auditing. You might miss a statement here and there, but I doubt that happens very often, if at all.
o FAIL_OPERATION – If it can’t audit, it’s going to cause the statement to fail.
o SHUTDOWN – If it can’t audit, it’s going to do what it says, it’s going to shut down the server.
• Audit destination:
o FILE – I always write to the file choice because it’s easiest for me. I don’t have a lot of auditing restrictions. Yes, the auditors want to know what happened. They don’t think that we’re going in there secretly deleting audit files and not reporting what we said we would
o APPLICATION_LOG – I could see application log writing if you have a Splunk application that reads all the logs and gathers them in a central repository (see an example of this below this list)
o SECURITY_LOG – this has more restrictions than writing to the application log
FILEPATH: If you chose FILE for Audit Destination, then you need to choose a path. Make sure don’t put it on the C drive. Even though we’re going to limit the audit file sizes, you don’t want it accidentally filling up the C drive. I don’t usually put it on data drives or log drives either. We have an E drive for applications where I work, that’s a great place for it to go.
• I never let the audit just collect max files and max sizes. I’ve found 4 files of 50MB each is good for my needs when collecting permissions and schema changes. Your number and sizing of files depend on your needs. If you have maximum files, and they’re gigantic, then they’re next to impossible to query.
o MAX_FILES – 4
o MAXSIZE – 50 MB
• RESERVE_DISK_SPACE: Since my files are quite small, I don’t include this.
• WHERE clause for filtering: This is optional. I usually filter things out in here like SQL Server internal accounts or monitoring tool accounts that I don’t want to clutter up the audit data. There are a lot of different fields you can filter on. You can choose to exclude or include things just like in a regular WHERE clause.
• Finally, we are enabling this (with the ALTER SERVER AUDIT statement) as part of creating it, unlike with the GUI, where it’s disabled by default after creation.
There’s also the option to write to the application or security log as follows:
USE [master] CREATE SERVER AUDIT [AuditSpecification] TO APPLICATION_LOG WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE) ALTER SERVER AUDIT [AuditSpecification] WITH (STATE = ON)
If you instead wanted to write to the security log you would just change APPLICATION_LOG to SECURITY_LOG in the above code.
If you chose the file destination for your audit, once you enable it, an audit file is placed on disk. This is where the data will live for your server and database audits that you associate with that audit specification. As the data collects, this file is going to grow to 50 MB because that’s what I specified in the configuration, and then it’ll create another file up to four (again because I specified four files). Once the fourth file is full, it will delete the oldest file and create another new file. That’s why you need to know how often you want to collect the data from the audit files so you don’t miss any data.
Audit Categories
Before we get into creating a server audit specification, let’s talk a bit about audit categories. There are three of them:
• Server level – gets everything at the server level, but you can also audit all the databases if you configure database actions at the server level. These capture permission changes and creating databases. Includes any action that doesn’t start with schema or database
• Database level – If you don’t want to audit all the databases the same way, then you can use the database level actions in each database to audit them separately. These include data manipulation languages (DML) and data definition language (DDL) changes. Namely things at the database level. Mainly, includes any action that starts with schema or database
• Audit level – audits the changes you make to the audit specifications. Turning an audit on or off is automatically captured and you don’t need to set that with the AUDIT_CHANGE_GROUP action.
NOTE: You don’t want the same database actions turned on at the server level and the database level because then you are getting duplicate audit records.
Creating a Server Audit
The first of two optional audits is the server audit specification. You can setup a server audit specification with the following script:
USE [master] CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification] FOR SERVER AUDIT [AuditSpecification] ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP), ADD (SERVER_OPERATION_GROUP), ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), ADD (LOGIN_CHANGE_PASSWORD_GROUP), ADD (SERVER_STATE_CHANGE_GROUP), ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (USER_CHANGE_PASSWORD_GROUP) WITH (STATE = ON)
Suggestions on how to configure your server audit specification:
• Name: I tend to name it ServerAuditSpecification or ServerAuditSpecification_servername.
• FOR SERVER AUDIT: You need to associate it with your audit specification. You need this because this is where your audit data will live.
• Actions with the ADD statement: I’m capturing perms and schema changes at the server level, and in all the databases on the server. That’s what these action types will do. If you just want to capture server changes, you’d remove all the ones starting with database and schema.
• We are also enabling as part of creating it, unlike with the GUI, where it’s disabled by default after creation.
Creating a Database Audit
The second of two optional audits is the database audit specification. You can setup a database audit specification with the following script:
USE [Auditing] CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification_Auditing] FOR SERVER AUDIT [AuditSpecification] ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (DBCC_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (USER_CHANGE_PASSWORD_GROUP) WITH (STATE = ON)
Suggestions on how to configure your database audit specification:
• Name: I always name it underscore database name because it helps identify what database it’s auditing like DatabaseAuditSpecification_Auditing.
• FOR SERVER AUDIT: You need to associate it with your audit specification. You need this because this is where your audit data will live.
• Actions with the ADD statement: I’m capturing perms and schema changes at the database level. Don’t use this if you’re already getting database and schema changes at the server audit level or you wind up with duplicate audit records.
• We are also enabling as part of creating it, unlike with the GUI, where it’s disabled by default after creation.
Where the database audit shines is when you want to audit objects because you can audit things like insert, update, delete, select, and execute on objects in the database, like tables, views, and stored procedures. These action types do require you to fill in all the additional columns for the audit actions depending on the object class.
Let’s look at an example. I create a separate audit specification for these types of auditing actions mainly because I don’t want them combined with my audit that collects permissions and schema changes since those are centralized for auditor reports.
USE [master] CREATE SERVER AUDIT [AuditSpecification_AuditingTables] TO FILE (FILEPATH = N'E:\sqlaudit\' ,MAXSIZE = 10 MB ,MAX_FILES = 10 ,RESERVE_DISK_SPACE = OFF ) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE) ALTER SERVER AUDIT [AuditSpecification_AuditingTables] WITH (STATE = ON)
USE [Auditing] CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification_Auditing Tables] FOR SERVER AUDIT [AuditSpecification_AuditingTables] ADD (INSERT ON OBJECT::[dbo].[testing] BY [public]), ADD (EXECUTE ON OBJECT::[dbo].[SelectTestingTable] BY [public]), ADD (SELECT ON OBJECT::[dbo].[TestingTop10] BY [public]), ADD (DELETE ON SCHEMA::[dbo] BY [auditing]), ADD (UPDATE ON DATABASE::[Auditing] BY [public]) WITH (STATE = ON)
Be very careful auditing entire schemas or databases. They can overload your audit and/or server.
Suggestions on how to configure your database audit specification when auditing specific objects, schemas, or databases:
• Name: I always name it underscore something descriptive to its purpose because it helps identify what it’s auditing.
• FOR SERVER AUDIT: You need to associate it with your audit specification. You need this because this is where your audit data will live.
• Actions with the ADD statement:
o Audit Action Type:
INSERT – gives you the ability to see who inserts to a table, schema, or database.
UPDATE – gives you the ability to see who updates a table, schema, or database.
EXECUTE – gives you the ability to see who executes on a stored procedure, function, schema, or database.
SELECT – gives you the ability to see who selects from a table, schema, or database.
DELETE – gives you the ability to see who deletes from a table, schema, or database
o Object Class:
OBJECT: Choose this to see queries against a specific table, view, stored procedure, or function.
SCHEMA: Choose this to see queries against any object in a schema
DATABASE: Choose this to see queries against any object in a database
o Object Schema: Required for OBJECT or SCHEMA object classes
o Object Name: Required for OBJECT class
o Principal Name: Required for OBJECT, SCHEMA, and DATABASE classes. Use public if you want to audit everyone. If you want to audit multiple users, you need one line for each user.
Querying Audit Data
While you can query the audit via the GUI, it’s far more powerful and easier to centralize the audit data if you query it with a SQL query. The following query gets what I’ve found to be the most useful columns in the audit:
SELECT distinct DATEADD(mi, DATEPART(TZ, SYSDATETIMEOFFSET()), event_time) as event_time,
aa.name as audit_action,statement,succeeded, server_instance_name,
database_name, schema_name, session_server_principal_name, server_principal_name,
object_Name, file_name, client_ip, application_name, host_name, file_name
FROM sys.fn_get_audit_file ('/var/opt/mssql/data/audit/*.sqlaudit',default,default) af
INNER JOIN sys.dm_audit_actions aa ON aa.action_id = af.action_id
where DATEADD(mi, DATEPART(TZ, SYSDATETIMEOFFSET()), event_time) > DATEADD(HOUR, -24, GETDATE())
order by DATEADD(mi, DATEPART(TZ, SYSDATETIMEOFFSET()), event_time) desc
When you get the query results, maybe there’s nothing listed because nothing auditable happened yet. Maybe you’ll see tons because there’s a bunch of stuff happening in the background that you didn’t realize was happening. Here’s an example of what some audit results may look like:
Disclaimer on Auditing
Be very careful how and what you audit. You can overload or freeze up a production server. It happened to me when I didn’t even think it was possible to crash a production server with an audit. I just thought somehow, I could stop the audit in between things it was auditing. Sometimes it’s going to be hard to stop your audit if it’s auditing too much or going to be so hard to weed through all the data, it will be like trying to find a needle in a haystack with all the stuff you’re auditing. I just go with the less is more method of auditing.
Additional Information
More information on server and database audit is provided in these links:
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15#database-level-audit-action-groups
This article first appeared in the SQLServerGeeks Magazine.
Author: Josephine Bush
Subscribe to get your copy.
