azurerm_sql_server creates a new resource each time if username is created and read in the same deployment

[iszekely]

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 Version

• Terraform v0.11.7
• provider.azurerm v1.13.0
• provider.random v2.0.0

Affected Resource(s)

• azurerm_sql_server
• azurerm_sql_database
• azurerm_sql_firewall_rule

Terraform Configuration Files

main.tf

provider "azurerm" {
  subscription_id = "${var.subscription_id}"
  version = "~> 1.0"
}

provider "random" {
  version = "~> 2.0"
}

terraform {
  backend "azurerm" {}
}

resource "random_string" "test_sql_password" {
  length = 15
  number = true
  min_numeric = 2
  upper = true
  min_upper = 2
  lower = true
  min_lower = 2
  special = false
}

resource "azurerm_key_vault_secret" "test_sql_username" {
  name = "test-sql-username"
  value = "${var.test_mssql_username}"
  vault_uri = "${var.key_vault_uri}"
}

resource "azurerm_key_vault_secret" "test_sql_password" {
  name = "test-sql-password"
  value = "${random_string.test_sql_password.result}"
  vault_uri = "${var.key_vault_uri}"
}

data "azurerm_key_vault_secret" "test_sql_username" {
  name = "test-sql-username"
  vault_uri = "${var.key_vault_uri}"
  depends_on = [ "azurerm_key_vault_secret.test_sql_username" ]
}

data "azurerm_key_vault_secret" "test_sql_password" {
  name = "test-sql-password"
  vault_uri = "${var.key_vault_uri}"
  depends_on = [ "azurerm_key_vault_secret.test_sql_password" ]
}

resource "azurerm_sql_server" "test_sql_server" {
  name = "${var.test_mssql_server_name}"
  resource_group_name = "${var.resource_group_name}"
  location = "${var.location}"
  version = "12.0"
  administrator_login = "${data.azurerm_key_vault_secret.test_sql_username.value}"
  administrator_login_password = "${data.azurerm_key_vault_secret.test_sql_password.value}"
}

resource "azurerm_sql_database" "test_sql_db" {
  name = "${var.test_mssql_database_name}"
  location = "${var.location}"
  resource_group_name = "${var.resource_group_name}"
  server_name = "${azurerm_sql_server.test_sql_server.name}"
  edition = "Standard"
}

resource "azurerm_sql_firewall_rule" "azure_services_fw_rule" {
  name = "azure_services"
  resource_group_name = "${var.resource_group_name}"
  server_name = "${azurerm_sql_server.test_sql_server.name}"
  start_ip_address = "0.0.0.0"
  end_ip_address = "0.0.0.0"
}

variables.tf

variable "subscription_id" {}
variable "location" {}
variable "resource_group_name" {}
variable "key_vault_uri" {}
variable "test_mssql_server_name" {}
variable "test_mssql_database_name" {}
variable "test_mssql_username" {}

env.tfvars

subscription_id = "<subscription_id>"
tenant_id = "<tenant_id>"
location = "northeurope"
resource_group_name = "<resource_group_name>"

key_vault_uri = "https://<vault>.vault.azure.net/"

test_mssql_server_name = "tftestsqlsrv"
test_mssql_database_name = "tftestsqldb"
test_mssql_username = "sqladmin"

Expected Behavior

The administrator username and password are created as key vault secrets, and then read as data sources. After consecutive executions Terraform should report that everything is up-to-date:

No changes. Infrastructure is up-to-date.

Actual Behavior

At first execution everything get created as expected. At second execution both the id and the administrator_login (although it is constant) trigger a new resource creation. The administrator_login_password also shows an attribute change, but that could be done in-place.

-/+ azurerm_sql_server.test_sql_server (new resource required)
      id:                             "/subscriptions/***/resourceGroups/***/providers/Microsoft.Sql/servers/tftestsqlsrv" => <computed> (forces new resource)
      administrator_login:            "sqladmin" => "${data.azurerm_key_vault_secret.test_sql_username.value}" (forces new resource)
      administrator_login_password:   <sensitive> => <sensitive> (attribute changed)
      fully_qualified_domain_name:    "tftestsqlsrv.database.windows.net" => <computed>
      location:                       "northeurope" => "northeurope"
      name:                           "tftestsqlsrv" => "tftestsqlsrv"
      resource_group_name:            "***" => "***"
      tags.%:                         "0" => <computed>
      version:                        "12.0" => "12.0"

The database and the firewall rules are also destroyed with the server, but does not get recreated. After the third execution the azurerm_sql_server gets created again, but this time the database and the firewall rules also show up.

So there are two problems:

1. dependency handling in azurerm_key_vault_secret resource and data source, and the azurerm_sql_server resource
2. dependency handling between azurerm_sql_server, azurerm_sql_database, and azurerm_sql_firewall_rule

If I create the two secrets in the key vault by hand and remove the two azurerm_key_vault_secret resources, everything just works fine. Even changes in them are noticed and appropriate changes performed.

Steps to Reproduce

1. Fill in the missing parts in env.tfvars
2. terraform apply Everything gets created as expected.
3. terraform apply The server is destroyed and a new server gets created.
4. terraform apply The server is destroyed and a new server, database, and firewall rules get created.

References

• Improper dependency handling between azurerm_sql_server and azurerm_sql_database #1869

[tombuildsstuff]

hi @iszekely

Thanks for opening this issue :)

Taking a look into this the ID for the Key Vault Secret contains the version at the end which is used for tracking. I believe in the instance above the Version is changing for the secret and thus the ID is changing, which is changing the value of the Key Vault Secret.

Would you be able to confirm if there's something changing the value of the Key Vault Secret out of band?

Thanks!

[iszekely]

Hi @tombuildsstuff

Obviously our first guess was the same: a new version of the key vault secret is generated due to the usage of random_string. I double-checked it again, and nothing changes. The random_string is stored in the TF state, so NO new version is generated.

If you create a key vault, fill in the env.tfvars, and run the steps to reproduce, you can see it happening very easily.

Thanks you!

[WodansSon]

@iszekely

This isn't an issue with the provider, this has to do with terraform core runtime and the way plan itself works. Since during plan it doesn't call any API's or ask Azure about property values it is basically, comparing the state file with the HCL file. Let's use the below HCL file for example and see what is going on:

resource "azurerm_key_vault_secret" "test_sql_username" {
  name = "test-sql-username"
  value = "sqladmin"
  vault_uri = "https://<vault>.vault.azure.net/"
}

data "azurerm_key_vault_secret" "test_sql_username" {
  name = "test-sql-username"
  vault_uri = "https://<vault>.vault.azure.net/"
  depends_on = [ "azurerm_key_vault_secret.test_sql_username" ]
}

resource "azurerm_sql_server" "test_sql_server" {
  name = "tftestsqlsrv"
  resource_group_name = "${azurerm_resource_group.test.name}"
  location = "${azurerm_resource_group.test.location}"
  version = "12.0"
  administrator_login = "${data.azurerm_key_vault_secret.test_sql_username.value}"
  administrator_login_password = "${data.azurerm_key_vault_secret.test_sql_password.value}"
}

Assuming we already ran the above HCL file once and it successfully created the resources as expected. We then run terraform plan again and receive the below output from terraform:

-/+ azurerm_sql_server.test_sql_server (new resource required)
      id:                           "/subscriptions/XXXX/resourceGroups/test-sql-password/providers/Microsoft.Sql/servers/tftestsqlsrv" => <computed> (forces new resource)
      administrator_login:          "sqladmin" => "${data.azurerm_key_vault_secret.test_sql_username.value}" (forces new resource)
      administrator_login_password: <sensitive> => <sensitive> (attribute changed)
      fully_qualified_domain_name:  "tftestsqlsrv.database.windows.net" => <computed>
      location:                     "westeurope" => "westeurope"
      name:                         "tftestsqlsrv" => "tftestsqlsrv"
      resource_group_name:          "test-sql-password" => "test-sql-password"
      tags.%:                       "0" => <computed>
      version:                      "12.0" => "12.0"

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

This is where the bug is in the terraform core runtime, what should happen here is it should expand the ${data.azurerm_key_vault_secret.test_sql_username.value} and substitute it with the value from the state file(below) (e.g. sqladmin), just like ${azurerm_resource_group.test.name} is expanded to test-sql-password, however this is not what is happening with data sources and that is why we are getting the unexpected output from terraform plan. BTW- the reason id is showing that it has changed is not because it has actually changed, it's because a child attribute has changed which will invalidates this id because the administrator_login is marked as ForceNew.

"data.azurerm_key_vault_secret.test_sql_username": {
                    "type": "azurerm_key_vault_secret",
                    "depends_on": [
                        "azurerm_key_vault_secret.test_sql_username"
                    ],
                    "primary": {
                        "id": "https://<vault>.vault.azure.net/secrets/test-sql-username/ccbe6e8eaca047089faa97f4f309a32b",
                        "attributes": {
                            "content_type": "",
                            "id": "https://<vault>.vault.azure.net/secrets/test-sql-username/ccbe6e8eaca047089faa97f4f309a32b",
                            "name": "test-sql-username",
                            "tags.%": "0",
                            "value": "sqladmin",
                            "vault_uri": "https://<vault>.vault.azure.net/",
                            "version": "ccbe6e8eaca047089faa97f4f309a32b"
                        },
                        "meta": {},
                        "tainted": false
                    },
                    "deposed": [],
                    "provider": "provider.azurerm"
                },

I am closing this issue as it is an upstream-terraform issue and will open a new issue in the terraform core runtime repository for this issue, and reference this issue in it.

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!