We are using Terraform to deploy infrastructure. We have Landing Zone in our environment. I have deployed Azure SQL DB PaaS and it is enabled for Private Endpoint. I would like to enable auditing on the SQL Server so that it can send the audit logs to a storage account which is enabled with private endpoint Both the Private Endpoints for the Storage Account and SQL are in the same subnets The code for TF for my SQL Server is provided below

resource "azurerm_mssql_server" "primary" {
  resource_group_name           = module.resourcegroup.resource_group.name
  location                      = module.resourcegroup.resource_group.location
  name                          = module.names-mssql-server.location.mssql_server.name_unique
  tags                          = module.resourcegroup.resource_group.tags
  administrator_login           = local.mssql_admin_username
  administrator_login_password  = random_password.mssql_primary.result
  public_network_access_enabled = false
  version                       = "12.0"
  azuread_administrator {
    login_username = data.azuread_group.sqladmin.name
    object_id      = data.azuread_group.sqladmin.id
  }
  identity {
    type = "SystemAssigned"
  }
  extended_auditing_policy {
    storage_endpoint                        = module.storageaccount.storage_account.self.primary_blob_endpoint
    storage_account_access_key              = module.storageaccount.storage_account.self.primary_access_key
    storage_account_access_key_is_secondary = false
    retention_in_days                       = 30
  }
}

The error that i keep getting while doing Terraform apply is :

│ Error: waiting for creation of Blob Auditing Policies Server: (Name "sql-primary-predev-cus-cb17" / Resource Group "rg-xxxx-predev-cus-409d"): Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account 'stqb2sal908f'. "
│ 
│   with azurerm_mssql_server.primary,
│   on resources.sql.tf line 39, in resource "azurerm_mssql_server" "primary":
│   39: resource "azurerm_mssql_server" "primary

" I have even given "Storage Account Contributor" role to the SQL Server to the Storage Account with the block below :

resource "azurerm_role_assignment" "regular-storage-account-sql-role" {
  scope                = module.storageaccount.storage_account.self.id
  role_definition_name = "Storage Account Contributor"
  principal_id         = azurerm_mssql_server.primary.identity.0.principal_id
}

Another thing i noticed is if i go to the portal to enable auditing via the portal, it is not showing me the Storage Account when i try to select the storage account. The private DNS zones for our private endpoint is in a Hub VNET and Resource Group as part of the Landing Zone architecture.

My SQL Server Network Settings is shown in the screenshot.

CodePudding user response：

Use the new azurerm_mssql_server_extended_auditing_policy resource to solve this problem.

Example:

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

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

The storage account key is optional in this resource. To utilize the mssql server controlled identity to access the storage account, the API needs not supplying the storage account key.

Auditing necessitates additional access to the storage account behind the firewall. However, in the scope of the storage account, the permission is "Storage Blob Data Contributor" rather than "Contributor." | Here is the Microsoft Document : Write audit to a storage account behind VNet and firewall | Docs

In the Original Post | Github you could see : Unfortunately, an Azure API issue prevents these arguments from being sent on the mssql_server resource.