MySQL AAD User Creation Filed

[hitty5]

MySQL AAD user creation fails when using service principal. When using the AAD authenticated administrator to login to MySQL and performing the creation manually, it works:

SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER 'func_pipeline' IDENTIFIED BY '<client_id>'

According to the AZ documentation aad_auth_validate_oids_in_tenant must set to OFF, that's not done by the provider.

The MySQL server instance has set the AAD administrator by using azurerm_mysql_active_directory_administrator.

Terraform Version

• 1.4.5
• petoju/mysql: 3.0.35

Affected Resource(s)

Please list the resources as a list, for example:

• mysql_user

Terraform Configuration Files

resource "mysql_user" "aad_user" {
  user        = "func_system_assigned_identity"
  auth_plugin = "aad_auth"
  aad_identity {
    type     = "service_principal"
    identity = <client id of system assigned managed identity of resource, e.g. function app"
  }
}

Panic Output

mysql_user.aad_user: Creating...
╷
│ Error: failed executing SQL: Error 9102 (HY000): An unexpected error occurred while trying to validate user '<client_id>'.

Expected Behavior

• MySQL AADUSER is created

Actual Behavior

• MySQL AADUSER creation fails

Steps to Reproduce

1. Create service principal or use system assigned or user managed identity
2. use client id
3. terraform apply

[petoju]

@kratkyzobak what do you think? Did you test the code somehow?

[kratkyzobak]

I have tested it against MySQL Flexible as Single server (where documentation link points) is “on retirement path”.

@hitty5 can you please confirm, you are trying it against MySQL Single Server resource? If so, we need somehow detect it (probably by provider configuration flag/feature setting??) to call given SET statement before for this type of servers.

[kratkyzobak]

I have retested it against "Azure Database for MySQL Flexible Server" with two obsertvations:

First - you can use SET aad_auth_validate_oids_in_tenant = OFF and it really turns off OIDs verification against Tenant.

Second - when you create user for Managed identity with SET aad_auth_validate_oids_in_tenant = ON there is translation of Client ID into Object ID (Service principal ID) => It should be set to Service principal ID for Managed identities.

@hitty5 can you please try connecting from Function App as user, you created manually? I belive, token of this user would contain service principal ID in oid field which is not registered in your MySQL instance? If so, please try to create user with providing service principal ID instead of client ID.

You can find service principal ID in managed identity blade in Azure portal or as output named principal_id from azurerm_user_assigned_identity or as identity.principal_id from azurerm_function_app.

[hitty5]

@kratkyzobak yes, I can confirm that I have used MySQL single server resource.

User assigned identity I have created an user assigned identity and attached it to the function app. neither principal id nor client id is working with create aaduser statement.

[kratkyzobak]

@hitty5 Can you please do one more test - does this statement work when used manually?

SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER 'func_pipeline'@'%' IDENTIFIED BY '<client_id>'

Just to be sure, there is not problem with passing hostname and it's really issue only with aad_auth_validate_oids_in_tenant.

I will then send PR to call this statement as it does not hurt for MySQL Flexible if used properly so probably no problem with using it.

[kratkyzobak]

By digging in code and searching for place, where to modify this i found, that you should be able to extend provider configuration by

provider "mysql" {
    endpoint = "azure://..."
    username = "adminname"
    conn_params = {
       aad_auth_validate_oids_in_tenant = "OFF"
    }
}

Can you please try if this solution is working for you?

[hitty5]

@kratkyzobak setting the parameter within the provider configurations works. But now the AAD user gets re-created each time when TF plan is applied:

# mysql_user.aad_func_cps_feedback must be replaced
-/+ resource "mysql_user" "aad_user" {
      ~ id          = "user@%" -> (known after apply)
        # (4 unchanged attributes hidden)

      + aad_identity { # forces replacement
          + identity = "<client id>"
          + type     = "service_principal"
        }
      - aad_identity { # forces replacement
          - identity = "user" -> null
          - type     = "group" -> null
        }
    }

Here the TF code:

resource "mysql_user" "user" {
  user        = "user
  auth_plugin = "aad_auth"
  host        = "%"
  aad_identity {
    type     = "service_principal"
    identity = "<cliend id>"
  }
}

Somehow the type is not "service_principal" after creation. I've checked to mysql.user table and the authentication_stringfor the user is not changing after apply.

[kratkyzobak]

Can you please send output of SHOW CREATE USER 'user'@'%' ? Most important part is AAD auth data which are saved there. For Service principal, there is excepted to be AADSP:<client-id>:upn:user. MySQL Single Server probably uses older version of aad_auth plugin and auth string here would differ.

[hitty5]

| CREATE USER 'user'@'%' IDENTIFIED WITH 'aad_auth' AS 'AADApp:<client id>:upn:user' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |

You are right, the authentication string looks different.

[petoju]

Fix is released in 3.0.36