Issues with Managed Identity Authentication via Azure DevOps - DB connection failed after 30s timeout

[mischmuc]

Previously, I was using the provider with an Azure DevOps Service Account connected via Service Principal, and it was working fine. Now, I replaced it with a Service Account using Managed Identity with Federated Credentials and updated the code to use azuread_managed_identity_auth with user_id as described in the provider documentation. Unfortunately, it is not working.

I initially thought it was a firewall issue, but my DevOps Agent IP is set in the firewall rules, and the managed identity is assigned as Entra admin.

I still receive the error: "Unable to create user [db-name].[mi-user]: DB connection failed after 30s timeout."

I am using the following ID for user_id: /subscriptions/.../resourcegroups/rg-name/providers/Microsoft.ManagedIdentity/userAssignedIdentities/mi-name.

If I switch back to the Service Principal, it works again. All my other resources are also working fine with the Managed Identity.
Any idea what the problem could be?

resource "mssql_user" "mi_db_user" {
  for_each = local.managed_identity

  server {
      host = data.azurerm_mssql_server.sql_server.fully_qualified_domain_name
      azuread_managed_identity_auth {
         user_id = local.mi_admin_id_string
      }
    }

  database       = data.azurerm_mssql_database.sql_database.name
  username      = each.value.sql_username
  roles              = ["db_datareader", "db_datawriter", "db_executor"]
  default_schema = "dbo"
  
  depends_on = [data.azurerm_mssql_server.sql_server, azuread_group_member.sqlserver_member]
}

[korneel76]

I have got the same issue. Whatever I try, I always get the same error:
Error: unable to create user [xxx].[xxx]: db connection failed after 30s timeout.

@mischmuc, you say it works for you when you use the provider with an Azure DevOps Service Account connected via Service Principal. When I do that I still get the timeout.
What does your resource look like in your old situation? The only thing you change is "azuread_managed_identity_auth" ?

[mischmuc]

@korneel76 Yes, with via Service Principal it is working for me, you have to use

server {
      host = "your_sql_server_fully_qualified_domain_name"
         azure_login {
            tenant_id = "your_tenant_id"
            client_id = "your_sp_client_id"
            client_secret = "your_sp_client_secret"
         }
}

In addition at least in my setup I'm using the same Service Principal as EntraID Admin on the Server and for the permissions to AD (EntraId) like described here: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal?view=azuresql

My Server is running behind a Vnet / Firewall so I also have to open the firewall for the current DevOps Agent IP.

Hope this helps

[korneel76]

@mischmuc Thanks. I tried the azure_login already, but I'm not sure about the permissions. So I will try again. ;-)