Auditing in Azure SQL Data Warehouse

The first article in a series on “Security Intelligence in Azure PaaS” inspired me to write something on auditing in Azure SQL Data Warehouse. To put it simply for anyone new to GRC (which stands for Governance, Risk Management, Compliance), auditing is just like logging, but in a more expensive suit and tie. Business tends to understand its purpose and requirements and is aware of the attached price tag. Azure SQL Data Warehouse is relatively new MPP (Massively Parallel Processing architecture) database offering. It is based on Parallel Data Warehouse with Generation 2 (optimised computing) generally available, but some regions and performance levels still need to submit support requests. On the surface, it’s a kind of SQL Server, but behind the scenes it’s a different beast altogether. This might explain the inconsistent experience people have with Azure Portal compared to the more mature Azure SQL Database.

How to enable audit?

Auditing is disabled by default and the UI experience depends on the region to which the logical server is deployed. For instance, in UK South, the portal offers no options to manage auditing:

In North Europe, the portal allows Table Auditing (table-storage based) to be enabled on the SQL Data Warehouse scope, but it isn’t possible to enable Blob Auditing:

On top of that, Blob Auditing behaves differently when enabled on a logical server level in different regions. In locations that support Table Auditing, turning on Blob Auditing automatically enables it in all databases, including SQL Data Warehouses—and that’s expected. In other regions, Blob Auditing is not automatically enabled and has to be turned on programmatically by calling ARM REST API. The PowerShell script to enable Blob Auditing can be found here:

<#
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqldatabaseauditing
#>
$setAzureRmSqlDatabaseAuditingSplat = @{
    ResourceGroupName = "ResourceGroupName"
    ServerName = "ServerName"
    DatabaseName = "DatabaseName"
    StorageAccountName = "StorageAccountName"
    State = "Enabled"
}
Set-AzureRmSqlDatabaseAuditing @setAzureRmSqlDatabaseAuditingSplat

Overall Blob Auditing seems to be the best option—it’s a common denominator that is available across all regions and SQL PaaS resources, and it is based on the SQL Audit feature with access to specifications that can be adjusted to individual business requirements. Details including audit actions and groups can be found on the reference page (follow the link above). The fact that Table Auditing has already been deprecated in SQL Databases might also help with this decision.

How to modify audit specifications?

Back in February, I wrote a post describing how to extract the time of the last successful geo-backup in Azure SQL Data Warehouse. The key takeaway was to “trust, but verify”, which I usually recommend in context of security in Azure. With a little help from the auditing process, it’s possible to verify quite a lot of managed operations, like backups and DBCC checks—at least in theory. By default, audit specification consists of three groups:

• SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
• FAILED_DATABASE_AUTHENTICATION_GROUP
• BATCH_COMPLETED_GROUP

The query to get actions for audited groups can be found here:

SELECT *
FROM sys.dm_audit_actions
WHERE containing_group_name IN (
     'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP',
     'FAILED_DATABASE_AUTHENTICATION_GROUP',
     'BATCH_COMPLETED_GROUP'
)

And now to put them into practice!

When I queried DMV above for available groups, I noticed DBCC_GROUP. In Azure SQL PaaS, Microsoft is the party responsible for consistency checks and fixing data corruptions, not customers. To live by “trust, but verify”, it would be useful to audit these checks, but attempts to add DBCC_GROUP didn’t go well:

Next I decided to add AUDIT_CHANGE_GROUP (listed as one of the enumerator names), because it’s good practice to monitor any changes to auditing and other security controls. They are one of first targets I would expect to see hit when an attacker starts moving laterally, but I got:

WARNING: The action group ‘AUDIT_CHANGE_GROUP’ is not supported. It will be removed in a future release.

So DMVs, messages from cmdlets and documentation is a little hit and miss at the moment. After some research, I decided to add BACKUP_RESTORE_GROUP, DATABASE_OPERATION_GROUP and USER_CHANGE_PASSWORD_GROUP:

$AuditActionGroup = @("SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
                      "FAILED_DATABASE_AUTHENTICATION_GROUP",
                      "BATCH_COMPLETED_GROUP",
                      "BACKUP_RESTORE_GROUP",
                      "DATABASE_OPERATION_GROUP",
                      "USER_CHANGE_PASSWORD_GROUP")

$setAzureRmSqlDatabaseAuditingSplat = @{
    ResourceGroupName = "ResourceGroupName"
    ServerName = "ServerName"
    DatabaseName = "DatabaseName"
    StorageAccountName = "StorageAccountName"
    State = "Enabled"
    AuditActionGroup = $AuditActionGroup
}
Set-AzureRmSqlDatabaseAuditing @setAzureRmSqlDatabaseAuditingSplat

Next, it’s time to verify that the audit actually works and read some events.

How to access auditing logs?

Blob Auditing uses Append Blobs, which impose a few restrictions on storage accounts (no premium storage, no VNET endpoints), but the advantages seem to outweigh the limitations. For instance, events are produced in near real-time and can be consumed directly in Stream Analytics jobs as input sink.

One of the easiest ways to access auditing logs is by using sys.fn_get_audit_file. This TVF is not supported in SQL Data Warehouse and has to be called from the context of the master or any other SQL database under the logical server that hosts SQL Data Warehouse.

SELECT *
FROM sys.fn_get_audit_file('https://StorageAccountName.blob.core.windows.net/sqldbauditlogs/ServerName/SqlDataWarehouseName/', default, default)

Recent versions of Management Studio come with an option to extract and filter the auditing log directly from storage accounts (File -> Open -> Merge Audit Files):

Finally – Blob Auditing uses the .xel format, and log files can be copied across and opened locally in SSMS.