pythonsql-serverazure-aksazure-managed-identity

ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource

I'm trying to access SQL Database using Managed Identity from AKS cluster. I Configured and deployed my python application on Azure Kubernetes Cluster. The problem is configuring my resource to have a managed identity” in Azure. We followed and configured managed identity from Microsoft spec doc and but it didn't work.

Error:

ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource.

Steps Followed:

from azure.identity import ManagedIdentityCredential
import pyodbc, struct
import pandas as pd

def get_conn():
    
    connection_string = "Driver={ODBC Driver 17 for SQL Server};Server=tcp:XXXXXX.database.windows.net,1433;Database=XXXXX;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30"
    
    credential = ManagedIdentityCredential(client_id="XXXXXX") # Client id of Managed Identity
    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
    
    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    
    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
    conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
    
    return conn

def get_all():
    
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM test_table_data")
        
        data = pd.DataFrame(cursor.fetchall())
        print(data.head())
        
    return

Also created a User in SQL,

CREATE USER [Msi_name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [Msi_name];
ALTER ROLE db_datawriter ADD MEMBER [Msi_name];
ALTER ROLE db_ddladmin ADD MEMBER [Msi_name];
GO

Any advise would be appreciated and helpful. Thanks

Solution

  • The AKS Cluster can't be linked directly with a Managed Identity, as you would do with another resource like a Virtual Machine or an Azure Function.

    Within your AKS Cluster, so inside your Kubelet, you get an identity platform which is separated than your Microsoft Entra ID Identity Platform (or at least not directly connected so to say).

    It does not mean you can't use your Managed Identity, but you need a step first. You must accomplish a Token Exchange between your AKS Cluster User Identity and Microsoft Entra ID. Then with the token of Entra ID, you can perform operations on Azure Control Plane.

    enter image description here

    It can be done via Workload Identity, which is practical. That means your Identity Provider of the AKS Cluster can be trusted by Entra ID to fetch tokens.

    Microsoft does provide a nice tutorial for it. Since you have already your AKS Cluster up and running and a Managed Identity, you need only to:

    You can follow all steps here: https://learn.microsoft.com/en-us/azure/aks/workload-identity-deploy-cluster#update-an-existing-aks-cluster