SQL Server Audit: Tracking Logins and Schema Changes

2018-12-13 · mssql, audit, security, compliance

"Who dropped that table?" and "who logged in as sa last night?" I've been asked both, and the default trace and ERRORLOG were no help. SQL Server Audit, built on Extended Events, is the proper answer. It writes tamper-evident records to a file or the Windows Security or Application log, costs very little, and since SQL Server 2016 SP1 it works in Standard edition too.

My setup captures failed and successful logins plus every DDL change. Requirements: SQL Server 2016 SP1 or 2017 (or 2008 R2+ Enterprise), CONTROL SERVER or ALTER ANY SERVER AUDIT, and a folder the service account can write to, ideally on its own volume, like E:\SQLAudit\.

Step 1: Create the server audit (the destination)

The audit object defines where records go and what happens if writing fails.

USE master;
CREATE SERVER AUDIT [Audit_Security]
TO FILE (
    FILEPATH = N'E:\SQLAudit\',
    MAXSIZE = 256 MB,
    MAX_ROLLOVER_FILES = 30,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

ON_FAILURE = CONTINUE keeps the server running if the disk fills; SHUTDOWN is what a strict compliance regime asks for, and FAIL_OPERATION (2012+) fails only the audited statements. QUEUE_DELAY = 1000 lets records be buffered for up to a second, which is what keeps the overhead negligible.

Writing to the Windows Security log requires the service account to have the "Generate security audits" right and the audit object access policy enabled; I stick to the file target because there's less to configure and fewer ways for it to break.

Step 2: Create the server audit specification

This is the instance-level part: logins, server role changes, and DDL across all databases.

CREATE SERVER AUDIT SPECIFICATION [AuditSpec_Server]
FOR SERVER AUDIT [Audit_Security]
    ADD (FAILED_LOGIN_GROUP),
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (LOGOUT_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP),
    ADD (DATABASE_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);

SCHEMA_OBJECT_CHANGE_GROUP covers CREATE/ALTER/DROP on tables, views, procedures and functions in every database; DATABASE_OBJECT_CHANGE_GROUP covers schemas, certificates and similar. AUDIT_CHANGE_GROUP records anyone who alters or disables the audit itself, which is the first thing to include when the goal is accountability.

SUCCESSFUL_LOGIN_GROUP can be noisy on an application server with connection pooling churn. If the volume is a problem, keep FAILED_LOGIN_GROUP at the server level and audit successful logins for privileged accounts only through a filter (step 4).

Step 3: Turn the audit on

Audits are created in the OFF state.

ALTER SERVER AUDIT [Audit_Security] WITH (STATE = ON);

Check the status:

SELECT name, status_desc, audit_file_path, audit_file_size / 1024 / 1024 AS file_mb
FROM sys.dm_server_audit_status;

Step 4: Optional database audit specification and filters

If one database holds the sensitive data, you can add a database-level specification for data access on specific objects:

USE Sales;
CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_Sales]
FOR SERVER AUDIT [Audit_Security]
    ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.Customers BY public),
    ADD (EXECUTE ON SCHEMA::dbo BY public),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);

Auditing every SELECT on a hot table produces a lot of records, so keep this to the tables the auditors care about.

Since 2012 the server audit itself can carry a WHERE predicate, which is how I limit successful-login records to SQL logins and the sa account:

ALTER SERVER AUDIT [Audit_Security] WITH (STATE = OFF);
ALTER SERVER AUDIT [Audit_Security]
WHERE (action_id = 'LGIF') OR (action_id = 'LGIS' AND server_principal_name = 'sa') OR (class_type = 'U' OR class_type = 'P' OR class_type = 'V');
ALTER SERVER AUDIT [Audit_Security] WITH (STATE = ON);

Step 5: Read the audit files

sys.fn_get_audit_file reads one file or a whole directory with a wildcard:

SELECT event_time, action_id, succeeded, session_server_principal_name, server_principal_name,
       client_ip, application_name, database_name, schema_name, object_name, statement
FROM sys.fn_get_audit_file('E:\SQLAudit\Audit_Security_*.sqlaudit', DEFAULT, DEFAULT)
WHERE event_time > DATEADD(day, -1, GETUTCDATE())
ORDER BY event_time DESC;

Useful action codes: LGIF failed login, LGIS successful login, CR create, AL alter, DR drop. The statement column holds the actual DDL text, and client_ip plus application_name tell you where it came from. SSMS also offers View Audit Logs on the audit node, which uses the same function behind a grid.

Times in the file are UTC; convert with DATEADD or AT TIME ZONE (2016+) for reports.

Trigger something and look

Generate some events and read them back:

CREATE TABLE dbo.AuditTest (id int);
DROP TABLE dbo.AuditTest;

Then attempt a login with a wrong password from sqlcmd -S sqlprod01 -U sa -P wrong. Within a second or two, the query in step 5 should show a CR, a DR and an LGIF row with your IP address.

Where it broke for me

"Msg 33222: Audit 'Audit_Security' failed to start. For more information, see the SQL Server error log." with "Operating system error 3 (The system cannot find the path specified.)" or error 5 (access denied) in the ERRORLOG. Create the folder and grant the service account Modify on it.
"Msg 33204: SQL Server Audit could not write to the security log." – the service account is missing the "Generate security audits" user right (secpol.msc → User Rights Assignment) or the audit policy for object access is not enabled via auditpol /set /subcategory:"Application Generated" /success:enable /failure:enable.
Audit files fill the disk. MAX_ROLLOVER_FILES only limits the count if MAXSIZE is set; with MAXSIZE = UNLIMITED a single file grows forever. Archive old files with a scheduled task and keep the two settings together.
A restore of the database on another server brings the database audit specification along, pointing at a server audit that does not exist there. Drop or recreate the specification after restoring, or the database will show a warning in the log at startup.

Next I'd copy the files to a share the DBAs can't delete from. An audit you can edit yourself doesn't convince anyone.