Authenticate with Azure Service Principal on MySQL Azure AD enabled instance failed

[3d-pro]

Hi! I'm trying to user Azure Service Principal to authenticate into MySQL Azure AD enabled instance and create MySQL Azure AD user & grant. Which it's always failed with error below. I also tried using null_resource to run command to manually create with shell script and it's working fine.

Terraform Version

• Terraform v1.3.9
• petoju/mysql: v3.0.37

Affected Resource(s)

• mysql_user
• mysql_grant

Terraform Configuration Files

provider "mysql" {
  endpoint = "azure://${module.mysql.mysql_server_fqdn}"
  username = "<service_principal_name>"
}

resource "mysql_grant" "mysql_admin" {
  user       = "mysql_admin"
  host       = "%"
  database   = "*"
  privileges = ["SELECT", "INSERT", "UPDATE", "DELETE", "CREATE", "DROP", "RELOAD", "PROCESS", 
  "REFERENCES", "INDEX", "ALTER", "SHOW DATABASES", "CREATE TEMPORARY TABLES", "LOCK TABLES", 
  "EXECUTE", "REPLICATION SLAVE", "REPLICATION CLIENT", "CREATE VIEW", "SHOW VIEW", "CREATE ROUTINE", 
  "ALTER ROUTINE", "CREATE USER", "EVENT", "TRIGGER"]
}

Panic Output

module.teleport.mysql_grant.mysql_admin[0]: Creating...
Error: failed to connect to MySQL: could not connect to server: Error 9106 (HY000): Azure AD access token is not valid for user '<service_principal_name>' (or does not contain group ID 'xxxx-xxxx-xxxx-xxxx-xxxx').

with module.teleport.mysql_grant.teleport_admin[0],
 on .terraform/modules/teleport/main.tf line 228, in resource "mysql_grant" "mysql_admin":
resource "mysql_grant" "mysql_admin" {

Expected Behavior

• User mysql_admin should be able to grant all the privileges in configuration

Actual Behavior

• Grant for mysql_admin user is failed

Steps to Reproduce

1. Create & use Azure service principal as authentication.
2. Setup mysql provider to use service principal as username to login instead of user or group.
3. terraform apply

[petoju]

Are you sure your Azure AD access token is correct? Does it contain the group and is it valid for that user? You can look into that token.

@kratkyzobak may know more, but I doubt he'll tell you much more than me.

[kratkyzobak]

Is the same as name given to mysql admin during server creation?

How do you authenticate against Azure at all? Are you using az cli or setting environment variables?

[3d-pro]

@petoju I tested by using null resource to run the script to get AAD access token and run mysql command to create user. It can created successfully. Here's the example of my code:

...
// Using existing service principal in environment to authenticate
data "external" "azure_db_access_token" {
  program = ["az", "account", "get-access-token", "--resource-type", "oss-rdbms", "--output", "json"]
}

resource "null_resource" "mysql_user_example_admin" {
  triggers = {
    mysql_host     = azurerm_mysql_flexible_server.main.fqdn
    mysql_username = "<service-principal-name>" // same name as AAD Administrator login
    mysql_password = data.external.azure_db_access_token.result.accessToken // get output from above data
  }

  provisioner "local-exec" {
    when        = create
    command     = "LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=y mysql -h ${self.triggers.mysql_host} -u ${self.triggers.mysql_username} --password=${self.triggers.mysql_password} -e \"SET aad_auth_validate_oids_in_tenant = OFF;CREATE AADUSER 'example_admin' IDENTIFIED BY '${azurerm_user_assigned_identity.teleport_db_mysql.client_id}';\""
    interpreter = ["bash", "-c"]
  }

  provisioner "local-exec" {
    when        = destroy
    command     = "LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=y mysql -h ${self.triggers.mysql_host} -u ${self.triggers.mysql_username} --password=${self.triggers.mysql_password} -e \"DROP USER 'example_admin';\""
    interpreter = ["bash", "-c"]
    on_failure  = continue
  }

  depends_on = [
    data.external.azure_db_access_token
  ]
}

@kratkyzobak yes the <service_principal_name> is the same name as login name in AAD resource:

# Using current service principal that run this terraform as AAD admin
data "azurerm_client_config" "current" {}

resource "azurerm_mysql_flexible_server_active_directory_administrator" "main" {
  server_id   = azurerm_mysql_flexible_server.main.id
  identity_id = azurerm_user_assigned_identity.main.id
  login       = "<service_principal_name>"
  object_id   = data.azurerm_client_config.current.client_id
  tenant_id   = data.azurerm_client_config.current.tenant_id
}

[kratkyzobak]

In your example you are using az cli, not servuce principal from environment. Are you completely sure, that token gotten from your external data source is not yours and belongs to service principal?

[3d-pro]

@kratkyzobak I am authenticating using this command in the shell before running terraform. az login -t <tenant-id> --service-principal -u <service-principal-client-id> -p <service-principal-client-secret>

[kratkyzobak]

Is it possible, you have any environment variables for azure identity set in runtime where you are testing?

When you test it, you are using directly AzCli identity. When provider tries to get token, it uses “default credentials” from Azure SDK for Go. AzCli is last one attempted in this case. Is it possible, you have any workload identity, managed identity environment variables or sthg like this? Another client secret for another service principal?

[wortner]

For us the problem is that it "falls back" to Workload identity credentials and there is no way in provider to skip it. It would be great to have some possibility to utilize DefaultAzureCredentialOptions.Exclude...Credential options.

[kratkyzobak]

For us the problem is that it "falls back" to Workload identity credentials and there is no way in provider to skip it. It would be great to have some possibility to utilize DefaultAzureCredentialOptions.Exclude...Credential options.

Problem here is, that azidentity package has a lot of configuration. Implement them all in advance was not a choice as it's really complex logic and required configuration for it (see https://github.com/hashicorp/terraform-provider-azurerm/blob/main/internal/provider/provider.go). I would like to use some template from AzureRM/AzureAD providers but there is none - even this providers uses copy&paste.

I belive, @petoju would not mind if you implement provider configuration property needed for your usage.

@wortner Do you need to use Workload identity anywhere else within same terraform module as MySQL provider is used? If no, you can just overwrite ENV variables for terraform process to set AZURE_CLIENT_ID and/or AZURE_FEDERATED_TOKEN_FILE to empty values so it will skip WIF.

[petoju]

@wortner FWIW it's OK azidentity has a lot of configuration. It's the same with every other (cloud) login.

My preference is to support the behavior that happens everywhere by default - ideally both in CLI and in some kind of library or SDK. That's predictable, easier to under and debug. I believe that is what is happening with current code.

Even from DevOps perspective, more explicit specification of credentials makes it issue in long run. Someone debugging this in the future once it stops working will be like "Why does $program find the proper permissions and this provider doesn't?". So implementing this will cause some WTF moments.

That said, if there's someone with enough time to implement it and it's strictly optional (=saying nothing won't affect behavior), I'd merge it.

[wortner]

@kratkyzobak we will empty the envs. For us it is good enough.

[wortner]

The issue was that AzureRm provider requires ARM variables and MySql depends on default Azure SDK AZURE variables. More over the TerraformTask of azure devops does not set the variables visible for the environment at all. Solution was to remove the task in favor of AzCli task and set all needed variables by hand. I am wondering why they opted for ARM_ variables and not the Azure SDK ones.