This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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
You can view the audit logs by querying the sys.fn_get_audit_file function.
i need to setup audit logs first to view them,
how to write it to azure storage account from azure sql.
it throws error
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'AUDIT_LOG_MONITOR'.
it throws error
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'AUDIT_LOG_MONITOR'.
This is not working, can you please share the correct syntaxt that i can run on azure sql.
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.
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
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