set application as an admin

[nazirakz]

Hello! I'm trying to do a secret-less connection between the app service and Azure SQL by using mssql_user
below the code:

resource "mssql_user" "web" {
server {
host = var.host_name
azure_login {
tenant_id = var.tenant_id
client_id = var.sql_sp_client_id (used client id of the application that is the ad admin of the SQl server)
client_secret = data.azurerm_key_vault_secret.AAD_admin_password.value
}
}

object_id = var.webapp_principal_id (used oblect_id of the webapp service principal )
database = var.database_name
username = var.webapp_name
roles = ["db_datareader", "db_datawriter"]
}

data "azurerm_key_vault_secret" "AAD_admin_password" {
name = "terraform-iac-secret"
key_vault_id = local.key_vault_id
}

As an admin, I set an application by using
azuread_administrator {
login_username = var.sql_server_ad_admin_username
object_id = var.sql_server_ad_admin_object_id
}

but in the end it throughs me this error: Error: unable to create user [tenant-DB].[wapp_name]: mssql: login error: Login failed for user ''.

Please, could you provide me with some feedback, thanks.

[JoshDouglasOU]

did you ever get this resolved. i think i have the same issue.

my sp is part of a group that is entra admin on the sql server, but i get getting login failed

[Masahigo]

I got it working at least. I was getting that same error if/when the Service Principal's client secret was rotated. The issue was that this TF provider stores the initial client secret to remote state and doesn't support the use case of rotating the credential after that.

Check my comment from here: https://dev.to/masahigo/comment/2800b