Demystifying Azure Security - Azure SQL Database - Auditing & Threat Detection

This blog post is part of the Demystifying Azure Security series. All posts in the series can be found here: Demystifying Azure Security - Series Index

Auditing & Threat Detection in Azure SQL Database is a very simple to configure yet very powerful security feature. Auditing feature audits all activity on your database to a Storage Account. You can determine the number of days for which you want to retain the data. It helps you remain compliant. In an event of any failure or compliance breach, you can go to the audit logs and can pinpoint the exact cause of the issue if this feature is enabled.

Threat Detection is an advanced feature, where Microsoft runs various algorithms under the hood and determines the pattern and identifies any potential attacks on your data. E.g. SQL Injection or patterns like SQL Injection can be detected when this feature is enabled. Please note that the Threat Detection feature has additional cost linked to it. It costs $15/server/month. It will be free for the first 60 days. Note that you can enable Auditing without enabling Threat Detection. But you can't enable Threat Detection without enabling Auditing on the data first.

SQL Threat Detection integrates alerts with Azure Security Center. If any anomalous activity is detected an alert is raised, you can get notification via email and can also review the same within the portal. You get real-time actionable alerts. Each alert also contains the information regarding how to mitigate the alert.

Configurations

To configure Auditing and Threat Detection at the database level, navigate to the database. Then follow the below steps:

1. In the database settings, click on "Auditing and Threat Detection"
2. You can optionally configure the settings at the Server level by click on the link "View server settings"
3. Next, toggle the "Auditing" setting on or off. Select the storage account and retention in the number of days.
4. Next, you can configure the "Threat Detection" on or off. If you toggle it on, then you have the option of selecting which type of Threats you want to detect.
5. You also have the option of configuring Email notifications which work with the Threat Detection.

When configuring Audit Logs Storage, you can select any subscription under the tenant and a storage account in that subscription. You can then select Retention in number of Days. When this number is set to Zero then that means unlimited retention. You can select a maximum of 3285 number of days for this value. You can also select whether to use a Primary or Secondary key while accessing the Storage Account for writing the logs.

Under Threat Detection types, you can select any one or all of the following types:

1. SQL injection
2. SQL injection vulnerability
3. Anomalous client login

Enabling at Database level vs Server level

If Blob Auditing or Threat Detection are enabled on the server, they will always apply to the database, regardless of the database settings.

At the server level, the configuration is almost identical. You need to navigate to the related Azure SQL Server first (instead of the SQL Database). Notice at the top of the below screenshot, it says "SQL server" instead of "SQL database". Then navigate to it's "Auditing and Threat Detection" section and perform the configurations similar to above sections.