Allow storage account with vnet and behind firewall to be used for SQL's advanced threat protection.
As described in Microsoft documentation https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing#subheading-1 a storage account behind firewall and a storage account with vnet is currently not supported for SQL's advanced threat protection.
This possess a high security risk where the logs will visible to anyone with access to the subscription, sas uri or storage account key.
We expect this to be fixed and enhanced so that a storage account with vnet and behind firewall can be used for SQL's advanced threat protection.

4 comments
-
Anonymous commented
For me this solution is not working. Microsoft please fix asap!
-
Arijit Ghosh commented
Make sure you have enabled:
1. Storage Account-> Firewalls and Virtual networks-> Allow access from -> Selected networks -> Add existing virtual network => Add the Vnets and subnets
2. Storage Account-> Firewalls and Virtual networks-> Under Firewall => Add your client IP address ('1.2.3.4')
3. Storage Account-> Firewalls and Virtual networks-> Under Exception => select "Allow trusted Microsoft services to access this storage account"
4. SQL Server -> Firewalls and virtual networks -> Under Add existing virtual network => Add the same Vnets and subnets that you have added in step 1
5. Go the Vnet -> Subnets -> scroll down to Service endpoints -> Add "Microsoft.Sql".(The 5th step can be automated)
Steps to resolve the issue:
Now,1. Create a Automation runbook and run the below:
Purpose: To assign Onwer role to the Service Principal
-------------------------------------------------------#Connecting to Azure
$connectionName = "AzureRunAsConnection"
$RunAsConnection = Get-AutomationConnection -Name $connectionName -ErrorAction StopConnect-AzAccount `
-ServicePrincipal `
-Tenant $RunAsConnection.TenantId `
-ApplicationId $RunAsConnection.ApplicationId `
-CertificateThumbprint $RunAsConnection.CertificateThumbprint | Write-Verbose
$Subs = Get-AzSubscription -SubscriptionId $RunAsConnection.SubscriptionId
$select_subs = Select-AzSubscription -SubscriptionName $Subs.Name#Assign Owner role to the Automation account ServicePrincipal
$ServicePrincipal =Get-AzADServicePrincipal -DisplayName "<ServicePrincipal_Name>"
Write-output $ServicePrincipal
New-AzRoleAssignment -ObjectId $ServicePrincipal.Id -RoleDefinitionName "Owner" -Scope "/subscriptions/<SubscriptionId>/resourceGroups/<Automation Account ResourceGroupName>/providers/Microsoft.Automation/automationAccounts/AutomationAccount"2. Create a 2nd Automation runbook and run the below:
Purpose: Assign Identity to the Sql Server and the Assign "Storage Blob Data Contributor" role to the SQL server under the Storage account
-------------------------------------------------------------------------------------------------------------------------------------------#Connecting to Azure
$connectionName = "AzureRunAsConnection"
$RunAsConnection = Get-AutomationConnection -Name $connectionName -ErrorAction StopConnect-AzAccount `
-ServicePrincipal `
-Tenant $RunAsConnection.TenantId `
-ApplicationId $RunAsConnection.ApplicationId `
-CertificateThumbprint $RunAsConnection.CertificateThumbprint | Write-Verbose
$Subs = Get-AzSubscription -SubscriptionId $RunAsConnection.SubscriptionId
$select_subs = Select-AzSubscription -SubscriptionName $Subs.NameWrite-Output "Enabling Advanced Data Security ...."
Enable-AzSqlServerAdvancedDataSecurity `
-ResourceGroupName <serverResourceGroup> `
-ServerName <serverName> -DoNotConfigureVulnerabilityAssessmentWrite-Output "Setting up Storage Blob Data Contributor role for the SQL Server ...."
$storageid = (Get-AzStorageAccount -ResourceGroupName <storageaccountResourceGroup> -StorageAccountName <storageAccountName>).Id
Set-AzSqlServer -ResourceGroupName <serverResourceGroup> -ServerName <serverName> -AssignIdentity
Start-Sleep -Seconds 60
$app = Get-AzADServicePrincipal -DisplayName <serverName>
New-AzRoleAssignment -ObjectId $app.Id -RoleDefinitionName "Storage Blob Data Contributor" -Scope $storageid
Write-Output "RBAC completed successfully!!"
Start-Sleep -Seconds 300##Please change the sleep value as desired. I did not find any specification for the sleep time. But it worked for me.
-
Anonymous commented
Yes, I agree this needs to be sorted ASAP. It also creates issues with bulk XML upload.
-
Chris Burger commented
We have the same issue with Azure SQL audit logs and virtual machine boot diagnostic logs. A solution would be very useful.