provider-sql
provider-sql copied to clipboard
Authenticating to MSSQL via Managed Identity AzureAD administrator is failing
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