How to Enable and Verify SQL Auditing for Azure SQL Managed Instance

If you’re running workloads on Azure SQL Managed Instance (SQL MI) and need to track who changed what data, when it happened, and from where, SQL Auditing is the right place to start.

This guide walks through how to:

Enable auditing for Azure SQL MI
Capture SELECT / INSERT / UPDATE / DELETE activity
Write audit logs to Azure Blob Storage
Read and troubleshoot .xel audit files using SQL
(Optional) Stream audit logs to Log Analytics for easier reporting

Why SQL Auditing matters

SQL Auditing helps you answer key operational and compliance questions:

Who ran a query?
What SQL statement was executed?
Which database/table/object was accessed?
Was it successful?
What was the client IP / host / application?

This is especially important for:

Security monitoring
Compliance (PCI DSS, ISO 27001, HIPAA, etc.)
Change investigations
Data access reviews

What SQL Auditing captures (and what it doesn’t)

SQL Auditing is great for:

SELECT / INSERT / UPDATE / DELETE statements
Login success / failure
Principal/user identity
Time of execution
Client IP / application name
Object and schema details

SQL Auditing is not ideal for:

Full before/after row values (row-level diffs)

If you need row history or before/after values, combine auditing with:

Temporal Tables (best for row version history)
CDC (Change Data Capture) (best for change feeds / ETL)

Architecture used in this setup

Recommended setup

SQL Audit to Azure Blob Storage (stores .xel audit files)
Diagnostic Settings → Log Analytics (optional but strongly recommended)
Server Audit Specification for batch + logins
Database Audit Specification for explicit CRUD on target DB

Step 1: Create a Blob container for audit logs

In the Azure Portal:

Open your Storage Account (use a generic name like stsqlauditprod001)
Go to Containers
Create a new private container (example: sql-audit-logs)
Note the container URL, e.g.:

https://<storage-account>.blob.core.windows.net/sql-audit-logs

Step 2: Generate a SAS token for the container

In the Storage Account:

Go to Shared access signature
Enable permissions required for writing audit files
Set a sensible expiry (for example 6–12 months)
Generate the SAS token
Copy the token and remove the leading ? before using it in SQL

Step 3: Create the SQL credential and server audit (T-SQL)

Connect to your Azure SQL Managed Instance using SSMS or Azure Data Studio, and run the following in the master database.

3.1 Create a credential for the Blob container

USE [master];
GOCREATE CREDENTIAL [https://<storage-account>.blob.core.windows.net/sql-audit-logs]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<sas_token_without_question_mark>';
GO

3.2 Create a server audit to write to Blob

CREATE SERVER AUDIT [MI_Audit_To_Blob]
TO URL (
    PATH = 'https://<storage-account>.blob.core.windows.net/sql-audit-logs',
    RETENTION_DAYS = 90
);
GO

3.3 Enable the server audit

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

Step 4: Configure what gets audited

There are two layers:

Server Audit Specification (server-level events, query batches, login events)
Database Audit Specification (database-level CRUD actions)

4.1 Server Audit Specification (captures query batches + logins)

Run in master:

USE [master];
GOCREATE SERVER AUDIT SPECIFICATION [MI_Audit_ServerSpec]
FOR SERVER AUDIT [MI_Audit_To_Blob]
    ADD (BATCH_COMPLETED_GROUP),
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON);
GO

Why `BATCH_COMPLETED_GROUP` matters

This is the key group that helps capture executed SQL batches (including many CRUD statements).

Without this, audit files may be written but contain fewer details than expected.

4.2 Database Audit Specification (explicit CRUD auditing)

Run this in your target user database (replace <AppDatabase> with your actual DB name):

USE [<AppDatabase>];
GOCREATE DATABASE AUDIT SPECIFICATION [AppDB_CRUD_AuditSpec]
FOR SERVER AUDIT [MI_Audit_To_Blob]
    ADD (SELECT ON DATABASE::[<AppDatabase>] BY [public]),
    ADD (INSERT ON DATABASE::[<AppDatabase>] BY [public]),
    ADD (UPDATE ON DATABASE::[<AppDatabase>] BY [public]),
    ADD (DELETE ON DATABASE::[<AppDatabase>] BY [public])
WITH (STATE = ON);
GO

This gives explicit CRUD coverage at the database level and is very useful for compliance reporting.

Step 5: Validate the audit configuration

Before troubleshooting logs, confirm the objects are in place.

5.1 Check credentials

USE [master];
GOSELECT 
    name AS credential_name,
    credential_identity,
    create_date,
    modify_date
FROM sys.credentials
ORDER BY name;

You should see a credential named like:

https://<storage-account>.blob.core.windows.net/sql-audit-logs

5.2 Check server audit (ON/OFF status)

SELECT
    name,
    is_state_enabled,   -- 1 = ON, 0 = OFF
    type_desc,
    create_date,
    modify_date
FROM sys.server_audits
ORDER BY name;

5.3 Check server audit specification (what is being captured)

SELECT
    sas.name AS server_audit_spec_name,
    sa.name  AS server_audit_name,
    sas.is_state_enabled,
    d.audit_action_name
FROM sys.server_audit_specifications sas
JOIN sys.server_audits sa
    ON sas.audit_guid = sa.audit_guid
JOIN sys.server_audit_specification_details d
    ON sas.server_specification_id = d.server_specification_id
ORDER BY sas.name, d.audit_action_name;

Look for these actions:

BATCH_COMPLETED_GROUP
SUCCESSFUL_LOGIN_GROUP
FAILED_LOGIN_GROUP

5.4 Check database audit specification (CRUD)

Run in your application database:

USE [<AppDatabase>];
GOSELECT
    das.name AS db_audit_spec_name,
    das.is_state_enabled,
    dd.audit_action_name,
    dd.class_desc
FROM sys.database_audit_specifications das
JOIN sys.database_audit_specification_details dd
    ON das.database_specification_id = dd.database_specification_id
ORDER BY das.name, dd.audit_action_name;

Look for:

SELECT
INSERT
UPDATE
DELETE

Step 6: Generate test activity

After enabling auditing, run a few test statements so there’s data to inspect.

USE [<AppDatabase>];
GOSELECT TOP 1 name FROM sys.tables;
GO

If you have a safe test table, run a few CRUD operations as well.

Step 7: Read audit logs from Blob using SQL

Azure SQL MI writes audit files in .xel format (Extended Events audit files).

7.1 Read all `.xel` files in a date folder

Important: Use *.xel in the path, not just *

SELECT TOP (200)
    event_time,
    action_id,
    succeeded,
    server_principal_name,
    database_principal_name,
    client_ip,
    application_name,
    database_name,
    schema_name,
    object_name,
    statement
FROM sys.fn_get_audit_file(
    'https://<storage-account>.blob.core.windows.net/sql-audit-logs/<mi-name>/master/MI_Audit_To_Blob_<year>/<yyyy-mm-dd>/*.xel',
    DEFAULT,
    DEFAULT
)
ORDER BY event_time DESC;

7.2 If the wildcard query returns no rows, test a single file directly

If you can see an .xel file in the container but no rows are returned, test the exact file path:

SELECT TOP (200)
    event_time,
    action_id,
    succeeded,
    server_principal_name,
    database_principal_name,
    client_ip,
    application_name,
    database_name,
    schema_name,
    object_name,
    statement
FROM sys.fn_get_audit_file(
    'https://<storage-account>.blob.core.windows.net/sql-audit-logs/<mi-name>/master/MI_Audit_To_Blob_<year>/<yyyy-mm-dd>/<file-name>.xel',
    DEFAULT,
    DEFAULT
)
ORDER BY event_time DESC;

This is a very common troubleshooting step and often confirms the issue is just a wildcard/path mismatch.

Common issue: Audit file exists but SQL returns no rows

This happens often, and the good news is it’s usually easy to fix.

Common causes and fixes

1) Wrong wildcard pattern

Problem: Using .../<date>/*
Fix: Use .../<date>/*.xel

2) Audit spec does not include query batches

Problem: Audit files are created, but no SQL statement events appear
Fix: Ensure the server audit spec includes:

BATCH_COMPLETED_GROUP

3) CRUD actions were not configured at DB level

Problem: You expect explicit SELECT/INSERT/UPDATE/DELETE records but only see logins or limited activity
Fix: Create a Database Audit Specification for CRUD on the target DB

4) Reading the active append blob too quickly

Azure SQL MI writes audit files as append blobs. Sometimes the active file is still being written and may not show all events immediately.

Fixes:

Wait 1–2 minutes
Refresh the blob container
Generate a few more test queries
Read an older/closed .xel file if available

Optional but recommended: Send audit events to Log Analytics

Reading .xel files is fine for low-level troubleshooting, but for daily operations, Log Analytics is much easier.

Enable in Azure Portal

On the SQL Managed Instance resource:

Open Diagnostic settings
Click Add diagnostic setting
Enable:
- SQLSecurityAuditEvents
Send to:
- Log Analytics Workspace (recommended)
Save

Query audit events in Log Analytics (KQL)

Once enabled, use this query to review activity:

SQLSecurityAuditEvents
| where TimeGenerated > ago(24h)
| project TimeGenerated, ActionName, Succeeded,
          ServerPrincipalName, DatabasePrincipalName,
          ClientIp, HostName, ApplicationName,
          DatabaseName, SchemaName, ObjectName,
          Statement, AffectedRows
| order by TimeGenerated desc

This makes it much easier to answer:

Who queried or changed data?
Which application connected?
What was the client IP?
Which table/object was targeted?

Security and operational best practices

1) Separate storage for audit logs

Use a dedicated storage account/container for audit files where possible.

2) Restrict access to audit storage

Only DBAs / security admins should have access to the audit container.

3) Use retention and lifecycle policies

Apply lifecycle rules on the storage account (e.g., move older audit files to Cool/Archive tier).

4) Monitor cost

Auditing SELECT on busy systems can generate a lot of data. Start broad, then refine if needed.

5) Pair with row-history features where needed

For critical business tables, combine auditing with:

Temporal Tables (history)
CDC (change feed)

Quick troubleshooting checklist

If audit files are being created but no rows are visible in SQL:

Confirm the audit is enabled (is_state_enabled = 1)
Confirm the server audit spec includes BATCH_COMPLETED_GROUP
Confirm DB audit spec includes SELECT/INSERT/UPDATE/DELETE
Use *.xel in the path (not just *)
Test the exact file path
Generate fresh SQL activity after enabling audit specs
Wait briefly if the file is still the active append blob

1) Check if a Blob audit credential already exists (T-SQL)

Run this in master:

USE [master];
GOSELECT 
    name AS credential_name,
    credential_identity,
    create_date,
    modify_date
FROM sys.credentials
ORDER BY name;

If you want to check for your audit storage URL specifically

(Replace with your storage container URL)

SELECT 
    name,
    credential_identity,
    create_date,
    modify_date
FROM sys.credentials
WHERE name = 'https://<storageaccount>.blob.core.windows.net/sqlaudit';

If you see a row, the credential exists.
credential_identity should usually be SHARED ACCESS SIGNATURE for MI audit-to-blob.

2) Check if a Server Audit is already created and whether it is ON/OFF

USE [master];
GOSELECT
    name,
    is_state_enabled,      -- 1 = ON, 0 = OFF
    type_desc,
    queue_delay,
    on_failure_desc,
    create_date,
    modify_date
FROM sys.server_audits
ORDER BY name;

Also check the audit destination (URL path)

SELECT
    a.name AS audit_name,
    af.audit_file_path,
    af.max_file_size,
    af.max_rollover_files,
    af.reserve_disk_space
FROM sys.server_audits a
LEFT JOIN sys.server_file_audits af
    ON a.audit_guid = af.audit_guid;

For MI audit-to-blob, you should see the Blob URL path in audit_file_path.

3) Check if Server Audit Specifications exist (what is being captured)

This tells you whether things like BATCH_COMPLETED_GROUP, SUCCESSFUL_LOGIN_GROUP, etc. are configured.

USE [master];
GOSELECT
    sas.name AS server_audit_spec_name,
    sa.name  AS server_audit_name,
    sas.is_state_enabled,
    d.audit_action_name,
    d.audit_action_id,
    d.class_desc,
    d.major_id,
    d.principal_id
FROM sys.server_audit_specifications sas
JOIN sys.server_audits sa
    ON sas.audit_guid = sa.audit_guid
JOIN sys.server_audit_specification_details d
    ON sas.server_specification_id = d.server_specification_id
ORDER BY sas.name, d.audit_action_name;

Quick check for login + batch auditing

SELECT
    sas.name,
    sas.is_state_enabled,
    d.audit_action_name
FROM sys.server_audit_specifications sas
JOIN sys.server_audit_specification_details d
    ON sas.server_specification_id = d.server_specification_id
WHERE d.audit_action_name IN ('BATCH_COMPLETED_GROUP', 'SUCCESSFUL_LOGIN_GROUP', 'FAILED_LOGIN_GROUP');

4) Check Database Audit Specifications (CRUD) in your database `DB1`

Run this inside the DB1database:

USE [DB1];
GOSELECT
    das.name AS db_audit_spec_name,
    das.is_state_enabled,
    dd.audit_action_name,
    dd.class_desc,
    dd.major_id,
    OBJECT_SCHEMA_NAME(dd.major_id) AS schema_name,
    OBJECT_NAME(dd.major_id) AS object_name,
    USER_NAME(dd.audited_principal_id) AS audited_principal
FROM sys.database_audit_specifications das
JOIN sys.database_audit_specification_details dd
    ON das.database_specification_id = dd.database_specification_id
ORDER BY das.name, dd.audit_action_name;

Quick check for CRUD actions

USE [DB1];
GOSELECT
    das.name,
    das.is_state_enabled,
    dd.audit_action_name
FROM sys.database_audit_specifications das
JOIN sys.database_audit_specification_details dd
    ON das.database_specification_id = dd.database_specification_id
WHERE dd.audit_action_name IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE')
ORDER BY das.name, dd.audit_action_name;

5) Check if audit logs are actually being written (proof)

Option A: Read from audit files in Blob

If you know the container path:

SELECT TOP (50)
    event_time,
    action_id,
    succeeded,
    server_principal_name,
    database_name,
    schema_name,
    object_name,
    statement,
    client_ip,
    application_name
FROM sys.fn_get_audit_file(
    'https://<storageaccount>.blob.core.windows.net/sqlaudit/*',
    DEFAULT,
    DEFAULT
)
ORDER BY event_time DESC;

Final thoughts

Azure SQL MI auditing is a powerful foundation for visibility, compliance, and incident response. With the right setup, you can quickly answer:

Who accessed or changed data
What they ran
When it happened
From where they connected

Start with Blob-based auditing, add Database CRUD auditing for your critical databases, and stream everything to Log Analytics for easier search and reporting.

If you want, I can also generate a companion article for:

Temporal Tables for before/after row history
CDC for change tracking pipelines
Azure Monitor alerts for suspicious SQL activity

How to Enable and Verify SQL Auditing for Azure SQL Managed Instance