azure-service-operator: Unable to create AAD User

Version of Azure Service Operator ASO V2 being used to create flexibleserversadministrator and user_aad. Facing below issue -

0927 03:48:29.053859       1 aad_user.go:134] controllers/UserController "msg"="Retrieved token for MySQL" "azureName"="testaadusers" "expires"="2023-09-28T01:28:57.934530917Z" "name"="testaadusers" "scope"="https://ossrdbms-aad.database.windows.net/.default"
E0927 03:48:29.088605       1 generic_reconciler.go:360] controllers/UserController "msg"="Encountered error impacting Ready condition" "error"="Reason: Failed, Severity: Warning, RetryClassification: RetrySlow, Cause: failed to connect database. Server: asotest-mysql-server.mysql.database.azure.com, Database: mysql, Port: 3306, AdminUser: shalinin: error pinging the mysql db (asotest-mysql-server.mysql.database.azure.com:3306/mysql): Error 1045: Access denied for user 'shalinin@'51.105.253.159' (using password: YES)" "name"="testaadusers"

I have already created flexibleserver and flexibleserversdatabase by following this wiki - https://github.com/Azure/azure-service-operator/blob/09b0cef5265ce6c6be7eee4643508037de6e43d0/v2/samples/dbformysql/v1api/v1api20210501_flexibleserver.yaml

Managed identity and federated identity created as well using this link - https://github.com/Azure/azure-service-operator/blob/09b0cef5265ce6c6be7eee4643508037de6e43d0/v2/samples/managedidentity/v1api/v1api20181130_userassignedidentity.yaml

Describe the bug Create flexibleserversadministrator and facing issue in creating user_aad.yaml and user.yaml. Facing below issue for user_aad.yaml-

0927 03:48:29.053859       1 aad_user.go:134] controllers/UserController "msg"="Retrieved token for MySQL" "azureName"="testaadusers" "expires"="2023-09-28T01:28:57.934530917Z" "name"="testaadusers" "scope"="https://ossrdbms-aad.database.windows.net/.default"
E0927 03:48:29.088605       1 generic_reconciler.go:360] controllers/UserController "msg"="Encountered error impacting Ready condition" "error"="Reason: Failed, Severity: Warning, RetryClassification: RetrySlow, Cause: failed to connect database. Server: asotest-mysql-server.mysql.database.azure.com, Database: mysql, Port: 3306, AdminUser: shalinin: error pinging the mysql db (asotest-mysql-server.mysql.database.azure.com:3306/mysql): Error 1045: Access denied for user 'shalinin@adobe.com'@'51.105.253.159' (using password: YES)" "name"="testaadusers"

To Reproduce Steps to reproduce the behavior:

Followed the steps - https://github.com/Azure/azure-service-operator/blob/09b0cef5265ce6c6be7eee4643508037de6e43d0/v2/samples/dbformysql/v1api/v1_user.yaml
Admin created well -

kubectl get flexibleserversadministrator.dbformysql.azure.com/shalinin
NAME       READY   SEVERITY   REASON      MESSAGE
shalinin   True               Succeeded

Facing issue when created user_aad.yaml -

kubectl get user.dbformysql.azure.com/testaadusers
NAME           READY   SEVERITY   REASON   MESSAGE
testaadusers   False   Warning    Failed   failed to connect database. Server: asotest-mysql-server.mysql.database.azure.com, Database: mysql, Port: 3306, AdminUser: shalinin: error pinging the mysql db (asotest-mysql-server.mysql.database.azure.com:3306/mysql): Error 1045: Access denied for user 'shalinin'@'51.123.123.159' (using password: YES)

Expected behavior Should be able to connect to the server without providing password as using managed identity.

Screenshots If applicable, add screenshots to help explain your problem.

Additional context aad_user.yaml file -

apiVersion: dbformysql.azure.com/v1
kind: User
metadata:
  name: testaadusers
  namespace: abc
spec:
  owner:
    name: asotest-mysql-server
  # Specify a list of server-level privileges. Privileges
  # include the following: RELOAD, PROCESS, SHOW DATABASES,
  # REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER
  privileges:
    - PROCESS
    - CREATE USER
  databasePrivileges:
    mysqldatabase-sample:
      # Privileges include the following:
      # SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
      # ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW,
      # SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER
      - SELECT
  aadUser:
    # alias is optional if the name of the user is less than 32 characters but required if it's longer
    # than 32 characters. Note that if alias is specified it is the name used to configure/control the user
    # via SQL commands, but the actual name you use to log into the Server with is the name specified in .spec.azureName.
    # For example if my azureName is "myuser@mydomain.onmicrosoft.com" and alias is "myuser", when logging in specify
    # username "myuser@mydomain.onmicrosoft.com".
    #alias: myalias
    # The serverAdminUsername should match the login name of the server AAD administrator.
    # If the administrator was configured with ASO, that field is .spec.login on the FlexibleServersAdministrator type.
    # If the administrator is a group, the ServerAdminUsername should be the group name, not the actual username of the
    # identity to log in with. For example if the administrator group is "admin-group" and identity "my-identity" is
    # a member of that group, the ServerAdminUsername should be "admin-group"
    serverAdminUsername: "shalinin"

About this issue

Original URL
State: closed
Created 9 months ago
Comments: 25 (11 by maintainers)

Most upvoted comments

You had a couple quesitons:

Question 1:

This is very strange. I think the problem is when i delete any of existing resources which i dont want then this error starts coming up in all the other resources, UMI, Federated credentials.

How and what resources did you delete?

Part of the instruction for creating AAD MySQL Users is to:

Create a Managed Identity
Use that managed identity as ASOs identity.

So if you deleted the managed identity which ASO was using as its managed identity (the identity which it is communicating to Azure with), then it’s expected that all of its calls to Azure fail.

If you don’t want to use that identity for the whole operator and instead only want to use it for managing the SQL user, you can look into the credential scope options, and specify serviceoperator.azure.com/credential-from on the User object. Or based on the snippet you’ve shown maybe you’re already using that but you deleted things in the wrong order, if you’re using that open to make ASO use the UMI for the MySQL RGs or namespaces, and you delete the UMI, then ASO will be unable to manage those resources anymore.

You need to delete the resources in ASO first, and then optionally the UMI. (If you’re using namespace scoped secrets really you should delete the whole namespace, and only then delete the UMI for that namespace)

Question 2:

Just want to understand here, if we create flexibleserversadministrators named shalinin then we can not use it in creating another flexibleserver. As shalinin is now bounded with 1 flexible server. If tries to connect shalinin with 2 flexible server, it is unable to connect and throw above patch error.

It looks to me like what you did here is tried to edit an existing ASO FlexibleServerAdministrator object named shalinin and have it point to a different FlexibleServer owner (at least, that’s what the webhook is saying). You cannot do this.

Possibly the confusion here is around “name in Kubernetes” versus “name in Azure”.

You can create a different FlexibleServersAdministrator, with a different name in Kubernetes (say shalinin2) and have that point to the new flexible server. It can even (I think) share the same name (in Azure), by setting the azureName field in the CRD to whatever you like. This is because name-uniqueness in Azure is the “fully qualified name” (flexibleServers/foo/administrators/shalinin is different than flexibleServers/bar/administrators/shalinin), but in Kubernetes its by Namespace + Name.

Basically there is a 1-1 mapping between “resource in Kubernetes” and “resource in Azure”. Since you’ve got 2 flexible servers, with with different administrators (the admins may be the same identity, but the actual administrator object is 1 per flexible server) you need 2 Kubernetes resources to match, not 1.

Question 3:

I just created flexibleserversadministrators named with my UMI and getting error -InternalServerError An unexpected error occured while processing the request. Tracking ID: ‘87903e’: GET https://management.azure.com/subscriptions/98790/providers/Microsoft.DBforMySQL/locations/westeurope/azureAsyncOperation/987680…

This looks like an error from the MySQL RP itself. We might need to engage them on this if it keeps happening consistently.

matthchr on Sep 29, 2023