This article shows how to enable server and database auditing settings using an Azure Resource Manager (ARM) template. Additionally, it explores the public preview feature of using Azure Log Analytics as the designated sink for these auditing logs. Together, the server and database logs will help in securing your analytics landscape and the ARM template will help you provision these features consistently.

Why audit your servers and databases

Azure SQL Server and SQL Database auditing help to make your server and databases more secure. Auditing can detect and log successful and failed logins, enabling the administrator to see who has access to the data. This helps to quickly identify malicious events like brute forcing attempts and take corresponding action.

Database and server level auditing collect valuable information like succeeded or failed database authentications, transaction rollbacks, transaction commits and completed SQL batches. These events get logged along with information like the target server name, host name of the caller and IP address of the caller. These events and their information will prove to be valuable assets for identifying and mitigating possible attacks.

An auditing policy can be defined on both the server level and the database level. Server level auditing will log actions on the ‘master’ database. Database level auditing will collect the same set of actions for specific databases.

If you choose to enable auditing on either server or database level, you will be asked to choose a sink. This is a storage method that collects all audit log files for further analysis. One sink type lets you define a server level audit policy which is propagated to all databases on that server: Blob Storage. In this scenario, all the logs are written to ever growing text files in a storage account. Since this is not the easiest method for log analysis – I’ve placed it out of scope for this article. However, I found mentioning this propagation of audit policy relevant since it can reduce the amount of configuration you need to do.

Why use ARM templates

If you find yourself spinning up databases regularly, configuring the auditing settings in your ARM template makes sense. The use of ARM templates makes sure you provision consistent environments across the board. By including auditing settings in your template all these environments get consistently audited. This could help in making all your database environments compliant with SIEM or regulatory requirements.

Not all Azure SQL Server and SQL Database deployments and configurations warrant use of ARM templates. It’s fair to state that enabling server and database auditing is easy through use of the Azure portal. When you already have a SQL Server, SQL Database and Log Analytics workspace, enabling auditing is just a few clicks away. And if your database landscape is not subject to frequent change, I advise you to configure these auditing settings manually.

Choosing an audit logs sink type

When configuring your auditing settings, you’ll need to appoint a sink for the log files. The current options are Storage Account, Event Hub and Log Analytics. Only the storage account option is currently generally available. The Event Hub and Log Analytics options are currently in public preview.

Although in preview; using Log Analytics as your designated sink for the audit logs provides many advantages over Storage Accounts and Event Hubs. It enables easy analysis using the Kusto query language and visualization options like line or bar charts for trend analysis. In addition, the results of the Kusto queries can easily be turned into alerts to notify operators of any potentially harmful events occurring on the database. These out-of-the-box analysis, visualization and alerting features make it a good option to explore.

The feature that is currently generally available is using append blobs in a Storage Account. These blobs can be downloaded by either a person or application for further analysis. This feature is implemented quite similarly to Log Analytics, but is out op scope for this article as mentioned before. Forwarding the logs to an Event Hub does not offer a complete analysis or alerting solution. A consuming solution needs to read the logs of the Event Hub for analysis, alerting or both. This option allows for highly customizable solutions. Because of this, it’s left out of the scope of this article.

Setting up the environment

Let’s get started by creating an ARM template that contains the resources we require: Azure SQL Server, SQL Database and a Log Analytics workspace. I’ve created an Azure resource group by the name of ‘auditdemorg’ to deploy the resources in. We’ll use the template to deploy the resources to an Azure subscription. See the provided sample below: 

Setting up server level auditing

The above template does not enable server or database level auditing. This can be verified in the Azure Portal by navigating to the server resource and opening the ‘Auditing’ blade. As the below screenshot shows, the server auditing is turned off.

Let’s enable server level auditing through the ARM template. For this, we’ll add two new resources to the child resources of the server. The first resource enables the forwarding of our master database’s diagnostics logs (or resource logs) to our Log Analytics workspace. It’s of the type ‘Microsoft.Sql/servers/databases/providers/diagnosticSettings’.

The second resource enables the audit settings on the server level. It’s of the type ‘Microsoft.Sql/servers/auditingSettings’. Please note that you should implement both resources to get the desired output.

After deploying our new ARM template to our Azure subscription, let’s take another look at the server’s ‘Auditing’ blade in the portal. You can see that the server’s auditing logs are being forwarded to the Log Analytics workspace.

The Log Analytics workspace should now record ‘SQLSecurityAuditEvents’ from the master database. These are stored in the ‘AzureDiagnostics’ table. It might take a few minutes for the audit logs to be forwarded to the workspace, so don’t be alarmed if at first you cannot access the table.

Setting up database level auditing

Although the server is forwarding the master database’s auditing logs, the database ‘audit-db’ is not yet forwarding any logs. We can verify this in the portal by looking at both the SQL Database resource and our Log Analytics workspace.

Just like the server, the SQL Database resource includes an ‘Auditing’ blade. This blade displays information on both the server level audit policy and the database level audit policy. In our case, the blade should state that server level auditing is enabled, but database level auditing is not. Querying our Log Analytics workspace will confirm that no logs from the database are being forwarded yet.

We will need to add additional resources to our ARM template to enable database level auditing. These resources are very similar to the ones we’ve added for our master database. Again, we’ll add ‘diagnosticSettings’ and ‘auditingSettings’ resources. The ‘diagnosticSettings’ resource is added as a server child resource. The ‘auditingSettings’ resource is added as a database child resource.

Now that our template is complete, let’s deploy it to our Azure subscription. After the deployment is finished, the final result can be verified by looking at the SQL Database ‘auditing’ blade. This should now show that database level auditing is enabled. The logs should be forwarded to the Log Analytics workspace.

Our Log Analytics workspace should confirm that auditing logs from both the master and audit-db databases are being forwarded. Use a client tool (e.g. SSMS) to log in to the database to elicit a login event. Query the Log Analytics workspace to verify the result. Please note that it takes a few minutes for the logs to be queryable in the Log Analytics workspace.

We are now finished. We’ve enabled both server level auditing and database level auditing using an ARM template. By adding parameter files for each of your different environments, you can now consistently create audited databases and servers – making them more resilient and compliant. Please find the complete template on my GitHub page.