search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.29k stars 21.48k forks source link

SQL server cannot access storage account when firewall rule is enabled #32068

Closed sajithvasu closed 3 years ago

sajithvasu commented 5 years ago

SQL server cannot access storage account when firewall rule on storage is enabled and SQL IP's are added to allow access. Storage is used for Vulnerability Scan logs db audit logs. It works fine when we disable firewall. Error seen: "code": "BlobAuditingInsufficientStorageAccountPermissions", "message": "Insufficient read or write permissions on the provided storage account."

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

SumanthMarigowda-MSFT-zz commented 5 years ago

@sajithvasu Thanks for the question! We are investigating and will update you shortly.

SumanthMarigowda-MSFT-zz commented 5 years ago

@sajithvasu Apologies for the delay! Refer to the suggestion mentioned in this link https://docs.microsoft.com/en-us/rest/api/sql/database%20extended%20auditing%20settings/createorupdate If the issue still persist, I would recommended you to please contact us in Azure Community forum since it's not directly related to the documentation. We'll follow up there. We will now proceed to close this thread. If there are further questions regarding this matter, please tag me in your reply. We will gladly continue the discussion and we will reopen the issue.

manya0393 commented 4 years ago

i am getting issue while using the extended auditing policy using terraform in the private Storage account, i am getting below error-

Error: waiting for creation of SQL Server "eflexp-testn5-sqldb" Blob Auditing Policies(Resource Group "eflexp-eastus2-testn5-rg-01"): Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account 'eflexptestn5stdgb01'. "

on var-azsql.tf line 60, in resource "azurerm_mssql_server" "this": 60: resource "azurerm_mssql_server" "this" {

Note:- Even though we have provided ‘Storage blob reader contributor’ role to user according to the below link- https://docs.microsoft.com/en-us/azure/azure-sql/database/audit-write-storage-account-behind-vnet-firewall

eugeneromero commented 4 years ago

@manya0393 I think you might be seeing the same bug I reported here. I also listed a workaround in that bug report, hopefully this helps you, until the issue is properly fixed.

brandonmbanks commented 4 years ago

@manya0393 I am also experiencing the same error. I believe the solution for this is to use the new azurerm_mssql_server_extended_auditing_policy resource.

In this resource the storage account key is optional. The API requires not passing the storage account key to use the mssql server managed identity to access the storage account.

Something like the following:

resource "azurerm_mssql_server" "example" {
  ...
}

resource "azurerm_mssql_server_extended_auditing_policy" "example" {
  server_id         = azurerm_mssql_server.example.id
  storage_endpoint  = azurerm_storage_account.example.primary_blob_endpoint
  # do not pass the storage account key
  retention_in_days = 30
  depends_on        = [azurerm_role_assignment.audit_contributor]
}

resource "azurerm_role_assignment" "audit_contributor" {
  scope                = azurerm_storage_account.audit_example.id
  role_definition_name = "Storage Blob Data Contributor"
  principal_id         = azurerm_sql_server.mssql_server.identity[0].principal_id
}

Unfortunately, there is an Azure API bug that is preventing not passing these parameters on the mssql_server resource. https://github.com/terraform-providers/terraform-provider-azurerm/issues/8915 https://github.com/Azure/azure-rest-api-specs/issues/11271

andyong79 commented 3 years ago

I was also getting a similar error BlobAuditingInsufficientStorageAccountPermissions: Insufficient read or write permissions on storage account 'xxxx'. Add permissions to the server Identity to the storage account. I was already successfully adding the permissions to the storage account for the SQL server identity (configured to be Identity Type SystemAssigned on the SQLServer) and I could see it was being added when I looked in the storage account IAM. Messed around with the dependsOn to make sure the role assignment had completed before trying to configure auditSettings which was also fine. Then after reading through this (https://docs.microsoft.com/en-us/azure/azure-sql/database/audit-write-storage-account-behind-vnet-firewall), particularly the pre-requisites, I noticed I was missing Allow trusted Microsoft services to access this storage account. So I set bypass to AzureServices in networkAcls and now it seems to have gone through successfully.

Here are some arm template snippets that seem to be working for me.

  {
    "name": "[variables('storageAccountName')]",
    "type": "Microsoft.Storage/storageAccounts",
    "apiVersion": "2019-06-01",
    "location": "[parameters('location')]",
    "dependsOn": [
      "[resourceId('Microsoft.Network/virtualNetworks', variables('vnetName'))]"
    ],
    "properties": {
      "accessTier": "Cool",
      "minimumTlsVersion": "TLS1_2",
      "supportsHttpsTrafficOnly": true,
      "allowBlobPublicAccess": true,
      "networkAcls": {
          "bypass": "AzureServices",
          "defaultAction": "Deny",
          "virtualNetworkRules": [
            {
              "id": "[resourceId('Microsoft.Network/virtualNetworks/subnets', variables('vnetName'), variables('subnetName'))]",
              "action": "Allow"
            }
          ]
      }
    },
    "sku": {
      "name": "Standard_LRS"
    },
    "kind": "StorageV2",
    "resources": [
            {
                "type": "Microsoft.Storage/storageAccounts/providers/roleAssignments",
                "apiVersion": "2020-03-01-preview",
                "name": "[concat(variables('storageAccountName'), '/Microsoft.Authorization/', variables('uniqueRoleGuid'))]",
                "dependsOn": [
                    "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]",
                    "[resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName'))]"
                ],
                "properties": {
                    "roleDefinitionId": "[variables('storageBlobContributor')]",
                    "principalId": "[reference(resourceId('Microsoft.Sql/servers', variables('sqlServerName')), '2019-06-01-preview', 'Full').identity.principalId]",
                    "scope": "[resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName'))]",
                    "principalType": "ServicePrincipal"
                }
            }
        ]
  },

{ "name": "[variables('sqlserverName')]", "type": "Microsoft.Sql/servers", "location": "[parameters('location')]", "tags": { "displayName": "SqlServer", "environment": "[parameters('environmentName')]" }, "apiVersion": "2020-08-01-preview", "identity": { "type": "SystemAssigned" }, "properties": { "administratorLogin": "[parameters('sqlAdministratorLogin')]", "administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]", "version": "12.0" }, "resources": [ { "name": "[variables('databaseName')]", "type": "databases", "location": "[parameters('location')]", "tags": { "displayName": "Database" }, "sku": { "name": "S2", "tier": "Standard", "capacity": 50 }, "apiVersion": "2018-06-01-preview", "dependsOn": [ "[variables('sqlserverName')]" ], "properties": { "edition": "Standard", "collation": "SQL_Latin1_General_CP1_CI_AS", "maxSizeBytes": "1073741824", "requestedServiceObjectiveName": "Standard" } }, { "apiVersion": "2019-06-01-preview", "type": "auditingSettings", "name": "DefaultAuditingSettings", "dependsOn": [ "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]", "[resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName'))]", "[extensionResourceId(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName')), 'Microsoft.Authorization/roleAssignments', variables('uniqueRoleGuid'))]" ], "properties": { "State": "Enabled", "storageEndpoint": "[reference(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName')), '2019-06-01').PrimaryEndpoints.Blob]", "storageAccountSubscriptionId": "[subscription().subscriptionId]", "auditActionsAndGroups": [ "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP" ], "retentionDays": 90 } } ] },

SumanthMarigowda-MSFT-zz commented 3 years ago

@andyong79 Thank you for sharing the troubleshooting steps which helped you in the fixing the issue. This could benefit other members who is reading this thread!

ngocketit commented 3 years ago

@andyong79 Doesn't seem to work for me as I got the same error: Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account. The storage account network_rules:

  network_rules {
    default_action = "Deny"
    bypass             = ["AzureServices"]
  }

and needed permissions added for mssql server managed identity principal

ngocketit commented 3 years ago

@brandonmbanks Have you been able to make it work?

alcsoft commented 3 years ago

This should solve it. { "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#", "contentVersion": "1.0.0.0", "parameters": { "sqlServerName": { "type": "string", "defaultValue": "[concat('sql-', uniqueString(resourceGroup().id))]", "metadata": { "description": "Name of the SQL server" } }, "location": { "type": "string", "defaultValue": "[resourceGroup().location]", "metadata": { "description": "Location for all resources." } }, "sqlAdministratorLogin": { "type": "string", "metadata": { "description": "The administrator username of the SQL Server." } }, "sqlAdministratorLoginPassword": { "type": "securestring", "metadata": { "description": "The administrator password of the SQL Server." } }, "storageAccountName": { "type": "string", "defaultValue": "[concat('sqlaudit', uniqueString(resourceGroup().id))]", "metadata": { "description": "The name of the auditing storage account." } }, "isStorageBehindVnet": { "type": "bool", "defaultValue": false, "metadata": { "description": "Enable Auditing to storage behind Virtual Network or firewall rules. The user deploying the template must have an administrator or owner permissions." } }, "isMSDevOpsAuditEnabled": { "type": "bool", "defaultValue": false, "metadata": { "description": "Enable Auditing of Microsoft support operations (DevOps)" } } }, "variables": { "StorageBlobContributor": "[subscriptionResourceId('Microsoft.Authorization/roleDefinitions', 'ba92f5b4-2d11-453d-a403-e96b0029c9fe')]", "uniqueRoleGuid": "[guid(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), variables('storageBlobContributor'), resourceId('Microsoft.Sql/servers', parameters('sqlServerName')))]" }, "resources": [ { "type": "Microsoft.Storage/storageAccounts", "name": "[parameters('storageAccountName')]", "apiVersion": "2019-06-01", "location": "[parameters('location')]", "sku": { "name": "Standard_LRS" }, "kind": "StorageV2", "properties": { "networkAcls": { "bypass": "AzureServices", "defaultAction": "[if(parameters('isStorageBehindVnet'), 'Deny', 'Allow')]" } }, "resources": [ { "condition": "[parameters('isStorageBehindVnet')]", "type": "Microsoft.Storage/storageAccounts/providers/roleAssignments", "apiVersion": "2020-03-01-preview", "name": "[concat(parameters('storageAccountName'), '/Microsoft.Authorization/', variables('uniqueRoleGuid'))]", "dependsOn": [ "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]", "[resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName'))]" ], "properties": { "roleDefinitionId": "[variables('StorageBlobContributor')]", "principalId": "[reference(resourceId('Microsoft.Sql/servers', parameters('sqlServerName')), '2020-08-01-preview', 'Full').identity.principalId]", "scope": "[resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName'))]", "principalType": "ServicePrincipal" } } ] }, { "type": "Microsoft.Sql/servers", "apiVersion": "2020-08-01-preview", "location": "[parameters('location')]", "name": "[parameters('sqlServerName')]", "identity": "[if(parameters('isStorageBehindVnet'), json('{\"type\":\"SystemAssigned\"}'), json('null'))]", "properties": { "administratorLogin": "[parameters('sqlAdministratorLogin')]", "administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]", "version": "12.0" }, "tags": { "displayName": "[parameters('sqlServerName')]" }, "resources": [ { "type": "auditingSettings", "apiVersion": "2019-06-01-preview", "name": "DefaultAuditingSettings", "dependsOn": [ "[parameters('sqlServerName')]", "[parameters('storageAccountName')]", "[extensionResourceId(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), 'Microsoft.Authorization/roleAssignments/', variables('uniqueRoleGuid'))]" ], "properties": { "state": "Enabled", "storageEndpoint": "[reference(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), '2019-06-01').PrimaryEndpoints.Blob]", "storageAccountAccessKey": "[if(parameters('isStorageBehindVnet'), json('null'), listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), '2019-06-01').keys[0].value)]", "storageAccountSubscriptionId": "[subscription().subscriptionId]", "isStorageSecondaryKeyInUse": false } }, { "condition": "[parameters('isMSDevOpsAuditEnabled')]", "type": "devOpsAuditingSettings", "apiVersion": "2020-08-01-preview", "name": "Default", "dependsOn": [ "[parameters('sqlServerName')]", "[parameters('storageAccountName')]", "[variables('uniqueRoleGuid')]" ], "properties": { "state": "Enabled", "storageEndpoint": "[reference(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), '2019-06-01').PrimaryEndpoints.Blob]", "storageAccountAccessKey": "[if(parameters('isStorageBehindVnet'), json('null'), listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), '2019-06-01').keys[0].value)]", "storageAccountSubscriptionId": "[subscription().subscriptionId]", "isStorageSecondaryKeyInUse": false } } ] } ] }

chintavasisht commented 1 year ago

@sajithvasu Apologies for the delay! Refer to the suggestion mentioned in this link https://docs.microsoft.com/en-us/rest/api/sql/database%20extended%20auditing%20settings/createorupdate If the issue still persist, I would recommended you to please contact us in Azure Community forum since it's not directly related to the documentation. We'll follow up there. We will now proceed to close this thread. If there are further questions regarding this matter, please tag me in your reply. We will gladly continue the discussion and we will reopen the issue.

That link is broken :(