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


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”).

auditing-table

Image 1 - Auditing with Azure Table Storage

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:

auditing-blob

Image 2 - Auditing with Azure Blob Storage

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

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]
} 
Comments (8)

  1. Chris Gray says:

    Is there a date set yet for when table auditing option will be removed from Azure?

    1. Subbu K says:

      Table Auditing option will be removed end of Sept 2017. And threat detection will stop supporting table auditing by end of this month (May-2017).

  2. Rody says:

    With Table storage now deprecated, how can we use the PowerBI “SQL Database Auditing” service? This service is only communicating with table storage and we had created several Dashboards for auditing purposes.

    1. Hi Rody,

      You can use the fn_get_audit_file (T-SQL) system function to pull data from the Blob audit logs for consumption in Power BI reports:
      https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql

      Thanks,
      Gilad (MSFT)

  3. Kevin Bowman says:

    Please can you describe how this can be applied through an ARM template? We have a sample from Olga (https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/11/arm-template-to-deploy-server-with-auditing-and-threat-detection-turned-on/) and this appears to work, however the Portal still thinks that Table logging is being used and is now complaining, yet logs are successfully being created in BLOBs

    1. Hi Kevin, what do you mean by “the portal still thinks … “? did you confirm that blob auditing is set for the server and the database level (either explicit or by inheritance)?

      1. aleksbor says:

        Hello Yochanan, JSON template enable Table auditing by default. After template deployment, you go to Auditing & Threat Detection in Azure portal to discover a message asking to switch from Table to Blob auditing. What is the proper way to deploy Blob auditing at a server level?
        This enables Table auditing currently:
        “type”:”auditingPolicies”,
        “name”:”DefaultAuditPolicy”,

        “auditingState”:”Enabled”,
        “eventTypesToAudit”:”All”

        1. Hi,
          Please take a look at this page
          https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/11/arm-template-to-deploy-server-with-auditing-and-threat-detection-turned-on/
          It should put you in the right direction, the difference is table uses “auditingPolicies” and blob uses “auditingSettings”

          Regards
          Clive Challinor [MSFT]

Skip to main content