How To Enable Auditing on SQL Database Servers or SQL Database on AZURE PORTAL

What is Auditing?

Azure database auditing tracks database events and writes them to an audit log in your azure storage account, log analytics workspace, or Event Hub 

How useful it is?

• Helps you to understand database activities, events and let you know about any vulnerability occured on your sql database or sql database server. 
• By analyzing reports can find any suspicious events, unusual activities and trends.

How much does it costs? What is Retention Period?

Depending upon the logs generated and storage type you choose to store the logs it costs you.

You can Choose to write logs into:

• Storage Account
• Log Analytics Workspace
• Event Hub

Before Selecting the Storage Account please consider your retention period (This period is nothing but the time period for which you want store the logs in storage account or workspace). For storage account you can choose retention days for 1 to 3285 days and if you want to go with infinite you can select retention period as 0 while enabling the auditing on sql database. For log analytics the retention period will be different. As the logs are stored in table of log analytics workspace the table is having retention period to store the data and it is minimum 30 days and it is in range of 30 like you can select 30, 60, 90, 120, 180, 270 days or 1, 1.5, 2 years as retention period of table.

You can choose the destination for writing logs based on your requirement but mostly professionals go with writing logs into log analytics workspace.   

How to Enable it?

You can enable auditing on server level or the database level. if you apply auditing on server level all the database comes under that server will be set for auditing. You can also apply auditing to individual databases.

Follow the below procedure to apply auditing on server level with Storage account you can use same procedure to apply it on database level :

• To enable auditing on database hosted on azure sql server. First go to the azure portal(https://portal.azure.com/#home). 
• Once you login select sql databases or search in search bar of portal and select sql databases.
• Select the database from the environment or subscription you want to apply auditing.
• Then select auditing under security

• Then click on server-level auditing to enable auditing for all databases at server level. This will enable auditing for all databases on server
• Then enable azure sql auditing. Select storage checkbox to store auditing logs to storage account.

• Then select subscription and storage account where logs to be stored.
• Then select managed identity or Storage access key depending upon your storage authentication type.
• Then click advance properties and add retention days.
• Lastly click on save. Auditing for all databases of server will be enabled.

Follow the below procedure to apply auditing on server level with Log Analytics Workspace you can use same procedure to apply it on database level : 

• To enable auditing on database hosted on azure sql server. First go to the azure portal(https://portal.azure.com/#home). 
• Once you login select sql databases or search in search bar of portal and select sql databases.
• Select the database from the environment or subscription you want to apply auditing.
• Then select auditing under security

• Then click on server-level auditing to enable auditing for all databases at server level. This will enable auditing for all databases on server

• Then enable azure sql auditing. Select log analytics checkbox to store auditing logs to Log Analytics Workspace.
• Then select subscription and log analytics workspace where logs to be stored.
• The retention period for logs will be same which is set to tables of that log analytics workspace.
• Lastly click on save. Auditing for all databases of server will be enabled.

How to Check Logs?

To view audit logs that are generated click on view audit logs.

click on server audit under Audit Source if enabled auditing at server level.

By clicking on Run in Query Editor you can able to see logs on query editor where you can filter the logs for investigation.

Tags:

azure auditing, sql azure auditing, enable azure auditing, azure auditing best practices, azure auditing and reportin, azure auditing logs, azure auditingsettings, azure auditing tools, how is azure sql database auditing configured, azure devops auditing, azure sql managed instance auditing, azure files auditing, azure auditing and logging, azure audit api, azure audit app registration, azure audit alerts, auditing azure ad registered applications, azure ad audit logs retention, azure aks audit logs, azure ad audit group membership changes