How to configure SQL audit logs in Fabric Data Warehouse (Preview)

2025-05-19

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. Learn how to enable and configure audit logs in this article.

In the preview of Microsoft Fabric, enabling SQL Audit Logs requires using the Audit API. This guide provides step-by-step instructions on how to configure SQL Audit Logs using Visual Studio Code (VS Code) and the REST Client extension.

Prerequisites

A Fabric workspace with an active capacity or trial capacity.
You must have the Audit permission to configure and query audit logs. For more information, see Permissions.
If you haven't already, download and install Visual Studio Code to download and install the application.
If you haven't already, install the REST Client - Visual Studio Marketplace.

Open your Microsoft Fabric workspace in a browser (Microsoft Edge).
Press F12 to open Developer Tools.
Select the Console tab. If necessary, select Expand Quick View to reveal the console prompt >.
Type the command powerBIAccessToken and press Enter. Right-click on the large unique string returned in the console and select Copy string contents.
Paste it in place of <bearer token> in the following scripts.

Install the MicrosoftPowerBIMgmt module from Microsoft Power BI Cmdlets for Windows PowerShell and PowerShell Core.
```
Install-Module -Name MicrosoftPowerBIMgmt
```

Use Connect-PowerBIServiceAccount to connect to Power BI PowerShell, and retrieve the bearer token.

Connect-PowerBIServiceAccount
$token = (Get-PowerBIAccessToken).Authorization
Write-Output "Bearer $token"

Enable SQL audit logs via the Audit API

Once you have obtained the Power BI bearer token, you can send a PATCH request using the REST Client extension.

In VS Code, create a new text file in VS Code with the .http extension.
Copy and paste the following request:
```
PATCH https://api.fabric.microsoft.com/v1/workspaces/<workspaceId>/warehouses/<warehouseId>/settings/sqlAudit
content-type: application/json
Authorization: Bearer <BEARER_TOKEN>
{
    "state": "Enabled",
    "retentionDays": "0"
}
```
- Replace <workspaceId> and <warehouseId> with the corresponding Fabric workspace and warehouse IDs. To find these values, visit your warehouse in the Fabric portal.
  - <workspaceID>: Find the workspace GUID in the URL after the /groups/ section, or by running SELECT @@SERVERNAME in an existing warehouse. For example, 11aaa111-a11a-1111-1aaa-aa111111aaa. Don't include the / characters.
  - <warehouseID>: Find the warehouse GUID in the URL after the /warehouses/ section, or by running SELECT @@SERVERNAME in an existing warehouse. For example, 11aaa111-a11a-1111-1aaa-aa111111aaa. Don't include the / characters.
- Replace <BEARER_TOKEN> with your bearer token.
- Setting state to "Enabled" activates auditing (use "Disabled" to turn it off).
- The retentionDays parameter is set to 0 by default for unlimited retention.
Select Send Request.

Check audit log status

To verify if the SQL Audit Logs are enabled, send a GET request using the same REST Client extension.

In VS Code, create a new text file in VS Code with the .http extension.
Copy and paste the following request, providing your own workspaceId, <warehouseId>, and <BEARER_TOKEN>.

GET https://api.fabric.microsoft.com/v1/workspaces/<workspaceId>/warehouses/<warehouseId>/settings/sqlAudit
content-type: application/json
Authorization: Bearer <BEARER_TOKEN>

The response returns ENABLED or DISABLED and the current configuration of auditActionsAndGroups.

Configure audit action groups

SQL audit logs rely on predefined action groups that capture specific events within the database. For details on audit action groups, see SQL audit logs in Fabric Data Warehouse.

In VS Code, create a new text file in VS Code with the .http extension.

Copy and paste the following request, providing your own workspaceId, <warehouseId>, and <BEARER_TOKEN>.

POST https://api.fabric.microsoft.com/v1/workspaces/<workspaceId>/warehouses/<warehouseId>/settings/sqlAudit
content-type: application/json
Authorization: Bearer <BEARER_TOKEN>
[  "DATABASE_OBJECT_PERMISSION_CHANGE_GROUP" ]

Select Send Request.

Query audit logs

SQL audit log data is stored in .XEL files in the OneLake, and can only be accessed using the sys.fn_get_audit_file_v2 Transact-SQL (T-SQL) function. For more information on how audit files are stored in the OneLake, see SQL audit logs in Fabric Data Warehouse.

From the SQL query editor or any query tool such as SQL Server Management Studio (SSMS) or the mssql extension with Visual Studio Code, use the following sample T-SQL queries, providing your own workspaceId and <warehouseId>.

SELECT * FROM sys.fn_get_audit_file_v2('https://onelake.blob.fabric.microsoft.com/<workspaceId>/<warehouseId>/Audit/sqldbauditlogs/', default, default, default, default)

To filter logs by time range, use the following query:

SELECT * FROM sys.fn_get_audit_file_v2('https://onelake.blob.fabric.microsoft.com/<workspaceId>/<warehouseId>/Audit/sqldbauditlogs/', default, default, '2025-03-30T08:40:40Z', '2025-03-30T09:10:40Z')

Share via