Authenticating to MSSQL via Managed Identity AzureAD administrator is failing

[bak-fts]

What happened?

I tried to authenticate provider-sql as the AzureAD administrator of an MSSQL Server provisioned by Crossplane via the MSSQLServer.sql.azure managed resource with the AzureAD administrator referencing a UserAssignedIdentity.managedidentity managed resource.

To do this, I constructed a secret referenced by the ProviderConfig.mssql object using the client ID as username of the User Assigned Identity, FQDN as endpoint of the MSSQL Server, and fedauth set to ActiveDirectoryManagedIdentity.

When attempting to manage resources in Crossplane via this SQL provider and this provider config, I got an error on the resources like the following:

    Message:               observe failed: cannot select user: The requested identity isn't assigned to this resource.
    Reason:                ReconcileError
    Status:                False
    Type:                  Synced

How can we reproduce it?

Create a managed identity and MSSQL Server like the following.

          # ---------------------------------------------------------
          # Admin User Assigned Identity
          # ---------------------------------------------------------
          - name: AdminUserAssignedIdentity
            base:
              apiVersion: managedidentity.azure.upbound.io/v1beta1
              kind: UserAssignedIdentity
              metadata:
                labels:
                  selector: admin
              spec:
                forProvider:
                  name: id-admin
                  location: switzerlandnorth
                  resourceGroupNameSelector:
                    matchLabels:
                      selector: database
            patches:
              - type: ToCompositeFieldPath
                fromFieldPath: status.atProvider.clientId
                toFieldPath: status.internal.adminIdentityClientId
              - type: ToCompositeFieldPath
                fromFieldPath: status.atProvider.principalId
                toFieldPath: status.internal.adminIdentityPrincipalId
                # To
              - type: ToCompositeFieldPath
                fromFieldPath: status.atProvider.clientId
                toFieldPath: status.internal.adminIdentityClientId

          # ---------------------------------------------------------
          # MS SQL Server
          # ---------------------------------------------------------
          - name: MSSQLServer
            base:
              apiVersion: sql.azure.upbound.io/v1beta1
              kind: MSSQLServer
              metadata:
                labels:
                  selector: main
              spec:
                forProvider:
                  resourceGroupNameSelector:
                    matchLabels:
                      selector: database
                  administratorLogin: fides
                  administratorLoginPasswordSecretRef:
                    key: password
                    name: "" # patched
                    namespace: default
                  azureadAdministrator:
                    - loginUsernameSelector:
                        matchControllerRef: true
                        matchLabels:
                          selector: admin
                      objectIdSelector:
                        matchControllerRef: true
                        matchLabels:
                          selector: admin
                  minimumTlsVersion: "1.2"
                  publicNetworkAccessEnabled: false
                  identity:
                    - type: SystemAssigned
                  location: switzerlandnorth
            patches:
              - type: FromCompositeFieldPath
                fromFieldPath: spec.forComposer.sqlServerVersion
                toFieldPath: spec.forProvider.version
                # To
              - type: ToCompositeFieldPath
                fromFieldPath: status.atProvider.fullyQualifiedDomainName
                toFieldPath: status.internal.sqlServerEndpoint

Then create a secret to be referenced by the ProviderConfig, and the ProviderConfig object itself:

          # ---------------------------------------------------------
          # SQL Connection Secret
          # ---------------------------------------------------------
          # Instead of relying on writeConnectionSecretToRef in MSSQL Server,
          # we write it manually to set fedauth=ActiveDirectoryManagedIdentity
          - name: SQLConnectionSecret
            base:
              apiVersion: kubernetes.crossplane.io/v1alpha1
              kind: Object
              spec:
                providerConfigRef:
                  name: crossplane-cluster
                forProvider:
                  manifest:
                    apiVersion: v1
                    kind: Secret
                    metadata:
                      name: sql-connection-secret
                      namespace: default
                    type: Opaque
                    data:
                      # echo -n ActiveDirectoryManagedIdentity|base64
                      fedauth: QWN0aXZlRGlyZWN0b3J5TWFuYWdlZElkZW50aXR5
            patches:
              - type: FromCompositeFieldPath
                fromFieldPath: status.internal.sqlServerEndpoint
                transforms:
                  - type: string
                    string:
                      type: Convert
                      convert: ToBase64
                toFieldPath: spec.forProvider.manifest.data[endpoint]
              - type: FromCompositeFieldPath
                fromFieldPath: status.internal.adminIdentityClientId
                transforms:
                  - type: string
                    string:
                      type: Convert
                      convert: ToBase64
                toFieldPath: spec.forProvider.manifest.data[username]

          # ---------------------------------------------------------
          # SQL ProviderConfig Kubernetes Object
          # ---------------------------------------------------------
          - name: SQLProviderConfigKubernetesObject
            base:
              apiVersion: kubernetes.crossplane.io/v1alpha2
              kind: Object
              spec:
                providerConfigRef:
                  name: crossplane-cluster
                forProvider:
                  manifest:
                    apiVersion: mssql.sql.crossplane.io/v1alpha1
                    kind: ProviderConfig
                    metadata:
                      name: default
                    spec:
                      credentials:
                        source: MSSQLConnectionSecret
                        connectionSecretRef:
                          name: sql-connection-secret
                          namespace: default

Finally, try to provision any resource using the provider-sql provider and observe the error.

  Warning  CannotObserveExternalResource  23s (x103 over 22h)  managed/user.mssql.sql.crossplane.io  cannot select user: The requested identity isn't assigned to this resource.

What environment did it happen in?

Crossplane version: 1.15 provider-sql version: v0.9.0

[Duologic]

Is this a duplicate of #165?