[azurerm_mssql_server] Failure to create blob for auditing settings on a firewalled storage account

[mikemowgli]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Terraform (and AzureRM Provider) Version

$ terraform -v
Terraform v0.12.28
+ provider.azuread v0.11.0
+ provider.azurerm v2.18.0

Affected Resource(s)

• azurerm_mssql_server

Terraform Configuration Files

When enabling extended_auditing_policy towards a firewalled (but not with private endpoint) storage account, the SQL Server fails to create successfully saying there are "Insufficient read or write permissions on storage account". However, I made sure I fulfilled all these requirements.
I also noticed the SQL Server Service Principal is not automatically granted the "Storage Blob Data Contributor" role on the Storage Account like it happens when the setting is enabled from the Azure Portal. But even adding an azurerm_role_assignment does not help.
I could workaround the issue by disabling the firewall on the storage account.

resource "azurerm_storage_account" "tamers-storage" {
  name                = local.workspace["technical_storage_account_name"]
  resource_group_name = "example"

  location                 = "westeurope"
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_storage_account_network_rules" "tamers-storage-firewall" {
  resource_group_name        = "example"
  storage_account_name       = azurerm_storage_account.tamers-storage.name
  default_action             = "Deny"
  ip_rules                   = ["81.246.4.173"]
  bypass                     = ["Metrics", "Logging", "AzureServices"]
}

resource "azurerm_mssql_server" "tamers-synapse-server" {
  name = "datatamerssynapse20"
  resource_group_name           = "example"
  location                      = "westeurope"
  version                       = "12.0"
  administrator_login           = var.synapse_admin_user
  administrator_login_password  = var.synapse_admin_password

  identity {
    type = "SystemAssigned"
  }

  extended_auditing_policy {
    storage_endpoint                        = azurerm_storage_account.tamers-storage.primary_blob_endpoint
    storage_account_access_key              = azurerm_storage_account.tamers-storage.primary_access_key
    storage_account_access_key_is_secondary = false
    retention_in_days                       = 31
  }
}

Debug Output

https://gist.github.com/mikemowgli/b0ece0be08ba5d29509d031e7f458943

Expected Behavior

Auditing setting applied and SQL server created successfully.

Actual Behavior

Failure to enable extended auditing setting

Steps to Reproduce

1. terraform apply

References

https://docs.microsoft.com/en-us/azure/azure-sql/database/audit-write-storage-account-behind-vnet-firewall#prerequisites

[yupwei68]

Duplicated of #6906

[mybayern1974]

hi @mikemowgli

Thanks for opening this issue :)

Given seems this is a duplicate of #6906 - rather than having multiple issues open tracking the same thing I'm going to close this issue in favour of that one; would you mind subscribing to #6906 for updates? Or feel free to reopen if you believe these two issues are not talking about the same thing.

Thanks!

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 hashibot-feedback@hashicorp.com. Thanks!