how to write sql server audit log to azure storage account

k shaz 36

i have azure sql , and i need to use bellow script to enable audit for selected objects, but i am not able to write the logs to azure storage account, i am getting below error

Msg 40517, Level 15, State 5, Line 7

Keyword or statement option 'FILE' is not supported in this version of SQL Server.

-- Use the master database for creating the audit
USE master;
GO

-- Create a Server Audit object
CREATE SERVER AUDIT DataAudit
--- TO azure storage account
WITH (ON_FAILURE = CONTINUE);
GO

-- Enable the Server Audit
ALTER SERVER AUDIT DataAudit
WITH (STATE = ON);
GO

Vahid Ghafarpour 17,875

To enable audit logs in Azure SQL Database and store them in an Azure storage account, you need to use Azure SQL Database Auditing, which is a built-in feature of Azure SQL Database.

-- Create a server-level audit
CREATE SERVER AUDIT DataAudit
    TO AUDIT_LOG_MONITOR;

-- Enable the server-level audit
ALTER SERVER AUDIT DataAudit
    WITH (STATE = ON);

-- Create a database-level audit specification
USE YourDatabaseName;
GO

CREATE DATABASE AUDIT SPECIFICATION ObjectAudit
    FOR SERVER AUDIT DataAudit
    ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.YourTable);

-- Enable the database-level audit specification
ALTER DATABASE AUDIT SPECIFICATION ObjectAudit
    WITH (STATE = ON);

Please check following link for more details

https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview?view=azuresql

Vahid Ghafarpour 17,875 Reputation points

2023-05-23T20:23:37.2966667+00:00

You can view the audit logs by querying the sys.fn_get_audit_file function.
k shaz 36 Reputation points

2023-05-23T20:39:20.44+00:00

i need to setup audit logs first to view them,

how to write it to azure storage account from azure sql.
k shaz 36 Reputation points

2023-05-23T21:13:04.8033333+00:00

it throws error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near 'AUDIT_LOG_MONITOR'.
k shaz 36 Reputation points

2023-05-23T21:13:15.87+00:00

it throws error

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near 'AUDIT_LOG_MONITOR'.
k shaz 36 Reputation points

2023-05-23T21:44:00.94+00:00

This is not working, can you please share the correct syntaxt that i can run on azure sql.

2 answers

Tamarick Hill 11 Reputation points Microsoft Employee

2023-05-24T13:35:45.7866667+00:00

As stated above, this is a built in feature to the Azure SQL Service. Assuming you are using Azure SQL Database, go into the logical server settings within the Azure Portal. Then under the Security section, you should see an option for 'Auditing'. Once you click that Auditing tab, you can enable auditing for the server and send your logs to a storage account.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Oury Ba-MSFT 16,156 Reputation points Microsoft Employee

2023-05-24T18:32:20.8866667+00:00

k shaz Thank you for reaching out.

My understanding is that you are trying to set up auditing for your Azure SQL database.

In addition to @Tamarick Hill 's answer.

For Azure SQL, the portal experience is the best way to do it, we don't have t-SQL support yet. Please check the doc below for step-by-step instructions.

Set up Auditing for Azure SQL Database and Azure Synapse Analytics

Regards,

Oury
Please sign in to rate this answer.
Oury Ba-MSFT 16,156 Reputation points Microsoft Employee

2023-05-25T15:47:56.81+00:00

k shaz Please don't forget to mark as accept answer wherever information provided were helpful. Please do comment below if you face any issues.

Regards,

Oury
Sign in to comment