azurerm_mssql_database_extended_auditing_policy to Log Analytics doesn't display correctly at Server level

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 v0.12.20 was used in this test AzureRM Provider version 2.56.0

Affected Resource(s)

azurerm_mssql_database_extended_auditing_policy

Terraform Configuration Files

From the documentation here: https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_database_extended_auditing_policy

The example linked in the documentation here: https://github.com/terraform-providers/terraform-provider-azurerm/tree/master/examples/sql-azure/sql_auditing_log_analytics

Debug Output

Panic Output

Expected Behaviour

Auditing to Log Analytics is applied at server and database level, and is reflected as set correctly in the Azure Portal at server and database level as set to "on"

Actual Behaviour

Overview pages display "green/active" at server level, but in the Auditing section in the Azure portal it doesn't fill in the required details, causing confusion (At database level it displays correctly).

For the database in the "Auditing" section in the Azure Portal: Working as expected

For the server overview page in the Azure Portal: Working as expected

For the server in the "Auditing" section in the Azure Portal: Note that the Auditing appears to be switched OFF here...

Steps to Reproduce

Run the example code at this link: https://github.com/terraform-providers/terraform-provider-azurerm/tree/master/examples/sql-azure/sql_auditing_log_analytics

Important Factoids

References

0000

The example code worked for me (Terraform v0.14.10, Azure RM Provider v2.57.0). However, I did encounter two problems:

a dependency issue when including Azure Defender Vulnerability Assessments (azurerm_mssql_server_security_alert_policy + azurerm_mssql_server_vulnerability_assessment) - these had to be called first otherwise the storage account would be selected on the Server Auditing blade.
I could not get enabled option "Enable Auditing of Microsoft support operations" (even with the "DevOpsOperationsAudit" log defined in azurerm_monitor_diagnostic_setting and azurerm_mssql_database_extended_auditing_policy / azurerm_mssql_server_extended_auditing_policy having log_monitoring_enabled set to enabled). From what I can see of the documentation this should be enough to turn it on via the REST API.

I fixed "1" with normal depends_on & fixed "2" by adding an ARM template resource as shown below

resource azurerm_template_deployment sql_sqlauditing {
  depends_on          = [ azurerm_mssql_server_extended_auditing_policy.sql ]
  name                = "terraform-sql-server-auditing"
  resource_group_name = azurerm_mssql_server.sql.resource_group_name

  template_body = <<DEPLOY
{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "ServerName": {
            "type": "string"
        }
    },
    "variables": {
    },
    "resources": [
        {
            "type": "Microsoft.Sql/servers/auditingSettings",
            "apiVersion": "2020-11-01-preview",
            "name": "[concat(parameters('ServerName'), '/', 'DefaultAuditingSettings')]",
            "properties": {
                "state": "Enabled",
                "isDevopsAuditEnabled": true,
                "isAzureMonitorTargetEnabled": true
            }
        }
     ],
    "outputs": {
    }
}
DEPLOY
  parameters = {
    "ServerName" = azurerm_mssql_server.sql.name
  }
  deployment_mode = "Incremental"
}

All of my testing was green-field with just SQL Server and master database existing.

I've just tried the example again from here using AzureRM provider 3.27.0

https://github.com/terraform-providers/terraform-provider-azurerm/tree/master/examples/sql-azure/sql_auditing_log_analytics (took two runs/passes - needs a wait in it so that it can find the databases - otherwise you get resources not found)

....and it seems a lot clearer now - could have been an Azure Portal fix rather than Terraform?

At Server Level

At database level

hashicorp / terraform-provider-azurerm