Creation of database resources fails if database is paused

[Freddy-scieneers]

If you create a Azure SQL Database in the "Serverless" compute tier, the database is able to automatically pause and resume. This will lead to the following behaviour.

Changes to anything within the database after the database is paused will fail because the db is not available:

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # mssql_schema.example will be created
  + resource "mssql_schema" "example" {
      + database_id = "8"
      + id          = (known after apply)
      + name        = "example"
      + owner_id    = (known after apply)
    }

  # mssql_sql_user.example will be created
  + resource "mssql_sql_user" "example" {
      + database_id = "8"
      + id          = (known after apply)
      + login_id    = "0x010600000000006400000000000000003D15DD8D6929D94DB9AC9C6DBC213200"
      + name        = "example"
    }

Plan: 2 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

mssql_sql_user.example: Creating...
mssql_sql_user.example: Still creating... [10s elapsed]
╷
│ Error: Failed to create user
│ 
│   with mssql_sql_user.example,
│   on contoso_dev_mssql.tf line 93, in resource "mssql_sql_user" "example":
│   93: resource "mssql_sql_user" "example" {
│ 
│ mssql: login error: Database 'testdb_1' on server 'terraform-mssql-test-local.database.windows.net' is not currently available.  Please retry the connection later.  If the problem
│ persists, contact customer support, and provide them the session tracing ID of '{XXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}'.

However, this will trigger the auto-resume of the database. According to the documentation this will take approx. one minute. If you then apply the same changes, the changes will be applied successfully:

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # mssql_schema.example will be created
  + resource "mssql_schema" "example" {
      + database_id = "8"
      + id          = (known after apply)
      + name        = "example"
      + owner_id    = (known after apply)
    }

  # mssql_sql_user.example will be created
  + resource "mssql_sql_user" "example" {
      + database_id = "8"
      + id          = (known after apply)
      + login_id    = "0x010600000000006400000000000000003D15DD8D6929D94DB9AC9C6DBC213200"
      + name        = "example"
    }

Plan: 2 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

mssql_sql_user.example: Creating...
mssql_sql_user.example: Creation complete after 4s [id=8/5]
mssql_schema.example: Creating...
mssql_schema.example: Creation complete after 3s [id=8/5]

Apply complete! Resources: 2 added, 0 changed, 0 destroyed.

Example code to test the issue (you'll need to wait for 60min after the first run to trigger the auto-pause, and then change anthing within the database resources):

terraform {
  required_providers {
    publicip = {
      source = "nxt-engineering/publicip"
    }

    mssql = {
      source = "PGSSoft/mssql"
    }
  }
}

provider "azurerm" {
  features {}
}

provider "mssql" {
  hostname   = azurerm_mssql_server.this.fully_qualified_domain_name
  azure_auth = {}
}

data "azurerm_client_config" "current" {}

data "publicip_address" "default" {
  source_ip = "0.0.0.0"
}

resource "azurerm_mssql_firewall_rule" "caller" {
  name             = "caller"
  server_id        = azurerm_mssql_server.this.id
  start_ip_address = data.publicip_address.default.ip
  end_ip_address   = data.publicip_address.default.ip
}


resource "azurerm_resource_group" "azure_test" {
  name     = "rg-test"
  location = "WestEurope"
}

resource "azurerm_mssql_server" "this" {
  name                = "terraform-mssql-test-local"
  resource_group_name = azurerm_resource_group.azure_test.name
  location            = azurerm_resource_group.azure_test.location
  version             = "12.0"


  azuread_administrator {
    login_username              = data.azurerm_client_config.current.object_id
    object_id                   = data.azurerm_client_config.current.object_id
    azuread_authentication_only = true
  }
}

variable "database_names" {
  default = ["testdb_1", "testdb_2", "testdb_3"]
}

resource "azurerm_mssql_database" "test_change_config" {
  for_each                    = toset(var.database_names)
  name                        = each.value
  server_id                   = azurerm_mssql_server.this.id
  auto_pause_delay_in_minutes = 60
  min_capacity                = 1
  sku_name                    = "GP_S_Gen5_1"
}

resource "mssql_sql_login" "example" {
  name                      = "example"
  password                  = "Str0ngPa$$word123"
  must_change_password      = true
  default_database_id       = data.mssql_database.test_db.id
  default_language          = "english"
  check_password_expiration = true
  check_password_policy     = true
}

resource "mssql_sql_user" "example" {
  name        = "example"
  database_id = data.mssql_database.test_db.id
  login_id    = mssql_sql_login.example.id
}


data "mssql_database" "test_db" {
  name = "testdb_1"
}

resource "mssql_schema" "example" {
  name        = "example"
  database_id = data.mssql_database.test_db.id
  owner_id    = mssql_sql_user.example.id
}