Auditing in Azure Sql Data Warehouse

First article in a series on “Security Intelligence in Azure PaaS” inspired me to write about auditing in Azure Sql Data Warehouse. Auditing to put it simply for person new to GRC (which stands for Governance, Risk Management, Compliance) is just like logging, but in expensive suite and a tie. Business tends to understand its purpose, requirements and is aware of attached price tag. Azure Sql Data Warehouse is relatively new MPP (after Massively Parallel Processing architecture) database offering based on Parallel Data Warehouse with generation 2 (optimised compute) generally available. Some regions and performance levels still need submitting support requests. On the surface it’s kind of Sql Server, but behind the scene there’s a different beast altogether. That fact might explain inconsistent experience in Azure Portal comparing to more mature Azure Sql Database.

ENABLE AUDIT

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

In North Europe portal allows to enable Table Auditing (Storage Account based table) on Sql Data Warehouse scope and it is not possible to enable Blob Auditing:

On top of that Blob Auditing behaves differently in different regions when enabled on logical server level. 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 turn on programaticly by calling ARM Rest API. Powershell script to enable Blob Auditing:

<# 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

https://gist.github.com/DataSic/884c1231fd853648600d907090184b8c

Overall Blob Auditing seems to be the best option - it’s a common denominator available across all regions and Sql PaaS resources that is based on Sql Audit feature with access to specification that can be adjusted to business requirements. Details including audit actions and groups can be found on reference page - link above. Fact that Table Auditing has been already deprecated in Sql Databases might also help with decision.

MODIFY AUDIT SPECIFICATION

Back in February I wrote a post that describes how to extract time of last successfully geo-backup in Sql Data Warehouse (link here https://www.datasic.com/post/azuredw-getlastgeobackup/). Key takeway was to “trust, but verify” which I usually recommend in context of security in Azure. And with a little help of audit it’s possible to verify quite a lot of managed operations like backups and DBCC checks - at least in theory. By default audit specification consist of 3 groups:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP FAILED_DATABASE_AUTHENTICATION_GROUP BATCH_COMPLETED_GROUP

Query to get actions for audited groups:

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

https://gist.github.com/DataSic/dd2bdaa2577a169b7858b7e3e9d0c4ec

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

Next I decided to add AUDIT_CHANGE_GROUP (listed as one of enumarator names) because it’s good practice to monitor any changes to audit and other security controls. They are one of first targets I would expect to see when attacker starts moving latteraly, 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 command lets 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

https://gist.github.com/DataSic/a0d547fae28b4acfacb7d387c92fbf49

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

ACCESS AUDITING LOG

Blob Auditing uses append blobs that imposes a few restrictions on storage accounts (no premium storage, no VNET endpoints), but advantages seem to be greater. For instance events are emited in near real-time and can be consumed directly in Stream Analytics jobs as input sink.

One of the easies methods to access auditing logs is by using sys.fn_get_audit_file. That TVF is not supported in Sql Data Warehouse and has to be called from context of master or any other Sql Database under 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)

https://gist.github.com/DataSic/dfe507a85193a5021f7b3fa053550adb

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

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