Interface & Features — SQL Server Auditor

Walk through the main screens, tabs, and controls you will use day to day.

Dashboard Tab

The Dashboard is the home screen of the UI. It provides a real-time health overview of all monitored instances and visual analytics of audit activity.

Health Panel

At the top of the dashboard, a health indicator shows the status of each registered instance (Collecting, Stopped, Error). The panel also shows the total event count, alert count, and last collection timestamp.

Charts

• Events by Category — Bar chart showing DML, DDL, Security, Login, and other category counts across all instances.
• Events by Hour — Line chart showing event volume over the last 24 hours for pattern analysis.

Instance Management

From the dashboard you can:

• Register Instance — Add a new SQL Server instance to monitor. Enter server name, authentication method, and optional display name.
• Configure Instance — Open the Configure tab pre-selected for a specific instance.
• Start / Stop Instance — Start or stop the audit session on a specific instance.
• Delete Instance — Remove a registered instance and optionally delete its audit data.

Auto-Refresh

The dashboard refreshes automatically at a configurable interval (default: 30 seconds). The refresh timer is shown in the toolbar. You can also click Refresh to update immediately.

Events Tab

The Events tab displays all collected audit events with powerful filtering and search capabilities.

Event Grid

Audit events are displayed in a scrollable DataGrid with the following columns: Time, Event Type, Category, Server, Database, Login, Statement (truncated), Duration, and Severity. Click any row to open the event detail panel.

Event Detail Panel

A resizable side panel shows all properties of the selected event, including the full SQL statement text, execution metrics (CPU, I/O, duration), client information, and any associated alert.

Search & Filter

The Events tab provides multiple filtering options that work together:

Export from Events

Configure Audit Tab

The Configure tab allows you to set up per-instance audit policies. Select an instance from the dropdown, then configure which events to capture and how.

Event Selection

Events are organized into three groups with individual toggle checkboxes:

• DML (Data Manipulation) — SELECT, INSERT, UPDATE, DELETE tracking with minimum duration filter
• DDL (Data Definition) — CREATE, ALTER, DROP on tables, views, procedures, and other objects
• Security — Login/Logout, Failed Logins, Permission changes (GRANT, DENY, REVOKE), Role changes, Server configuration

Session Settings

Event Categories

Filters

Narrow the scope of captured events using optional per-instance filters:

Apply Configuration

After making changes, click Save & Apply. The collector service will pick up the new configuration on its next collection cycle and recreate the audit session with the updated settings.

Alerts Tab

The Alerts tab displays security-relevant alerts generated automatically by the collector service and AI analysis.

Alert Grid

Alerts are displayed with the following information:

• Severity — Critical (red), Warning (yellow), Info (blue)
• Title — Brief description of the alert
• Server / Database — Where the alert originated
• Time — When the alert was raised
• Acknowledged — Whether an operator has acknowledged the alert

Alert Workflow

1. Alerts are generated automatically based on event patterns (e.g., multiple failed logins, privilege escalation, off-hours DDL changes)
2. Unacknowledged alerts show a badge count on the Alerts tab header
3. Click an alert row to view details, then click Acknowledge to mark it as reviewed
4. Use Acknowledge All to bulk-acknowledge all visible alerts
5. Toggle Show Acknowledged to include or hide previously acknowledged alerts

Alert Types

Compliance Reports Tab

Generate professional PDF compliance reports with a single click. Four pre-built report types cover common regulatory requirements.

Report Types

Report Configuration

• Period — Select the reporting window: Last 7 days, 30 days, 90 days, or 365 days
• Format — PDF (generated using PdfSharpCore with styled headers, tables, and summary statistics)
• Output — Save dialog opens automatically after generation. Reports include a cover page with generation timestamp, period, and instance scope.

AI-Powered Security Insights

The AI Insights panel is a slide-out drawer on the right side of the main window. It uses the OpenAI API to analyze your audit data and provide automated security intelligence.

Opening AI Insights

Click the AI button in the header bar to toggle the AI drawer panel (380px wide). The panel slides out from the right without navigating away from your current tab.

Analysis Types

Configuration

To use AI Insights, you need an OpenAI API key. Enter it in Settings → General → OpenAI API Key. The key is stored encrypted on the local machine. The AI panel sends a summarized version of your recent audit data — no raw SQL statements or credentials are transmitted.

Settings Window

The Settings window is accessed via the gear icon in the header bar. It has a 6-page left-navigation layout:

General

• Central Database — View and update the connection to the central audit database
• Account — View current user info, change password
• OpenAI API Key — Configure the API key for AI Insights (encrypted with DPAPI)
• Auto-Refresh Interval — Set the dashboard refresh timer (default: 30 seconds)

Service

• Collector Configuration — View and edit the collector service settings (collection interval, deduplication, batch size)
• Service Status — Check if the collector service is running, start/stop from the UI

Users (Admin Only)

The Users page is only visible to Admin-role users. It provides a DataGrid for managing dashboard user accounts.

• Add User — Create a new user with username, display name, password, and role (Admin, Operator, Viewer)
• Delete User — Remove a user account (cannot delete the last Admin)
• Reset Password — Set a new password for any user
• Change Role — Promote or demote users between roles

Role Permissions

Integrations (SIEM & Webhooks)

The Integrations page lets you configure external endpoints that receive alert notifications. Each endpoint is stored in the central database and managed via a DataGrid.

Adding an Endpoint

1. Click Add Integration
2. Enter a Name (e.g., “Splunk HEC” or “Slack #security”)
3. Enter the URL (webhook URL or SIEM HTTP endpoint)
4. Select the Format: JSON or CEF (Common Event Format)
5. Enable or disable the endpoint
6. Click Save

Supported Platforms

Test & Health

• Test Webhook — Send a test alert to all enabled endpoints to verify connectivity
• Consecutive Failures — The grid shows a failure counter per endpoint. After 5 consecutive failures, the endpoint is auto-disabled
• Enable / Disable — Toggle individual endpoints without deleting configuration

Compliance Pack — SQL Server Audit integration

The Compliance Pack is an optional upgrade that reads security and backup/restore events from a native SQL Server Audit you configure on the monitored instance, instead of the built-in DDL-trigger + msdb polling path. This is the evidence trail external auditors recognise for SOX, HIPAA, PCI DSS, and ISO 27001 — produced by SQL Server itself and tamper-evident by design.

When to enable it

• You must produce a legally admissible audit trail for regulators
• You need to capture failed BACKUP / RESTORE attempts (msdb.backupset only records successes)
• You deploy on Azure SQL Managed Instance and want backup auditing
• You want automatic audit-configuration-change tracking (AUDIT_CHANGE_GROUP)

If none of the above applies, leave it off — the default DBLense pipeline is lighter and covers all the operational scenarios.

How the collector reads audit data

The collector never reads .sqlaudit / .xel files directly. It calls sys.fn_get_audit_file() over the same TCP 1433 connection it already uses for monitoring — SQL Server opens the file or blob on its own side. No agent, no file share, no Azure SDK, no blob storage keys on the collector host.

Four-step install

1. On the monitored host (or Managed Instance), run the supplied script:
   • On-prem / VM: scripts\install-server-audit-onprem.sql (edit audit folder, defaults to C:\SQLAudit\)
   • Managed Instance: scripts\install-server-audit-mi.sql (edit blob URL and paste SAS token)
   Run with sqlcmd -I -i <script> as sysadmin / CONTROL SERVER.
2. Grant the collector login read access:

   GRANT VIEW SERVER AUDIT STATE TO [dblense_collector];   -- SQL 2022+
   -- or CONTROL SERVER on earlier versions

3. In the DBLense UI, edit the registered instance and in the Compliance Pack card:
   • Tick Use native SQL Server Audit (Compliance Pack)
   • Server Audit Name: DBLenseServerAudit
   • Audit File Path / URL:
     • On-prem: C:\SQLAudit\*.sqlaudit
     • MI: https://<acct>.blob.core.windows.net/sqlaudit/<mi-name>/DBLenseServerAudit/*.xel
4. Save. The collector switches to the Compliance Pack path on its next cycle (≤ 30 s).

Action groups captured

The install script adds the following to the server audit specification:

• FAILED_LOGIN_GROUP, SUCCESSFUL_LOGIN_GROUP, LOGOUT_GROUP
• SERVER_PRINCIPAL_CHANGE_GROUP, DATABASE_PRINCIPAL_CHANGE_GROUP
• SERVER_ROLE_MEMBER_CHANGE_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP
• SERVER_PERMISSION_CHANGE_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, SERVER_OBJECT_PERMISSION_CHANGE_GROUP, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
• BACKUP_RESTORE_GROUP (both success and failure)
• DATABASE_CHANGE_GROUP, DATABASE_OBJECT_CHANGE_GROUP, SCHEMA_OBJECT_CHANGE_GROUP
• AUDIT_CHANGE_GROUP, SERVER_OPERATION_GROUP

Operational notes

• Disk (on-prem): defaults 50 files × 256 MB = 12 GB ceiling. Security-only audit typically generates 10–100 MB / day.
• Blob (MI): at ~100 MB / day, 90 days retention ≈ 9 GB ≈ a few cents / month on hot-tier LRS.
• Restart safety: audit auto-starts with SQL Server; collector resumes from the last event_time watermark — no gaps.
• Uninstall: untick Use Server Audit (reverts to default pipeline immediately), then run the UNINSTALL block at the bottom of the install script.

Full setup walkthrough, permissions, cost notes and troubleshooting live in scripts/COMPLIANCE-SETUP.md in the install directory.

Central DB Ledger (defence in depth)

Independently of the Compliance Pack, the DBLense central database can itself be hardened using SQL Server Ledger Tables. This protects the central audit archive against tampering even by a sysadmin on the central server.

What Are Ledger Tables?

SQL Server 2022+ Ledger Tables provide database-level tamper-evidence. Each row is linked to the previous row via a cryptographic hash chain. Any modification to historical data breaks the chain and is detectable.

Ledger Configuration

Enable Ledger

1. Navigate to Settings → Security
2. Click Enable Ledger Tables
3. The tool will alter the AuditEvents and AuditAlerts tables to enable ledger mode (requires SQL Server 2022+)
4. Once enabled, the ledger cannot be disabled without recreating the tables

Verify Integrity

Click Verify Integrity to run a ledger verification check. The result shows Pass or Fail for each ledger table, confirming whether any historical data has been tampered with.

Generate Digest

Click Generate Digest to create a cryptographic digest of the current ledger state. Save this digest externally (e.g., Azure Confidential Ledger, Azure Blob Storage, or a file share) for independent verification at a later date.

Diagnostics

• Log Viewer — View recent application and collector service log entries
• Connection Test — Verify connectivity to the central database
• Version Info — Application version, .NET runtime, and database schema version