Azure SQL Database Auditing – Move from Azure Table Storage to Blob Storage

[Yochanan_Rachamim]

First published on MSDN on Apr 13, 2017
Microsoft recently deprecated the use of Azure Table storage for SQL Database Auditing. Use Azure Blob Storage to save your auditing files instead.

If you were using Azure Table Storage for your auditing data, here is a simple step by step on how to move to Azure Blob Storage instead.

On this database, called “Sales”, I am inheriting the auditing settings from the server “auditingdemo”. Auditing is not individually configured for this database, as this is not a supported feature for Auditing with Table Storage (for more details about it, please read the session “ Blob/table differences in server auditing policy inheritance ”).

[caption id="attachment_146" align="alignnone" width="576"] Image 1 - Auditing with Azure Table Storage (Update 2017-10-30: note that this screed look different now on Azure Portal)[/caption]

To change it from Table to Blob Storage, all you need to do is the following:

1. Go to the SQL Database server, and modify it from Table to Blob. (Yes, as simple as that)


2. Click Save, and now you have Auditing type Blob at the server level:

[caption id="attachment_156" align="alignnone" width="575"] Image 2 - Auditing with Azure Blob Storage (Update 2017-10-30: note that this screed look different now on Azure Portal)[/caption]

That’s it. Now you have blob auditing enabled. If you need to have database level auditing, you may set it at the database level as well, but it is not a recommended setting, as per the feature documentation :

You should avoid enabling both server Blob auditing and database Blob auditing together, unless:

• You need to use a different storage account or retention period for a specific database.


• You want to audit different event types or categories for a specific database that are being audited for the rest of the databases on this server (e.g. if table inserts need to be audited only for a specific database).

You may ask: “What about the auditing data that was in the Table Storage? Will I lose it?

The answer is No. The old data continues in Table Storage, and the new auditing data goes to blob storage from this point onwards.



We created the following PowerShell script to help you quickly and easily identify all the resources (Azure SQL Servers + Databases) across all your subscriptions that currently have Table auditing enabled and require migration to Blob auditing.

Thanks to Tomer Weisberg and Gilad Mittelman for sharing

[code language="PowerShell"]Login-AzureRmAccount

$arrayTableFound = @()

foreach($sub in Get-AzureRmSubscription)
{
Write-Host 'Looking for table auditing in subscription'$sub.Name'('$sub.SubscriptionId')'

Set-AzureRmContext -SubscriptionName $sub.Name
$sqlServers = Get-AzureRmResourceGroup | Get-AzureRmSqlServer
foreach($sqlServer in $sqlServers)
{
$serverName = $sqlServer.ServerName
Write-Host 'Looking for table auditing on server'$serverName

$serverAuditingPolicy = $sqlServer | Get-AzureRmSqlServerAuditingPolicy -WarningAction SilentlyContinue

if ($serverAuditingPolicy.AuditState -eq 'Enabled' -and $serverAuditingPolicy.AuditType -eq 'Table')
{
Write-Host -ForegroundColor Red 'Found table auditing on server'$serverName
$arrayTableFound += ,@("SERVER: $serverName")
}
foreach($sqlDB in $sqlServer | Get-AzureRmSqlDatabase)
{

if ($sqlDB.DatabaseName -eq 'master')
{
#no support for auditing on master
continue
}
Write-Host 'Looking for table auditing on DB'$sqlDB.DatabaseName
$DBAuditingPolicy = Get-AzureRmSqlDatabaseAuditingPolicy -ServerName $sqlServer.ServerName -DatabaseName $sqlDB.DatabaseName -ResourceGroupName $sqlDB.ResourceGroupName -WarningAction SilentlyContinue

if ($DBAuditingPolicy.AuditState -eq 'Enabled' -and $DBAuditingPolicy.AuditType -eq 'Table')
{
Write-Host -ForegroundColor Red 'Found table auditing on database'$sqlDB.DatabaseName'Server'$sqlServer.ServerName
$databaseName = $sqlDB.DatabaseName
$arrayTableFound += ,@("DATABASE: $databaseName (on Server $serverName)")
}
}
}
}

Write-Host -ForegroundColor Green 'Total resources with Table auditing:'$arrayTableFound.Length

Write-Host -ForegroundColor Red 'The following resources have Table auditing enabled:'

foreach($item in $arrayTableFound)
{
Write-Host -ForegroundColor Red $item[0]
} [/code]