Home
Knowledge Center
How to Audit Microsoft SQL Server

How to Audit Microsoft SQL Server

Auditing is an essential part of database governance. Whether you're protecting financial records, healthcare data, or internal logs, SQL Server offers native capabilities—and those can be extended with platforms like DataSunrise. This guide explores how to audit Microsoft SQL Server using both native tools and external solutions while maintaining visibility over sensitive data and access control.

General Information with Useful Links

Microsoft SQL Server includes a broad range of security and audit options across its editions. Auditing begins with knowing what to log, where to store it, and how to alert stakeholders. The official SQL Server auditing documentation lays out the foundation.

If you're handling financial transactions or medical records, aligning your audits with regulations like GDPR or HIPAA is a must. A full checklist is available via Microsoft’s security center.

To understand where sensitive data exists in your environment, use DataSunrise's discovery capabilities early in your process.

How to Native Audit Microsoft SQL Server

Auditing SQL Server natively is done using CREATE SERVER AUDIT and AUDIT SPECIFICATION objects. These tools allow precise monitoring of logins, data access, permission changes, and schema modifications—all without third-party tools.

Here's how to start:

CREATE SERVER AUDIT Audit_SQL_Logs
TO FILE (FILEPATH = 'D:\AuditLogs\');
GO

CREATE SERVER AUDIT SPECIFICATION Audit_Logins
FOR SERVER AUDIT Audit_SQL_Logs
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP);
GO

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

Add a database-level audit for sensitive table access:

CREATE DATABASE AUDIT SPECIFICATION AuditSensitiveData
FOR SERVER AUDIT Audit_SQL_Logs
ADD (SELECT ON OBJECT::dbo.Patients BY public)
WITH (STATE = ON);
GO

The results can be viewed using:

SELECT * FROM sys.fn_get_audit_file('D:\AuditLogs\*', default, default);

This native setup captures who accessed what and when, which is critical for monitoring exposure of sensitive data. SQL Server also offers built-in dynamic data masking to obscure sensitive fields at runtime.

Key Features of Native Auditing

No external dependencies
Available in Enterprise Edition and partially in Standard
Integrated with Windows security events

However, native auditing lacks flexibility for large-scale compliance operations. That's where DataSunrise offers deeper value.

How to Audit Microsoft SQL Server with DataSunrise

DataSunrise acts as a transparent proxy for Microsoft SQL Server. It logs every query, applies audit rules, and forwards events to external systems. Here's how to configure it step-by-step:

Step 1: Add SQL Server to DataSunrise

Log in to the management console and navigate to DB Instances. Choose Add New, select Microsoft SQL Server, and enter the following:

Connection name
SQL Server IP or hostname
Port (default is 1433)
Credentials (read-only access is sufficient for audit purposes)

How to Audit Microsoft SQL Server - Architecture diagram showing how server and database audit specifications generate logs routed to audit, application, and event outputs — Proxy configuration form in DataSunrise for defining ports and SSL settings for secure SQL traffic inspection

Step 2: Enable Reverse Proxy Mode

Set up a listener on a new port (e.g., 15433). This redirects client traffic through DataSunrise, allowing full visibility without modifying SQL Server itself. More on reverse proxy mode here.

Step 3: Configure Audit Rules

Go to Audit → Rules and click Create Rule. You can:

Log query text, schema, and timestamp
Target users/groups/roles
Limit by time of day or query type

Apply rule priorities to fine-tune how events are processed.

How to Audit Microsoft SQL Server - Proxy configuration form in DataSunrise for defining ports and SSL settings for secure SQL traffic inspection — SQL injection rule configuration in DataSunrise audit module with penalties for risky patterns such as comments, unions, and constants

Step 4: Set Up Log Management

Audit logs in DataSunrise can be:

Saved locally with rotation settings (storage guide)
Forwarded via Syslog or Webhooks to SIEM tools
Sent to Microsoft Teams using integration instructions

Each log contains user, host, SQL text, latency, and result code. Use report generators for periodic exports.

Step 5: Add Compliance Context

Go to Compliance Manager and link your audit policies to GDPR, SOX, or PCI DSS templates. DataSunrise highlights untracked areas or weak audit coverage using automated compliance reports.

For example, you can flag missing logs for SELECTs on tables storing PII—helping stay compliant with GDPR.

Why Choose DataSunrise for SQL Server Audit?

Works with on-prem and cloud SQL Server
Does not require changes to existing databases
Offers real-time threat detection based on query behavior (behavior analytics)

If you want to go deeper, the DataSunrise audit guide walks through advanced configurations.

How to Audit Microsoft SQL Server - SQL injection rule configuration in DataSunrise audit module with penalties for risky patterns such as comments, unions, and constants — Architecture diagram showing how server and database audit specifications generate logs routed to audit, application, and event outputs

Summary

Auditing Microsoft SQL Server doesn’t have to be complex. You can start with built-in SQL features and scale up to DataSunrise for full coverage across sensitive data, user activity, and regulatory alignment.

From simple audit logs to full automation with compliance reports, choosing the right tools makes all the difference.

Try the DataSunrise Demo to see how it works live, or explore more in the deployment modes section to match your infrastructure.