SQL Server Audit

SERVER AUDIT          ← defines the destination (file, Windows event log, App log)
  └── SERVER AUDIT SPECIFICATION    ← captures server-level events (logins, AG, DB operations)
  └── DATABASE AUDIT SPECIFICATION  ← captures database-level events (DML, DDL, schema access)

-- File-based audit (most common for compliance)
CREATE SERVER AUDIT ComplianceAudit
TO FILE
(
    FILEPATH = N'C:\SQLAudit\',          -- must exist and be writable by SQL service account
    MAXSIZE = 100 MB,                    -- per-file size before rolling; 0 = unlimited
    MAX_ROLLOVER_FILES = 50,             -- number of files to keep; UNLIMITED = keep all
    RESERVE_DISK_SPACE = OFF             -- ON pre-allocates full MAXSIZE immediately
)
WITH
(
    QUEUE_DELAY = 1000,                  -- ms; 0 = synchronous (guaranteed but slower)
    ON_FAILURE = CONTINUE,              -- CONTINUE | SHUTDOWN | FAIL_OPERATION
    AUDIT_GUID = 'a1b2c3d4-e5f6-...'    -- optional; useful for AG failover correlation
)
WHERE ([action_id] = 'SL'               -- optional server-side predicate filter
    OR [action_id] = 'IN');
GO

ALTER SERVER AUDIT ComplianceAudit WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION ServerLoginAuditSpec
FOR SERVER AUDIT ComplianceAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (DBCC_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON);
GO

USE SensitiveDB;
GO

CREATE DATABASE AUDIT SPECIFICATION SensitiveDataAuditSpec
FOR SERVER AUDIT ComplianceAudit
ADD (SELECT ON OBJECT::dbo.Customers BY PUBLIC),       -- object-level: any principal
ADD (INSERT ON OBJECT::dbo.Customers BY PUBLIC),
ADD (UPDATE ON OBJECT::dbo.Customers BY PUBLIC),
ADD (DELETE ON OBJECT::dbo.Customers BY PUBLIC),
ADD (EXECUTE ON OBJECT::dbo.usp_GetCustomer BY PUBLIC),
ADD (DATABASE_OBJECT_CHANGE_GROUP),                    -- DDL changes within this DB
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON);
GO

-- Audit all access to a table by any user
ADD (SELECT ON OBJECT::dbo.CreditCardNumbers BY PUBLIC)

-- Audit access only by a specific user
ADD (UPDATE ON OBJECT::dbo.SalaryData BY [DOMAIN\john.smith])

-- Audit execute on a stored procedure
ADD (EXECUTE ON OBJECT::dbo.usp_ProcessPayment BY PUBLIC)

-- Audit access to an entire schema (captures all objects in schema)
ADD (SELECT ON SCHEMA::Finance BY PUBLIC)

CREATE SERVER AUDIT FileAudit
TO FILE
(
    FILEPATH = N'D:\Audit\',
    MAXSIZE = 200 MB,
    MAX_ROLLOVER_FILES = 100,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

CREATE SERVER AUDIT AppLogAudit
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

CREATE SERVER AUDIT SecLogAudit
TO SECURITY_LOG
WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN);

-- Read all records from current file set
SELECT
    event_time,
    action_id,
    succeeded,
    server_principal_name,
    database_name,
    schema_name,
    object_name,
    statement,
    additional_information,
    session_id,
    client_ip,
    application_name,
    server_instance_name,
    file_name,
    audit_file_offset
FROM sys.fn_get_audit_file
(
    N'D:\Audit\ComplianceAudit_*.sqlaudit',  -- wildcard to include all rolled files
    DEFAULT,
    DEFAULT
)
ORDER BY event_time;

-- Read audit records for failed logins in the last 24 hours
SELECT
    event_time,
    action_id,
    server_principal_name,
    client_ip,
    application_name,
    additional_information
FROM sys.fn_get_audit_file(N'D:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'LGIF'   -- LGIF = Login Failed
  AND event_time >= DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY event_time DESC;

-- Read from a specific offset (incremental polling pattern)
DECLARE @file_name NVARCHAR(260) = N'D:\Audit\ComplianceAudit_0_132000000000000000.sqlaudit';
DECLARE @offset BIGINT = 0;

SELECT *
FROM sys.fn_get_audit_file(@file_name, DEFAULT, @offset)
ORDER BY audit_file_offset;

-- Map action_id codes to human-readable names
SELECT action_id, name, class_desc
FROM sys.dm_audit_actions
WHERE name LIKE '%LOGIN%'
ORDER BY name;

-- Common action_id codes:
-- CNAU = Create Server Audit
-- LGIF = Login Failed
-- LGIS = Login Succeeded
-- LGO  = Logout
-- SL   = SELECT
-- IN   = INSERT
-- UP   = UPDATE
-- DL   = DELETE
-- EX   = EXECUTE
-- AL   = ALTER
-- CR   = CREATE
-- DR   = DROP
-- GRNT = GRANT
-- DENY = DENY
-- RVKE = REVOKE

-- Enable
ALTER SERVER AUDIT ComplianceAudit WITH (STATE = ON);
ALTER SERVER AUDIT SPECIFICATION ServerLoginAuditSpec WITH (STATE = ON);

-- Disable (must disable spec before changing audit)
ALTER SERVER AUDIT SPECIFICATION ServerLoginAuditSpec WITH (STATE = OFF);
ALTER DATABASE AUDIT SPECIFICATION SensitiveDataAuditSpec WITH (STATE = OFF);
ALTER SERVER AUDIT ComplianceAudit WITH (STATE = OFF);

ALTER SERVER AUDIT SPECIFICATION ServerLoginAuditSpec
WITH (STATE = OFF);
GO

ALTER SERVER AUDIT SPECIFICATION ServerLoginAuditSpec
ADD (EXECUTE_AS_GROUP),
DROP (LOGOUT_GROUP);
GO

ALTER SERVER AUDIT SPECIFICATION ServerLoginAuditSpec
WITH (STATE = ON);
GO

-- Must disable and drop specs before dropping the audit object
ALTER DATABASE AUDIT SPECIFICATION SensitiveDataAuditSpec WITH (STATE = OFF);
DROP DATABASE AUDIT SPECIFICATION SensitiveDataAuditSpec;

ALTER SERVER AUDIT SPECIFICATION ServerLoginAuditSpec WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION ServerLoginAuditSpec;

ALTER SERVER AUDIT ComplianceAudit WITH (STATE = OFF);
DROP SERVER AUDIT ComplianceAudit;

CREATE DATABASE AUDIT SPECIFICATION SOX_FinancialDB
FOR SERVER AUDIT ComplianceAudit
ADD (SELECT   ON SCHEMA::Finance BY PUBLIC),
ADD (INSERT   ON SCHEMA::Finance BY PUBLIC),
ADD (UPDATE   ON SCHEMA::Finance BY PUBLIC),
ADD (DELETE   ON SCHEMA::Finance BY PUBLIC),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);

CREATE DATABASE AUDIT SPECIFICATION HIPAA_PHI_Access
FOR SERVER AUDIT ComplianceAudit
ADD (SELECT ON OBJECT::dbo.PatientRecords BY PUBLIC),
ADD (SELECT ON OBJECT::dbo.DiagnosisCodes BY PUBLIC),
ADD (UPDATE ON OBJECT::dbo.PatientRecords BY PUBLIC),
ADD (DELETE ON OBJECT::dbo.PatientRecords BY PUBLIC),
ADD (EXECUTE ON OBJECT::dbo.usp_GetPatientData BY PUBLIC),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON);

-- PCI-DSS: Enable AUDIT_CHANGE_GROUP to detect tampering with audit config
CREATE SERVER AUDIT SPECIFICATION PCI_ServerSpec
FOR SERVER AUDIT ComplianceAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),            -- critical: captures anyone disabling audit
ADD (SERVER_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON);

CREATE SERVER AUDIT FilteredLoginAudit
TO FILE (FILEPATH = N'C:\Audit\')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
WHERE ([action_id] = 'LGIF'        -- Failed logins only
    OR [succeeded] = 0);           -- Any failed operation

-- Filter: only capture events on a specific database
WHERE ([database_name] = N'ProductionDB');

-- Filter: exclude monitoring users from audit noise
WHERE ([server_principal_name] NOT IN (N'monitoring_login', N'sa'));

-- Filter: capture all failures
WHERE ([succeeded] = 0);

-- Enabled at the server level (applies to all databases) via Azure Portal or:
-- ALTER DATABASE ... SET AUDIT_ENABLED = ON / OFF is not the right approach;
-- Azure SQL auditing is configured through ARM/PowerShell/CLI, not T-SQL.

-- Reading audit logs from Azure Blob Storage:
SELECT *
FROM sys.fn_get_audit_file
(
    'https://yourstorageaccount.blob.core.windows.net/sqldbauditlogs/yourserver/yourdb/SqlDbAuditing_ServerAudit/*.xel',
    DEFAULT,
    DEFAULT
)
ORDER BY event_time DESC;

-- List all server audits and their state
SELECT
    a.name                  AS audit_name,
    a.type_desc             AS destination,
    a.log_file_path,
    a.log_file_name,
    a.queue_delay,
    a.on_failure_desc,
    a.is_state_enabled,
    a.audit_guid
FROM sys.server_audits a
ORDER BY a.name;

-- List server audit specifications and which audit they use
SELECT
    s.name              AS spec_name,
    a.name              AS audit_name,
    s.is_state_enabled
FROM sys.server_audit_specifications s
JOIN sys.server_audits a ON s.audit_guid = a.audit_guid;

-- List server audit specification details (which action groups are included)
SELECT
    s.name          AS spec_name,
    d.audit_action_name,
    d.class_desc,
    d.is_group
FROM sys.server_audit_specifications s
JOIN sys.server_audit_specification_details d ON s.server_specification_id = d.server_specification_id
ORDER BY s.name, d.audit_action_name;

-- List database audit specifications in current database
SELECT
    s.name                  AS spec_name,
    a.name                  AS audit_name,
    s.is_state_enabled
FROM sys.database_audit_specifications s
JOIN sys.server_audits a ON s.audit_guid = a.audit_guid;

-- List database audit spec details (object-level + action groups)
SELECT
    s.name              AS spec_name,
    d.audit_action_name,
    d.class_desc,
    OBJECT_NAME(d.major_id) AS object_name,
    USER_NAME(d.audited_principal_id) AS principal_name,
    d.is_group,
    d.containing_object_class_desc
FROM sys.database_audit_specifications s
JOIN sys.database_audit_specification_details d ON s.database_specification_id = d.database_specification_id
ORDER BY s.name, d.audit_action_name;

-- Show all available audit action groups
SELECT name, action_id, class_desc, is_group
FROM sys.dm_audit_actions
WHERE is_group = 1
ORDER BY name;

-- Show all available audit action types (non-group)
SELECT name, action_id, class_desc
FROM sys.dm_audit_actions
WHERE is_group = 0
ORDER BY name;

-- Check audit file size and recent activity
SELECT
    audit_file_path,
    audit_file_size,
    create_time,
    event_count
FROM sys.dm_audit_file_metadata
WHERE audit_file_path LIKE N'D:\Audit\%'
ORDER BY create_time DESC;

-- Step 1: Create the audit
CREATE SERVER AUDIT SOX_Audit
TO FILE (FILEPATH = N'E:\SQLAudit\SOX\', MAXSIZE = 500 MB, MAX_ROLLOVER_FILES = 100)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT SOX_Audit WITH (STATE = ON);

-- Step 2: Server-level events
CREATE SERVER AUDIT SPECIFICATION SOX_ServerSpec
FOR SERVER AUDIT SOX_Audit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP)
WITH (STATE = ON);

-- Step 3: Per-database for each financial DB
USE FinancialDB;
GO
CREATE DATABASE AUDIT SPECIFICATION SOX_FinancialDB
FOR SERVER AUDIT SOX_Audit
ADD (SELECT   ON SCHEMA::Accounting BY PUBLIC),
ADD (INSERT   ON SCHEMA::Accounting BY PUBLIC),
ADD (UPDATE   ON SCHEMA::Accounting BY PUBLIC),
ADD (DELETE   ON SCHEMA::Accounting BY PUBLIC),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);

-- Failed logins in the last hour, grouped by IP
SELECT
    client_ip,
    server_principal_name,
    COUNT(*) AS failed_attempts,
    MIN(event_time) AS first_attempt,
    MAX(event_time) AS last_attempt
FROM sys.fn_get_audit_file(N'E:\SQLAudit\SOX\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'LGIF'
  AND event_time >= DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY client_ip, server_principal_name
HAVING COUNT(*) >= 5              -- threshold for brute-force detection
ORDER BY failed_attempts DESC;

-- All actions by a specific user in the last 7 days
SELECT
    event_time,
    action_id,
    database_name,
    schema_name,
    object_name,
    succeeded,
    statement,
    client_ip,
    application_name
FROM sys.fn_get_audit_file(N'E:\SQLAudit\SOX\*.sqlaudit', DEFAULT, DEFAULT)
WHERE server_principal_name = N'DOMAIN\suspect_user'
  AND event_time >= DATEADD(DAY, -7, GETUTCDATE())
ORDER BY event_time DESC;

-- Table to track last processed position
CREATE TABLE dbo.AuditCheckpoint
(
    audit_name      NVARCHAR(128) NOT NULL,
    last_file       NVARCHAR(260) NULL,
    last_offset     BIGINT        NOT NULL DEFAULT 0,
    processed_at    DATETIME2     NOT NULL DEFAULT SYSDATETIME()
);

-- Incremental read from last checkpoint
DECLARE @last_file   NVARCHAR(260);
DECLARE @last_offset BIGINT;

SELECT @last_file = last_file, @last_offset = last_offset
FROM dbo.AuditCheckpoint
WHERE audit_name = N'SOX_Audit';

-- Read new records only
SELECT *
FROM sys.fn_get_audit_file(
    COALESCE(@last_file, N'E:\SQLAudit\SOX\*.sqlaudit'),
    DEFAULT,
    COALESCE(@last_offset, 0)
)
ORDER BY event_time;

-- After processing, update checkpoint to last record
-- UPDATE dbo.AuditCheckpoint SET last_file = @new_file, last_offset = @new_offset ...

-- Use SQL Agent alert or XE session to watch for AUDIT_CHANGE events
-- Or query the audit log for recent AUDIT_CHANGE_GROUP events:
SELECT event_time, server_principal_name, statement, additional_information
FROM sys.fn_get_audit_file(N'E:\SQLAudit\SOX\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ('CNAU', 'ALAU', 'DRAU', 'CNSP', 'ALSP', 'DRSP')  -- audit create/alter/drop events
ORDER BY event_time DESC;