How to use terraform to store connection string from SQL Managed Instance in Azure Key vault

Question

I am using terraform to deploy a SQL managed instance and need to store the 4 connection strings that come with it in azure key vault. According to terraform documentation for SQL managed instance: https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_managed_instance connection string is not an attribute that is exported, so can't just do an output and reference it. How would I identify these connection strings to store them in key vault?

If it is not possible with terraform I am open to powershell/arm solutions

Answer 1

In general, you can store any secret in a key vault using terraform like so:

resource "azurerm_key_vault_secret" "example" {
  name         = "the-secret-name"
  value        = "the-secret-value"
  key_vault_id = var.keyvault_id
}

Every connection string follows a certain syntax so I would put these together as strings based on the name, admin user and admin password, and add them to the key vault, similar to this:

locals {
    username = "admin"
    password = "abc"
}

resource "azurerm_mssql_managed_instance" "example" {
  name                = "managedsqlinstance"
  resource_group_name = var.resource_group_name
  location            = var.location
  
  administrator_login          = local.username
  administrator_login_password = local.password
  ...
}
resource "azurerm_key_vault_secret" "example" {
  name         = "sql-connectionstring"
  value        = "Server=tcp:${azurerm_mssql_managed_instance.example.name}.database.windows.net,1433;Persist Security Info=False;User ID=${local.username};Password=${local.password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  key_vault_id = var.keyvault_id
}

To find the connection strings and their exact syntax, you should have a look at the sql managed instance that has been created in the azure portal.