Error: unable to create user, "Connection was denied since Deny Public Network Access is set to Yes"

[flcdrg]

I am trying to create a SQL user on a new server/database that is using private endpoints and has public access disabled. There seems to be either a timing or DNS caching issue, as if I retry the deployment again then it will succeed.

Error: unable to create user [sqldb].[sqldbuser]: mssql: login error: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database).

I have tried adding in a time_sleep resource and also using a data resource for azurerm_mssql_server without success.

resource "azurerm_mssql_server" "sql_server" {
  name                          = "sql-server"
  location                      = data.azurerm_resource_group.group.location
  resource_group_name           = data.azurerm_resource_group.group.name
  version                       = "12.0"
  administrator_login           = "sqladmin"
  administrator_login_password  = "MyRandomP@assword!"
  minimum_tls_version           = "1.2"
  public_network_access_enabled = false

  identity {
    type = "SystemAssigned"
  }

  # Azure AD administrator
  azuread_administrator {
    login_username = var.sql_admin_username
    object_id      = var.sql_admin_object_id
  }
}

resource "azurerm_private_endpoint" "pe_sql_server" {
  name                = "pe-${azurerm_mssql_server.sql_server.name}"
  location            = azurerm_mssql_server.sql_server.location
  resource_group_name = azurerm_mssql_server.sql_server.resource_group_name
  subnet_id           = data.azurerm_subnet.privatelink.id

  # private connection details
  private_service_connection {
    name                           = "psc-${azurerm_mssql_server.sql_server.name}"
    private_connection_resource_id = azurerm_mssql_server.sql_server.id
    is_manual_connection           = false
    subresource_names              = ["sqlServer"]
  }
}

resource "azurerm_mssql_database" "sql_server_db" {
  name                 = "sqldb"
  server_id            = azurerm_mssql_server.sql_server.id
  collation            = "SQL_Latin1_General_CP1_CI_AS"
  sku_name             = var.mssql.sku
  zone_redundant       = false
  storage_account_type = var.mssql.storage_account_type
  min_capacity = var.mssql.min_capacity
  max_size_gb  = var.mssql.max_size_gb
  auto_pause_delay_in_minutes = var.mssql.auto_pause_delay
}

resource "time_sleep" "mssql_db_delay" {
  depends_on = [
    azurerm_private_endpoint.pe_sql_server,
    azurerm_mssql_database.sql_server_db
  ]
  create_duration = "30s"
}

data "azurerm_mssql_server" "sql_server" {
  name                = azurerm_mssql_server.sql_server.name
  resource_group_name = data.azurerm_resource_group.group.name
}

resource "mssql_user" "user" {
  server {
    host = data.azurerm_mssql_server.sql_server.fully_qualified_domain_name
    login {
      username = azurerm_mssql_server.sql_server.administrator_login
      password = azurerm_mssql_server.sql_server.administrator_login_password
    }
  }
  database = azurerm_mssql_database.sql_server_db.name
  username = "sqluser"
  password = "sqluserpassword"
  roles    = ["db_owner"]

  depends_on = [
    azurerm_private_endpoint.pe_sql_server,
    time_sleep.mssql_db_delay
  ]

  lifecycle {
    ignore_changes = all
  }
}