Auditing in Azure SQL Data Warehouse

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.

 

Previous Last week reading (2018-06-11)
Next Last week reading (2018-06-17)

About author

Janusz Rokicki
Janusz Rokicki 1 posts

Azure solutions architect with a passion for secure, high-performance, maintainable data-driven applications that can be deployed with confidence on-premises and in the cloud.

View all posts by this author →

You might also like

Azure Data Factory 8 Comments

Azure Data Factory v2 and its available components in Data Flows

Many of you (including me) wonder about it. Namely: Is it possible to move my ETL process from SSIS to ADF? How can I reflect current SSIS Data Flow business

Last Week Reading 0 Comments

Last week reading (2018-12-16)

Hello data geeks. Last week we had many news about Cosmos DB, and this time the top topics are Power BI, Python, Azure Functions and ADF. We have new sections

Last Week Reading 0 Comments

Last week reading (2018-09-30)

Entire last week was dimmed by MsIgnite, hence take a look at videos from that conference: Microsoft Ignite 2018 Some sessions recorded and available over there. CosmosDB at Ignite 2018

1 Comment

Leave a Reply