caxejc commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Community Note

Please vote on this issue by adding a :thumbsup: 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 and review the contribution guide to help.

Terraform Version

1.5.3

AzureRM Provider Version

3.61

Affected Resource(s)/Data Source(s)

azurerm_mssql_database

Terraform Configuration Files

data "azurerm_mssql_server" source_server{
  provider = azurerm.dev

  name = "server-name"
  resource_group_name = database-rg"
}
data "azurerm_mssql_database" "tf-test-db" {
  provider = azurerm.dev

  name = "tf-test-db"
  server_id = data.azurerm_mssql_server.source_server.id
}

module "primary_east_us" {
  source = "../mssql_server"

  name = primary-east-us"
  resource_group = module.demo_storage_rg.name #basic module that just creates an rg
  identity_type = "UserAssigned"
  identity_ids = [ module.eastus-mngidentity.id ]
  primary_user_assigned_identity_id = module.eastus-mngidentity.id
}

#Here's the mssql_server module:
resource "azurerm_mssql_server" "this" {
  name = var.name
  resource_group_name = var.resource_group
  location = var.location
  version = "12.0"
  minimum_tls_version = "1.2"

  identity {
    type = var.identity_type
    identity_ids = var.identity_type == "UserAssigned" ? var.identity_ids : []
  }

  primary_user_assigned_identity_id = var.identity_type == "UserAssigned" ? var.primary_user_assigned_identity_id : null
}

module "demo_primary" {
  source = "../mssql_database"

  name = "Demo"
  server_id = module.primary_east_us.id
  server_name = module.primary_east_us.name
  create_mode = "PointInTimeRestore"
  creation_source_database_id = data.azurerm_mssql_database.tf-test-db.id
  restore_point_in_time = timeadd(timestamp(),"-1h")
}

#mssql_database module:
resource "azurerm_mssql_database" "this" {
  name = var.name
  server_id = var.server_id
  sku_name = var.sku_name
  license_type = var.license_type
  max_size_gb = var.max_size_gb
  create_mode = var.create_mode
  creation_source_database_id = contains(["Copy","OnlineSecondary", "PointInTimeRestore", "Restore", "RestoreExternalBackup", "RestoreExternalBackupSecondary","Secondary"], var.create_mode) ? var.creation_source_database_id : null
  geo_backup_enabled = var.geo_backup_enabled
  min_capacity = var.min_capacity
  restore_point_in_time = var.create_mode == "PointInTimeRestore" ? var.restore_point_in_time : null
  recover_database_id = var.create_mode == "Recovery" ? var.recover_database_id : null
  restore_dropped_database_id = var.create_mode == "Restore" ? var.restore_dropped_database_id : null
  read_replica_count = var.read_replica_count
  read_scale = var.read_scale
  storage_account_type = var.storage_account_type
  transparent_data_encryption_enabled = var.transparent_data_encryption_enabled

  dynamic "long_term_retention_policy" {
    for_each = var.long_term_retention_policy ? [1] : []

    content {
      weekly_retention = var.weekly_retention != "" ? var.weekly_retention : null
      monthly_retention = var.monthly_retention != "" ? var.monthly_retention : null
      yearly_retention = var.yearly_retention != "" ? var.yearly_retention : null
      week_of_year = var.week_of_year != "" ? var.week_of_year : null
    }
  }

  dynamic "short_term_retention_policy" {
    for_each = var.short_term_retention_policy ? [1] : []

    content {
      retention_days = var.retention_days_short_term_retention_policy
      backup_interval_in_hours = var.backup_interval_in_hours
    }
  }

  dynamic "threat_detection_policy" {
    for_each = var.threat_detection_policy ? [1] : []

    content {
      state = var.state
      email_account_admins = var.email_account_admins
      email_addresses = var.email_addresses
      retention_days = var.retention_days_threat_detection_policy
      storage_account_access_key = var.storage_account_access_key
    }
  }
}

Debug Output/Panic Output

#Plan output:
 # module.storage.module.demo_primary.azurerm_mssql_database.this will be created
  + resource "azurerm_mssql_database" "this" {
      + auto_pause_delay_in_minutes         = (known after apply)
      + collation                           = (known after apply)
      + create_mode                         = "PointInTimeRestore"
      + creation_source_database_id         = "/subscriptions/{source-subscription}/resourceGroups/database-rg/providers/Microsoft.Sql/servers/server-name/databases/tf-test-db"
      + geo_backup_enabled                  = true
      + id                                  = (known after apply)
      + ledger_enabled                      = (known after apply)
      + license_type                        = "BasePrice"
      + maintenance_configuration_name      = (known after apply)
      + max_size_gb                         = 250
      + min_capacity                        = (known after apply)
      + name                                = "Demo"
      + read_replica_count                  = (known after apply)
      + read_scale                          = false
      + restore_point_in_time               = (known after apply)
      + sample_name                         = (known after apply)
      + server_id                           = "/subscriptions/{subscription}/resourceGroups/storage-rg/providers/Microsoft.Sql/servers/primary-east-us"
      + sku_name                            = "GP_Gen5_2"
      + storage_account_type                = "Geo"
      + transparent_data_encryption_enabled = true
      + zone_redundant                      = (known after apply)

      + long_term_retention_policy {
          + monthly_retention = "PT0S"
          + week_of_year      = 1
          + weekly_retention  = "PT0S"
          + yearly_retention  = "PT0S"
        }

      + short_term_retention_policy {
          + backup_interval_in_hours = 12
          + retention_days           = 7
        }

      + threat_detection_policy {
          + email_account_admins = "Disabled"
          + retention_days       = 0
          + state                = "Disabled"
        }
    }

Apply Output:
Error: creating/updating Database: (Name "Demo" / Server Name primary-east-us" / Resource Group storage-rg"): sql.DatabasesClient#CreateOrUpdate: Failure sending request: StatusCode=400 -- Original Error: Code="MismatchingSubscriptionWithUrl" Message="The provided subscription {source-subscription} did not match the subscription in the Url {subscription}."

Expected Behaviour

Creation of a new database in {subscription} as a point in time restore of another database from {source-subscription}.

Especially with the plan working as intended.

Actual Behaviour

MismatchingSubscriptionWithUrl error.

Steps to Reproduce

I have adjusted names from the actual code

We have multiple subscriptions each with their own service principle and associated workspace/repo in Terraform Cloud. The service principle for the relevant subscription was given reader at the subscription level for the source-subscription and "SqlDb Migration Role" for the source database (I had a previous access error when trying this process).

Create config
terraform init
terraform plan
terraform apply

Important Factoids

We have multiple subscriptions each with their own service principle and associated workspace/repo in Terraform Cloud. The service principle for the relevant subscription was given reader at the subscription level for the source-subscription and "SqlDb Migration Role" for the source database (I had a previous access error when trying this process).

References

No response

sinbai commented 1 year ago

Hi @caxejc, thank you for taking the time to raise this! Could you please try creating a point in time database to another subscription directly through Azure Portal or other tools (e.g. az cli)?

We use Issues in this repository to track feature enhancements and bugs in the Azure Provider. So that we can maintain focus on that, we instead ask that broader questions are raised using one of the Community Resources.

caxejc commented 1 year ago

So, is that a limitation of azurerm_mssql_database? It can't interact (copy/pitr/recover/etc) across subscriptions?

As you mentioned I can this in other ways, but I would prefer to do so through the TF resource if possible. I want to make sure this is a resource limitation and not a bad implementation.

Thanks

jassi007 commented 9 months ago

I get the same error when try to restore in another subscription.

######## create_mode = "Recovery" recover_database_id = "xxxxxx" ########################

Is this a bug or anything missing here ?

hashicorp / terraform-provider-azurerm

MismatchingSubscriptionWithUrl error when trying to create a point in time restore database across subscriptions using azurerm_mssql_database #23056