Adding sql server logins and user in a loop via Terraform in azure

Question

I am trying to create mssql login and user for each database i have via a for loop .

I am getting error while running tf code :

mssql: login error: Login failed for user

Please help : below is the code snippet

Provider:

 mssql = {
          source = "betr-io/mssql"
          version = "0.3.1"
        }

tf code :

resource "azurerm_mssql_server" "cdapi" {
  count = var.deploy_mssql ? 1 : 0

  name                         = local.db_server_name
  resource_group_name          = "rg-${var.environment}-ccapi"
  location                     = var.location
  administrator_login          = var.mssql.admin.username
  administrator_login_password = data.azurerm_key_vault_secret.mssql_admin_password[0].value

  azuread_administrator {
    login_username = var.mssql.aad_admin.username
    object_id      = var.mssql.aad_admin.object_id
  }

  minimum_tls_version = "1.2"

  public_network_access_enabled = false
  version                       = "12.0"

 
}

resource "azurerm_mssql_database" "cdapi_mssql" {
  for_each = var.deploy_mssql ? var.mssql.databases : {}

  name                        = each.key
  server_id                   = azurerm_mssql_server.ccapi[0].id
  collation                   = each.value.collation
  elastic_pool_id             = id
  sku_name                    = "ElasticPool"
  //sku_name                    = each.value.sku
  //max_size_gb                 = each.value.max_size_gb
  //min_capacity                = each.value.min_capacity
  auto_pause_delay_in_minutes = each.value.auto_pause_delay_in_minutes
  
 
}

resource "mssql_login" "user_login" {
  for_each = var.deploy_mssql ? var.mssql.databases : {}
  server {
    host = azurerm_mssql_server.cdapi[0].fully_qualified_domain_name
    login {
      username = azurerm_mssql_server.cdapi[0].administrator_login
      password = azurerm_mssql_server.cdapi[0].administrator_login_password
    }
  }
  login_name = "user-${each.key}"
  password  = "wravDjXsVL4UrHsv"
}
resource "mssql_user" "database_users" {
for_each = var.deploy_mssql ? var.mssql.databases : {}
  server {
    host = azurerm_mssql_server.cdapi[0].fully_qualified_domain_name
    login{
      username = "user-${each.key}"
      password = "wravDjXsVL4UrHsv"
    }
  }
  database  = each.key
  username  = "${each.key}user"
  roles     = ["db_datareader", "db_datawriter"]
  depends_on = [mssql_login.user_login]

Answer 1

Adding sql server logins and user in a loop via Terraform in azure

It seems the issue here was with the authentication provision because of incorrect credentials or users and also check your IP has access over server by creating a firewall.

I came across some github issue where I didnt get any relevent info related to blocker

check the configuration of mssql_login and mssql_user. Specifically, ensure that the server block is correctly referencing the SQL Server.

Updated configuration:

resource "azurerm_mssql_database" "sqldb" {
  for_each = var.databases

  name            = each.key
  server_id       = azurerm_mssql_server.sqlserver.id
  collation       = each.value.collation
  elastic_pool_id = azurerm_mssql_elasticpool.elasticpool.id
  sku_name        = "ElasticPool"

  auto_pause_delay_in_minutes = each.value.auto_pause_delay_in_minutes
}

resource "mssql_login" "sql_login" {
  server {
    host = "${local.db_server_name}.database.windows.net"
    login {
      username = var.sql_admin_user
      password = data.azurerm_key_vault_secret.mssql_admin_password.value
    }
  }

  login_name = var.sql_dbuser_username
  password   = var.sql_dbuser_password

  depends_on = [
    azurerm_mssql_server.sqlserver,
    azurerm_mssql_database.sqldb
  ]
}

resource "mssql_user" "sql_user" {
  server {
    host = "${local.db_server_name}.database.windows.net"
    login {
      username = var.sql_admin_user
      password = data.azurerm_key_vault_secret.mssql_admin_password.value
    }
  }

  username = var.sql_dbuser_username
  password = var.sql_dbuser_password
  database = var.sql_db_name
  roles    = var.sql_dbuser_roles

  depends_on = [
    azurerm_mssql_server.sqlserver,
    azurerm_mssql_database.sqldb,
    mssql_login.sql_login
  ]
}

Deployment:

Refer:

Timeout expired messages when connecting to SQL Server - SQL Server | Microsoft Learn

mssql_user | Resources | betr-io/mssql | Terraform | Terraform Registry

Login error for admin SQL Server during terraform plan - Stack Overflow answered by Nopesound