Re-creation of azurerm_mssql_database fails with 404

[lhaatveit]

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.13.4
+ provider registry.terraform.io/hashicorp/azurerm v2.33.0
+ provider registry.terraform.io/hashicorp/null v2.1.2
+ provider registry.terraform.io/hashicorp/random v2.3.1

Affected Resource(s)

• azurerm_mssql_server

Terraform Configuration Files

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "~> 2.33.0"
    }
    random = {
      source  = "hashicorp/random"
      version = "~> 2.3.0"
    }
  }
}

provider "azurerm" {
  features {}
}

locals {
  location       = "northeurope"
  location_short = "neu"
  environment    = "test"
  common_tags = {
    environment  = "test"
    "managed-by" = "Terraform"
    "created-by" = "Terraform"
  }
}

resource "azurerm_resource_group" "test" {
  name = "test-rg-${local.location_short}-${local.environment}"
  location = local.location
  tags = local.common_tags
}

resource "random_password" "mssql_server_admin" {
  length           = 32
  special          = true
  min_special      = 1
  min_numeric      = 1
  min_upper        = 2
  min_lower        = 2
  override_special = "!#%_-"
}

resource "azurerm_mssql_server" "test" {
  name                         = "test-mssqlserver-${local.location_short}-${local.environment}"
  resource_group_name          = azurerm_resource_group.test.name
  location                     = local.location
  version                      = "12.0"
  administrator_login          = "CatalystOneAdmin"
  administrator_login_password = random_password.mssql_server_admin.result
  tags                         = local.common_tags
}

resource "azurerm_mssql_elasticpool" "test" {
  name                = "test-elasticpool-${local.location_short}-${local.environment}"
  resource_group_name = azurerm_resource_group.test.name
  location            = local.location
  server_name         = azurerm_mssql_server.test.name
  license_type        = "LicenseIncluded"
  max_size_gb         = 10

  sku {
    name     = "GP_Gen5"
    tier     = "GeneralPurpose"
    family   = "Gen5"
    capacity = 2
  }

  per_database_settings {
    min_capacity = 0.25
    max_capacity = 1.0
  }

  tags = local.common_tags
}

resource "azurerm_mssql_database" "test" {
  name           = "test-mssqldb-${local.environment}"
  server_id      = azurerm_mssql_server.test.id
  collation      = "SQL_Latin1_General_CP1_CI_AS"
  max_size_gb    = 4
  sku_name       = "ElasticPool"
  elastic_pool_id = azurerm_mssql_elasticpool.test.id
  license_type   = "LicenseIncluded"
  tags           = local.common_tags

  provisioner "local-exec" {
    command = "echo ${azurerm_mssql_database.test.id}"
  }
}

Debug Output

https://gist.github.com/lhaatveit/3bac422cdd7e311d006281c5b1454714

Expected Behavior

The database should successfully be destroy and re-created.

Actual Behavior

The database is destroyed and recreated, but Terraform fails with the following error:

Error: retrieving MsSql Database "test-mssqldb-test" (MsSql Server Name "test-mssqlserver-neu-test" / Resource Group "test-rg-neu-test"): sql.DatabasesClient#Get: Failure responding to request: StatusCode=404 -- Original Error: autorest/azure: Service returned an error. Status=404 Code="ResourceNotFound" Message="The Resource 'Microsoft.Sql/servers/test-mssqlserver-neu-test/databases/test-mssqldb-test' under resource group 'test-rg-neu-test' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix"

Steps to Reproduce

1. terraform apply
2. terraform taint azurerm_mssql_database.test
3. terraform apply

Important Factoids

The same issue also appears intermittently when using a database with a dedicated SKU, but it appears consistently when the database belongs to an elastic pool.

[rubenaleman]

I'm having the same issue. The error shows up when the resource needs to be recreated due to a change that can't be done in-place. In my case, I'm seeing the same problem with PostgreSQL:

Error: retrieving PostgreSQL Server "*******" (Resource Group "*******"): postgresql.ServersClient#Get: Failure responding to request: StatusCode=404 -- Original Error: autorest/azure: Service returned an error. Status=404 Code
="ResourceNotFound" Message="The Resource 'Microsoft.DBforPostgreSQL/servers/*******' under resource group '*******' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix"

Although the error, the resource is still created, but all the resources depending on it aren't (like for example a private endpoint in my case). After import the resource and re-execution of terraform apply, it completes without problems.

[tberreis]

Any updates on this? We do have the same error with Terraform 1.7.4, azurerm 3.92.0.

[Aeropher]

I am seeing this issue 3.83.0 and 3.96.0 of the provider so it is far from a legacy issue.

We do not use taint to recreate as it is deprecated, instead we use the Terraform CLI with the -replace argument. The database does indeed get deleted but terraform seems to time out waiting for the resource to recreate and reports a 404 error within the next 2 minutes of waiting for the resource to be created. The database successfully creates after another 30 seconds of waiting.

So the command that we use works for all the other resources we have tested but not the database. It looks like this:
terraform apply -replace="module.myModule[\"myIndex\"].azurerm_mssql_database.mydb" --auto-approve

The most relevant part of the error message is this:
Database Name: "myDatabaseName"): unexpected status 404 with error: ResourceNotFound: The Resource 'Microsoft.Sql/servers/serverName/databases/myDatabaseName' under resource group 'ResourceGroupName' was not found. For more details please go to https://aka.ms/ARMResourceNotFoundFix

It is worth noting that the resource supports custom timeouts which are not honoured and the default timeouts are very high already.