Support for writing SQL Audit logs to storage account with firewall enabled

[sean-nixon]

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

Description

The ability to configure Azure SQL Server to send server audit logs to a storage account with virtual network rules (firewall) enabled was recently announced to be generally available (see references for announcement link). The organization I am working with would like to enable this using Terraform to improve our security posture around audit log storage.

The procedure to enable this is as follows:

• Enable "SystemAssigned" identity on the server
• Assign the server the role "Storage Blob Data Contributor" on the storage account
• Update the auditing settings WITHOUT specifying the storage account access key so that Azure SQL will access the account using its managed identity

Currently, this workflow is impossible to accomplish with Terraform for the following reasons:

• storage_account_access_key is REQUIRED within the azurerm_mssql_server resource. This should not be set in order to log to storage accounts with firewall enabled
• extended_auditing_policy is an inline block of the azurerm_mssql_server, requiring the auditing settings to be set at the same time as the server creation. Because the SystemAssigned identity won't be available until AFTER the server is created, it's impossible to satisfy the prerequisites of assigning the Blob Contributor role to the server identity without doing three terraform apply runs -- one to create the server, one to create the role assignment, and one to update the auditing settings on the server.

I think a new dedicated resource for server auditing settings should be created to work around this. The extended_auditing_policy could then be either deprecated or maintained as an optional (incompatible) alternative. The new dedicated resource would allow the auditing settings to be configured after the server and role assignment are created naturally using Terraform dependencies.

New or Affected Resource(s)

• azurerm_mssql_server (update)
• azurerm_mssql_server_auditing_settings (new)

Potential Terraform Configuration

resource "azurerm_mssql_server_auditing_settings" {
  storage_endpoint                        = "https://myaccount.blob.core.windows.net/"
  storage_account_access_key              = "xxx" # Optional (only required if not using SystemAssigned identity)
  storage_account_access_key_is_secondary = false # Optional (only required if not using SystemAssigned identity)
  retention_in_days                       = 30
}

References

Announcement: https://azure.microsoft.com/en-us/updates/azure-sql-auditing-to-storage-account-behind-virtual-network-and-firewall-now-generally-available/

Documentation: https://docs.microsoft.com/en-us/azure/sql-database/create-auditing-storage-account-vnet-firewall

Auditing settings API doc: https://docs.microsoft.com/en-us/rest/api/sql/server%20auditing%20settings/createorupdate

[yupwei68]

Hi @sean-nixon , thanks for opening this issue. There is another way to set sql server logs into a storage account with firewall or vn, which is to set the current service principal as the administrator of the sql server. Currently in release 2.10.0, I have merged a PR #6822 to support set AAD admin of azurerm_mssql_server. I hope the example below can help to solve your current problem.

resource "azurerm_mssql_server" "test" {
  name                         = "acctestsqlserver%[1]d"
  resource_group_name          = azurerm_resource_group.test.name
  location                     = azurerm_resource_group.test.location
  version                      = "12.0"
  administrator_login          = "missadministrator"
  administrator_login_password = "thisIsKat11"
  azuread_administrator {
    login_username = "AzureAD Admin"
    object_id      = data.azuread_service_principal.test.id
  }
  extended_auditing_policy {
    storage_account_access_key              = azurerm_storage_account.test.primary_access_key
    storage_endpoint                        = azurerm_storage_account.test.primary_blob_endpoint
    storage_account_access_key_is_secondary = true
    retention_in_days                       = 6
  }
}

[sean-nixon]

@yupwei68 Thanks for the response. I guess I missed your PR and previously closed issue since I only searched open issues. I was wondering if you could point me to the documentation that describes the approach of using Azure AD Administrator credentials for this? Everything I can find now specifies using the server's managed identity. I vaguely remember reading something similar a while back though. As I linked above, this capability to send audit logs to storage accounts behind a firewall only recently became generally available.

I'm wondering if it's possible that using the AAD administrator credentials was the pre-GA recommendation on how to do it prior to this latest announcement. I believe it's theoretically possible for us to just use the AAD admin approach, but we would like to do it the "official" way if possible.

[yupwei68]

hi @sean-nixon , sorry that I currently could not find the document for it. But I have written this case into acctest in the PR above(test with pass), on the other hand, I have an error message on portal suggest that we shall assign sql server administrator before we audit it to a storage account behind firewall.

[sean-nixon]

@yupwei68 When and in what region did you perform the test associated with the screenshot? That doesn't match up with my experience. Just now, I created a new SQL server in East US, did not configure an AAD administrator on it, and successfully applied auditing settings configured to log to a storage account behind a firewall all through the Portal. In the role assignments on the storage account, it shows the server has having blob data contributor access:

I think this is a more streamlined experience as it does not introduce the dependency of having AAD administrator configured.

[hashitop]

@yupwei68 thanks so much for your effort on this

I spotted a few things and would like to ask you some questions

• When looking into the reference document, the sql server will need to be assigned or registered to AD with system assigned identity, which part of the implementation is for that step?

• In your test case at the network rules, I believe the default action would rather be Deny with exceptions to a number of authorised services. IMO, this is what I think it should look like

  network_rules {
    default_action             = "Deny"
    ip_rules                   = ["127.0.0.1"]
    virtual_network_subnet_ids = [azurerm_subnet.example.id]
    bypass                     = ["Logging", "Metrics", "AzureServices"]
  }

• As per the same reference document under REST API section, it is stating explicitly that Configure Azure SQL server's blob auditing policy, without specifying a storageAccountAccessKey:, however, the storage_account_access_key attribute is currently defined as required field according to this document. Can you please check if there is any conflict between those two documents

[yupwei68]

Hi @hashitop ,@sean-nixon ,Thanks for your comments.
@hashitop you're right. I need to change default_action = "Deny" in acctest.
@sean-nixon , I found that my method to link sql auditing to a storage account behind firewall by setting the SQL admin has expired.
We currently plan to have a separate resource "azurerm_mssql_server_auditing" in the next major release to solve this problem, at the same time, we'll keep the block extended_auditing_policy in azurerm_mssql_server.

[sean-nixon]

That's great news! Thank you @yupwei68

[hashitop]

@yupwei68 thank you!

[yuri-tieto]

@yupwei68 is similar feature (external resource azurerm_sql_server_auditing) coming also to azurerm_sql_server resource*?

[WodansSon]

Removed milestone as PR #7793 is only a partial fix for this issue and it will be resolved fully in a follow up PR shortly...

[mleziva]

Using AzureRM version 2.33.0 the terraform is completing successfully but the auditing is not being enabled in the Azure portal:

My terraform resource

resource "azurerm_mssql_database_extended_auditing_policy" "this" {
  count                               = local.enable_extended_audit_policy ? 1 : 0
  database_id                         = azurerm_mssql_database.this[0].id
  storage_endpoint                    = data.azurerm_storage_account.audit_storage_account[0].primary_blob_endpoint
  retention_in_days                   = 30
  depends_on                          = [azurerm_role_assignment.audit_storage_account_blob_data_contributor]
}

My storage account has a firewall enabled and I am able to successfully setup SQL auditing with that storage account using the Azure Portal, but running the terraform does not make any changes to the SQL auditing

[peteneville]

This is working for me on 2.33 and an azurerm_role_assignment of Storage Blob Data Contributor on the storage account.

resource "azurerm_role_assignment" "data-contributor-role" {
  scope                       = data.azurerm_storage_account.platform.id
  role_definition_name        = "Storage Blob Data Contributor"
  principal_id                = azurerm_mssql_server.database_server_main[0].identity.0.principal_id
}

resource "azurerm_mssql_server_extended_auditing_policy" "database_server_main" {
  server_id                    = azurerm_mssql_server.database_server_main[0].id
  storage_endpoint             = data.azurerm_storage_account.platform.primary_blob_endpoint
  retention_in_days            = 0
}

[mleziva]

Looks like you can set auditing at both the server level and the DB level. I was using azurerm_mssql_database_extended_auditing_policy and checking the server which is why I was not seeing the changes. After checking the database I see that the auditing is working as expected.

I think this issue is resolved and can be closed

[jayanthrajanna]

@mleziva / @peteneville , Can you please share the full terraform code if it resolved your issues on this scenario.

[jayanthrajanna]

resource "azurerm_mssql_server" "Azure_sqlserver" {
name = "n0-server-sql"
resource_group_name = azurerm_resource_group.rg.name
location = azurerm_resource_group.rg.location
version = "12.0"
administrator_login = "missadministrator"
administrator_login_password = "thisIsKat11"
}

resource "azurerm_sql_database" "SQL_database01" {
name = "newdm0"
resource_group_name = azurerm_resource_group.rg.name
location = azurerm_resource_group.rg.location
server_name = azurerm_mssql_server.Azure_sqlserver.name
tags = var.rgtags
}

resource "azurerm_sql_active_directory_administrator" "sql_active_directory_administrator" {
server_name = azurerm_mssql_server.Azure_sqlserver.name
resource_group_name = azurerm_resource_group.rg.name
login = "sqlserverappreg"
tenant_id = "<tenant_id>"
object_id = "<Object_id>"
}

resource "azurerm_role_assignment" "data-contributor-role" {
scope = azurerm_storage_account.storage_account.id
role_definition_name = "Storage Blob Data Contributor"
principal_id = "<object iD"
}

resource "azurerm_mssql_server_extended_auditing_policy" "database_server_main" {
server_id = azurerm_mssql_server.Azure_sqlserver.id
storage_endpoint = azurerm_storage_account.storage_account.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.storage_account.primary_access_key
retention_in_days = 0
}

################getting below error##############
Error: authorization.RoleAssignmentsClient#Create: Failure responding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="PrincipalTypeNotSupported" Message="Principals of type Application cannot validly be used in role assignments."

on main.tf line 185, in resource "azurerm_role_assignment" "data-contributor-role":
185: resource "azurerm_role_assignment" "data-contributor-role" {

Error: waiting for creation of MsSql Server "n0-server-sql" Extended Auditing Policy (Resource Group "n01-dev-rg-t"): Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account 'nesdsda824'. "

on main.tf line 191, in resource "azurerm_mssql_server_extended_auditing_policy" "database_server_main":
191: resource "azurerm_mssql_server_extended_auditing_policy" "database_server_main" {

[mleziva]

@jayanthrajanna, looks like you are using an application instead of the managed identity of the sql server for the principal being assigned "Storage Blob Data Contributor" role. The resource snippet I used for the role is below:

resource "azurerm_role_assignment" "audit_storage_account_blob_data_contributor" {
  scope                 = data.azurerm_storage_account.audit_storage_account[0].id
  role_definition_name  = "Storage Blob Data Contributor"
  principal_id          = azurerm_mssql_server.this.identity[0].principal_id
} 

[jayanthrajanna]

Hello @mleziva , Thanks for your response.

I applied the changes as you stated above, I am now able to create a role definition with system assigned managed identity. But the change is not applying. The code runs but at the end it displays below error.

Error: waiting for creation of MsSql Server "" Extended Auditing Policy (Resource Group ""): Code="InternalServerError" Message="An unexpected error occured while processing the request. Tracking ID: ''"

I tried to execute multiple times but i am getting same error. Can you please help what can be the solution for this.

[yupwei68]

Hi @sean-nixon would you please close this issue because it has been solved?

[apurvchandra]

I am still hitting this and getting error with azure provider version 2.65.0 Terraform v0.13.5 , was it ever solved .

Error: waiting for creation of MsSql Server "xxxxxxxxx-db" Extended Auditing Policy (Resource Group "xxxxxx-rg"): Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account 'auditlogsstorage'. "

[sshah90]

Hi @yupwei68 I am getting Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account xxxx error.

here is my codebase

resource "azurerm_role_assignment" "audit_primary" {
  scope                = azurerm_storage_account.sqlserver_sa.id
  role_definition_name = "Storage Blob Data Contributor"
  principal_id         = azurerm_sql_server.sqlserver_primary.identity[0].principal_id
}

resource "azurerm_mssql_server_extended_auditing_policy" "primary-audit-enable" {
  depends_on = [
    azurerm_role_assignment.audit_primary
  ]
  server_id                               = azurerm_sql_server.sqlserver_primary.id
  storage_endpoint                        = azurerm_storage_account.sqlserver_sa.primary_blob_endpoint
  storage_account_access_key              = azurerm_storage_account.sqlserver_sa.primary_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 90
}

If I enable the audit policy using the below AZ CLI command, it works fine without any issue.

az sql server audit-policy update -g mygroup -n myserver --state Enabled --bsts Enabled --storage-account mystorage

any suggestions?

EDIT
I have bypassed "AzureServices","Logging","Metrics" while creating a Storage account.

[Lddeiva]

I assigned Owner access to fix the issue. Please give it a try.

resource "azurerm_role_assignment" "sqlserver01" {
  scope                = azurerm_storage_account.diagst.id
  role_definition_name = "Owner"
  principal_id         = azurerm_mssql_server.sqlserver01.identity.0.principal_id
}

resource "azurerm_mssql_server_extended_auditing_policy" "auditing" {
  server_id        = azurerm_mssql_server.sqlserver01.id
  storage_endpoint = azurerm_storage_account.diagst.primary_blob_endpoint

  retention_in_days = 180

  depends_on = [
    azurerm_private_dns_a_record.diagrecord,
    azurerm_role_assignment.sqlserver01
  ]
}

Then, I got to know about this Azure/azure-rest-api-specs#10258. Not sure if this is the latest decision as well.

[amaniommi39]

Any update on this issue?

[rcskosir]

Thanks for opening this issue. This was a problem in the 2.x version of the provider which is no longer actively maintained and should be solved by #8447.
If this is still an issue with the 3.x version of the provider please do let us know by opening a new issue, thanks!

[github-actions]

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 have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.