Edit

Share via

SQL audit logs in Fabric Data Warehouse

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

Auditing in Fabric Data Warehouse provides enhanced security and compliance capabilities by tracking and recording database events.

With SQL audit logs, you can monitor database activities, detect potential security threats, and meet compliance requirements by maintaining an audit trail of key actions, such as:

  • Authentication attempts and access control changes
  • Data access and modification operations
  • Schema changes and administrative activities
  • Permission changes and security configurations

Important

By default, SQL audit logs are OFF. Users with Audit queries permissions must enable it to capture the logs.

To get started, review the steps in How to configure SQL audit logs in Fabric Data Warehouse.

Storage

SQL audit logs are encrypted at rest and stored in OneLake.

For Fabric Data Warehouse, audit logs are written to .XEL files stored in the warehouse Audit folder in OneLake.

Users with the following roles can access the audit folder:

  • Workspace Admins
  • Workspace Members
  • Workspace Contributors
  • Workspace Viewers with Read All permission

These users can:

  • Browse the Audit folder
  • View the .XEL audit files generated by SQL auditing
  • Copy the files for offline analysis
  • Open the files with tools such as SQL Server Management Studio (SSMS)

You can also query audit logs with T-SQL via sys.fn_get_audit_file_v2.

For instructions, see How to configure SQL audit logs in Fabric Data Warehouse.

Tip

Configuring audit logs in Microsoft Fabric Data Warehouse can increase storage costs depending on the action groups and events recorded. Enable only the required events to avoid unnecessary storage costs.

Performance

The SQL audit logs feature is optimized for availability and performance of the database being audited. During periods of very high activity or high network load, the auditing feature might allow transactions to proceed without recording all of the events marked for auditing.

Permissions

Users must have the Audit queries (Audit) permission to configure and query audit logs.

  • By default, Workspace Admins have the Audit queries permission to all items in the workspace.
  • Admins can grant Audit queries permissions on items to other users via the share dialog box.

Workspace Admins can grant Audit queries permissions to an item using the shared menu option in the Fabric portal. To verify if a user has Audit queries permissions, check the Manage Permissions settings.

  1. In your Warehouse item, select the Share button.

    Or, in the Fabric portal, in your workspace. Select the ... context menu for your Warehouse item, select Manage permissions.

  2. In the Grant people access pane, you can grant permissions to a user.

    Screenshot showing where to select the Audit queries (Audit) permission on the item Share menu.

Querying audit logs using T-SQL permissions

Users can also be granted the ability to query audit logs through T-SQL permissions by granting the VIEW DATABASE SECURITY AUDIT permission, even if they do not have workspace administrative roles.

Granting the following permission allows a user to query audit logs using the sys.fn_get_audit_file_v2 function:

GRANT VIEW DATABASE SECURITY AUDIT TO [user];

Tip

The VIEW DATABASE SECURITY AUDIT permission only grants the ability to query audit logs and does not allow access to the files or to the user to perform any modification of audit configuration.

Database-level audit action groups and actions

To make audit log configuration more accessible, the Fabric portal uses friendly names to help non-SQL Admins and other users easily understand captured Fabric Data Warehouse events.

Fabric maps these friendly names to the underlying SQL Audit action groups. Use the following table as a reference.

Friendly Name Action Group Name Description
Object Was Accessed DATABASE_OBJECT_ACCESS_GROUP Logs access to database objects like message types, assemblies, or contracts.
Object Was Changed DATABASE_OBJECT_CHANGE_GROUP Logs CREATE, ALTER, or DROP operations on database objects.
Object Owner Changed DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP Logs ownership changes of database objects.
Object Permission Was Changed DATABASE_OBJECT_PERMISSION_CHANGE_GROUP Logs GRANT, REVOKE, or DENY actions on database objects.
User Was Changed DATABASE_PRINCIPAL_CHANGE_GROUP Logs creation, alteration, or deletion of database principals (users, roles).
User Was Impersonated DATABASE_PRINCIPAL_IMPERSONATION_GROUP Logs impersonation operations (such as EXECUTE AS).
Role Member Was Changed DATABASE_ROLE_MEMBER_CHANGE_GROUP Logs addition or removal of logins from a database role.
User Failed To Log In FAILED_DATABASE_AUTHENTICATION_GROUP Logs failed authentication attempts within the database.
Schema Permission Was Used SCHEMA_OBJECT_ACCESS_GROUP Logs access to schema objects.
Schema Was Changed SCHEMA_OBJECT_CHANGE_GROUP Logs CREATE, ALTER, or DROP operations on schemas.
Schema Object Permission Was Checked SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP Logs changes to schema object ownership.
Schema Object Permission Was Changed SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP Logs GRANT, REVOKE, or DENY actions on schema objects.
Batch Was Completed BATCH_COMPLETED_GROUP This event is raised whenever any batch text, stored procedure, or transaction management operation completes executing.
Batch Was Started BATCH_STARTED_GROUP This event is raised whenever any batch text, stored procedure, or transaction management operation starts to execute.
Audit Was Changed AUDIT_CHANGE_GROUP This event is raised whenever any audit is created, modified, or deleted.
User Logged Out DATABASE_LOGOUT_GROUP This event is raised when a database user signs out of a database.
User Logged In SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP Indicates that a principal successfully logged in to a database.

Database-level audit actions

In addition to action groups, you can configure individual audit actions to log specific database events:

Audit Action Description
SELECT Logs SELECT statements on a specified object.
INSERT Logs INSERT operations on a specified object.
UPDATE Logs UPDATE operations on a specified object.
DELETE Logs DELETE operations on a specified object.
EXECUTE Logs execution of stored procedures or functions.
RECEIVE Logs RECEIVE operations on Service Broker queues.
REFERENCES Logs permission checks involving foreign key constraints.

Limitations

  • Your default workspace doesn't support SQL audit logs.
  • SQL audit logs aren't supported for warehouse snapshots.

Important

Audit logs are stored within the Warehouse item in OneLake. If you delete the Warehouse, you also delete the associated audit log files and can't access them anymore.
To retain audit logs for compliance or investigation purposes, copy the .XEL files to another storage location before deleting the Warehouse.

SQL analytics endpoint limitations

The following limitations apply when auditing SQL analytics endpoints:

  • DML operations aren't captured. The audit doesn't record operations such as INSERT, UPDATE, DELETE, and MERGE because data manipulation for Lakehouse tables occurs through the Lakehouse runtime rather than through the SQL analytics endpoint.
  • Direct access to the audit folder isn't currently supported. Users can't browse or download the underlying .XEL audit files from the Lakehouse audit folder.

You can still query audit events for SQL analytics endpoints with the T-SQL function sys.fn_get_audit_file_v2.

Next step

Configure SQL audit logs in Fabric Data Warehouse

Related content

  • Last updated on