Microsoft SQL Server Audit Integration

Overview

This integration collects login events, query audit logs, privilege changes, and command execution logs from Microsoft SQL Server using SQL Server Audit and Extended Events.

Supported versions: SQL Server 2016, 2019, 2022, Azure SQL

---

Prerequisites

• A KYRA MDR Collector installed and running (Installation Guide)
• SQL Server sysadmin or equivalent permissions
• NXLog or similar agent for forwarding Windows Event Logs to syslog

---

Configuration

Step 1: Create a Server Audit

CREATE SERVER AUDIT KyraAudit
    TO FILE (FILEPATH = 'C:\AuditLogs\', MAXSIZE = 100MB, MAX_ROLLOVER_FILES = 10);
ALTER SERVER AUDIT KyraAudit WITH (STATE = ON);

Step 2: Create Audit Specifications

-- Server-level audit
CREATE SERVER AUDIT SPECIFICATION KyraServerSpec
    FOR SERVER AUDIT KyraAudit
    ADD (FAILED_LOGIN_GROUP),
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP);
ALTER SERVER AUDIT SPECIFICATION KyraServerSpec WITH (STATE = ON);

-- Database-level audit
CREATE DATABASE AUDIT SPECIFICATION KyraDbSpec
    FOR SERVER AUDIT KyraAudit
    ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public);
ALTER DATABASE AUDIT SPECIFICATION KyraDbSpec WITH (STATE = ON);

Step 3: Forward to KYRA Collector

Use NXLog to forward audit events from the Windows Event Log to the KYRA Collector via syslog. Alternatively, configure the audit to write to the Application Log:

CREATE SERVER AUDIT KyraAudit TO APPLICATION_LOG;

---

Collected Log Types

Log Type	Security Use	Priority
Login success/failure	Brute force detection	High
SA account usage	Privileged account monitoring	Critical
Role membership changes	Privilege escalation detection	Critical
DML queries (SELECT, INSERT, UPDATE, DELETE)	Data access auditing (ISMS-P)	High
DDL changes (DROP, ALTER)	Schema change monitoring	High
xp_cmdshell execution	OS command execution (RCE) detection	Critical
BULK INSERT	Mass data loading detection	Medium

---

Troubleshooting

No Audit Events

1. Verify the server audit is enabled: SELECT * FROM sys.server_audits;
2. Confirm audit specifications are active: SELECT * FROM sys.server_audit_specifications;
3. Check that NXLog is running and forwarding events to the collector

High Volume

Narrow the database audit specification to specific tables containing sensitive data rather than the entire dbo schema.

For additional help, contact kyra@seekerslab.com.