I am trying to connect Azure SQL with AKS using managed Identity but this doesnt seems to work. I have been checking numerous blogs and I am as confused as I started. I am using aad-pod-identity:
https://github.com/Azure/aad-pod-identity
https://blog.seblab.be/posts/configure-aad-pod-ientity-on-aks/
https://medium.com/devopsturkiye/azure-kubernetes-service-aks-mimarisinde-aad-pod-identity-943effa54a39
https://medium.com/devopsturkiye/azure-kubernetes-service-aks-mimarisinde-aad-pod-identity-943effa54a39
I tried to create an admin user as well, using this query:
/* Create db contained user for user assigned identity
you can use the principal name or display name */
CREATE USER sqlpodid FROM EXTERNAL PROVIDER;
/* Assign the needed security roles to this db contained user */
ALTER ROLE db_datareader ADD MEMBER sqlpodid;
ALTER ROLE db_datawriter ADD MEMBER sqlpodid;
/* Check that the assignation is ok */
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;
apiVersion: "aadpodidentity.k8s.io/v1"
kind: AzureIdentityBinding
metadata:
name: sqlaadbinding1
spec:
AzureIdentity: my-identity
Selector: connectsqlserver
kind: AzureIdentity
metadata:
name: my-identity
spec:
type: 0
ResourceID: /subscriptions/<sub id>/resourcegroups/<MC_resource_group/providers/Microsoft.ManagedIdentity/userassignedIdentities/sqlpodid
ClientID: <cliendId of sqlpodid>
The MIC pod repeatedly showing the below log:
reconciling identity assignment for [/subscriptions/<sub id>/resourcegroups/<MC_resource_group>/providers/Microsoft.ManagedIdentity/userassignedIdentities/sqlpodid] on node aks-agentpool-vmss
The NMI pod is showing:
server.go:427] failed to get matching identities for pod: default/pod, error: clientID in request: id##### REDACTED #####id, getting assigned identities for pod default/pod in CREATED state failed after 16 attempts, retry duration [5]s, error: <nil>. Check MIC pod logs for identity assignment errors
2023-07-27T17:15:05.971574924Z I0727 17:15:05.971454 1 server.go:239] status (404) took 80016338075 ns for req.method=GET reg.path=/metadata/identity/oauth2/token req.remote=10.244.0.32
I dont know what is the redacted clientId, I tried to search in agentpool managed identity, user managed identity, AKS cluster or ressource group. I didnt find it anywhere.
Other than that I tried to give my managed Identity a role assignment as well.
az role assignment create --role "Managed Identity Operator" --assignee <CLIENT ID OF AKS CLUSTER> --scope <FULL RESOURCE ID OF MANAGED IDENTITY>
for me in AKS cluster the serviePrinipalProfile.clientId = msi.
so I chose the clientid from: identityProfile.kubeletidentity.clientId.
The pod definition is simple. Its a simple Spring boot App which has
Spring.datasource.url=url: "jdbc:sqlserver://<SRV>.database.windows.net:1433;database=<DB>;authentication=ActiveDirectoryMSI;msiClientId=<MI ID>;selectMethod=cursor;responseBuffering=full;sendStringParametersAsUnicode=false;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
where MSI clientId is the Managed Identity "sqlpodid"'s clientid. Some places its mentioned as PrincipalId or ObjectId I am not sure what goes here.
This is my pod yaml:
apiVersion: v1
kind: Pod
metadata:
name: pod
labels:
aadpodidbinding: connectsqlserver
spec:
containers:
...
nothing seems to work.
