How can we improve Azure SQL Database?

← SQL Database

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.

105 votes
Vote
Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
You have left! (?) (thinking…)
Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Arijit Ghosh commented  ·   ·  Flag as inappropriate

    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 Stop

    Connect-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 Stop

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

    Write-Output "Enabling Advanced Data Security ...."
    Enable-AzSqlServerAdvancedDataSecurity `
    -ResourceGroupName <serverResourceGroup> `
    -ServerName <serverName> -DoNotConfigureVulnerabilityAssessment

    Write-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  ·   ·  Flag as inappropriate

    Yes, I agree this needs to be sorted ASAP. It also creates issues with bulk XML upload.

  • Chris Burger commented  ·   ·  Flag as inappropriate

    We have the same issue with Azure SQL audit logs and virtual machine boot diagnostic logs. A solution would be very useful.

SQL Database: Bugs

Categories

Feedback and Knowledge Base

(thinking…)
Hosted by UserVoice