search

hashicorp / terraform-provider-azurerm

Terraform provider for Azure Resource Manager
https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs
Mozilla Public License 2.0
4.59k stars 4.63k forks source link

The server identity is not correctly configured on server 'sql-asgrndv2-main-rndv2-**-**'. Please re-configure Identity on the server." #13994

Open Uvindu96 opened 2 years ago

Uvindu96 commented 2 years ago

Terraform (and AzureRM Provider) Version

Expected behavior

We have configured the SQL server and databases using terraform. And recently I was trying to increase the db short term backup retention period using terraform. Once the retention period is set it should be able to update the db short term backup retention time.

Actual behavior

When I trying to apply the changes it gives me the following error.

Error: setting Blob Auditing Policies for Database: (Name "sqldb-asgrndv2-is-user-rndv2" / Server Name "sql-asgrndv2-main-rndv2-eastus2-***" / Resource Group "rg-****ndv2-main-***-eastus2-001"): sql.ExtendedDatabaseBlobAuditingPoliciesClient#CreateOrUpdate: Failure responding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="BlobAuditingNoServerIdentity" Message="The server identity is not correctly configured on server 'sql-a***v2-main-rndv2-eastus2-***'. Please re-configure Identity on the server."
  on .terraform/modules/is-mssql-serverless-user-db/modules/azurerm/MsSQL-Database/mssql_database.tf line 12, in resource "azurerm_mssql_database" "mssql_database":
  12: resource "azurerm_mssql_database" "mssql_database" {

Since I'm not doing any changes to db auditing policy this error is strange. I'm only making changes in azurerm_mssql_database resource. Is there any solution for this ?

Following are the terraform resources that we are using.

  1. azurerm_mysql_server 

    name                          = join("-", ["mysql", var.project, var.environment, var.location, var.padding])
location                      = var.location
resource_group_name           = var.resource_group_name
create_mode                   = "Default"
sku_name                      = var.sku_name
storage_mb                    = var.storage_mb
backup_retention_days         = var.backup_retention_days
geo_redundant_backup_enabled  = var.geo_redundant_backup_enabled
auto_grow_enabled             = var.auto_grow_enabled
public_network_access_enabled = false
administrator_login           = var.administrator_login
administrator_login_password  = var.administrator_login_password
version                       = var.db_version
ssl_enforcement_enabled       = var.ssl_enforcement_enabled

tags = var.default_tags
}

  2. azurerm_mssql_database

  name                        = join("-", ["sqldb", var.project, var.application_name, var.workload, var.environment])
  server_id                   = var.server_id
  elastic_pool_id             = var.elastic_pool_id
  collation                   = var.collation
  read_scale                  = var.read_scale
  sku_name                    = var.sku_name
  min_capacity                = var.min_capacity
  auto_pause_delay_in_minutes = var.auto_pause_delay_in_minutes
  zone_redundant              = var.zone_redundant
  short_term_retention_policy {
    retention_days = var.short_term_retention_policy_retention_days
  }
  tags = var.tags
  lifecycle {
    prevent_destroy = true
  }
}
  1. azurerm_mssql_server_extended_auditing_policy
resource "azurerm_mssql_server_extended_auditing_policy" "mssql_extended_auditing_policy" {
  server_id                               = var.mssql_server_id
  storage_endpoint                        = var.mssql_storage_account_primary_blob_endpoint
  storage_account_access_key              = var.mssql_storage_account_primary_access_key
  retention_in_days                       = var.retention_in_days
}
aristosvo commented 2 years ago

Hi @Uvindu96

This looks similar, meaning you can probably fix it by enabling the SystemAssigned Identity on mssql_server:

resource "azurerm_mssql_server" "serverx" {
...
  identity {
    type = "SystemAssigned"
  }
...
}

After that, you can probably also remove storage_account_access_key from the azurerm_mssql_server_extended_auditing_policy. This whole operation means you'd change from key based authorization to a Identity based authentication/authorization.

It's a bit weird this doesn't work the old way anymore though! Do you have firewall rules on the storage account maybe?

Uvindu96 commented 2 years ago

@mbfrahry I was able to enable the mange identity to sql server, but now I'm having a new issue and now it's giving me following error. Please note that I have already enabled the server level auditing logs.

Error: waiting for creation of MsSql Server "sql-asgrndv2-main-rndv2-eastus2-002" Extended Auditing Policy (Resource Group "rg-asgrndv2-main-rndv2-eastus2-002"): Code="BlobAuditingInsufficientStorageAccountPermissions" Message="Insufficient read or write permissions on storage account 'st**ecurity****prod'. Add permissions to the server Identity to the storage account."

Looks like something similer to this issue azurerm_mssql_server_extended_auditing_policy does not correctly set up access to storage account

@yupwei68 Do you have any idea on this issue ? And may I know what are the special permissions required to enable the auditing logs, after enabling the mange identity to the sql server ? I have already provided the Storage Blob Data Contributor permissions.

aristosvo commented 2 years ago

Hi @Uvindu96! Have you tried the solution for that mentioned issue, assigning a role on the storage account?

resource "azurerm_role_assignment" "write_permissions_server" {
  scope                = azurerm_storage_account.storage_accountx.id
  role_definition_name = "Storage Blob Data Contributor"
  principal_id         = azurerm_mssql_server.serverx.identity.0.principal_id
}

Assignment should be done before creating the mssql_extended_auditing_policy, like:

resource "azurerm_mssql_server_extended_auditing_policy" "mssql_extended_auditing_policy" {
  depends_on = [ azurerm_role_assignment.write_permissions_server ]
  ...
}
Uvindu96 commented 2 years ago

@aristosvo I'm using a azure AD group, and I'm adding the sql manage identity as a member to that AD group. I have gave the Storage Blob Data Contributor role permissions to that AD group. And assign it to the storage account, So in this way it should work right ?

This is my Terraform configuration. Please note that manage identity type is System Assigned.

module "mssql-server" {
  source                                 = "/home/uvindu/RndDBs/module2/"
  db_server_administrator_login          = var.mssql_db_administrator_login
  db_server_administrator_login_password = var.mssql_db_administrator_login_password
  default_tags                           = local.default_tags
  environment                            = var.environment
  location                               = var.location
  application_name                       = var.application_name_main
  padding                                = var.padding
  project                                = var.project
  resource_group_name                    = module.resource-group.resource_group_name
  mssql_identity_type                    = var.server_mssql_identity_type
  depends_on = [
    module.resource-group
  ]
}

# Add manage identity to storage account

module "add-group-member" {
source              = "/home/uvindu/RndDBs/module5/"
group_object_id     = var.ad_group_object_id
member_object_id    = module.mssql-server.azurerm_mssql_server_identity_object_id
depends_on = [
  module.mssql-server
]
}

module "sql-server-audit-logs" {
  source                                      = "/home/uvindu/RndDBs/module3/"
  mssql_server_id                             = module.mssql-server.azurerm_mssql_server_id
  mssql_storage_account_primary_blob_endpoint = var.log_archival_storage_account_primary_blob_endpoint
  retention_in_days                           = 365
  depends_on = [
    module.mssql-server,
    module.add-group-member
  ]
}
aristosvo commented 2 years ago

Hi @Uvindu96, it is hard to validate your setup from a distance. Based on the error message it is an authentication problem, an other possibility is that it might be a network issue (but the error message doesn't indicate that).

It would probably be easiest if you could validate the solution as suggested and work from there. That would be my approach at least, checking the network is the other option.