Funtion app to access warehouse with managed identity or other auth ways

[simontaylor]

I am now trying to get connection from function app to warehouse. 

I can do it once and only once since the MI will be blocked by default after the successful connection.

It is said that there is no email address with the service principal I am using hence the token will expire after the 1st use. Any idea how to get it up and running?

[simontaylor · Accepted solution]

MI works. I can close it now.

[v-hjannapu]

Hi @simontaylor,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to @deborshi_nag  ,  for those inputs on this thread. 

Direct Managed Identity authentication from Azure services such as Functions or Container Apps to Fabric Warehouse/Lakehouse SQL endpoints is generally not supported currently and that the currently supported production approach is to authenticate using a Service Principal. They are also right that a more secure pattern is to avoid client secrets and instead use certificate-based authentication, with the certificate stored in Key Vault and accessed using the Function App’s Managed Identity.

SQL access tokens are not single-use; they are short-lived (typically about one hour) and must simply be requested again whenever a new connection is created. Token expiry is expected behavior and does not prevent repeated connections as long as the application refreshes the token.

Given the current platform capabilities, the practical solution is to use a Service Principal authenticated with a certificate (not a secret), store the certificate securely in Key Vault, allow the Function App’s Managed Identity to retrieve it and then obtain a fresh SQL access token for each connection attempt. 

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Best Regards,
Community Support Team.

[simontaylor]

I can use MI to get it connected and then write query result to kusto with three consecutive successes now.

Will circle back once I get next one after some hours.

[simontaylor]

Great. Then can you please let me know how to create the cert in corp?

[simontaylor]

AME key vault cert or federated credentials?

[deborshi_nag]

no fed cred - not supported, only option to use AME. 

[simontaylor]

AME can access corp fabric data warehouse?

[deborshi_nag]

not sure what you mean by corp fabric data warehouse, but for any Fabric Warehouse recommended secretless method today for secure app authentication is SPN + Certificate (e.g., AME cert).

[simontaylor]

My situation doesn't support SPN + cert. I am checking whether I can upload the cert file to deploy it to linux server.

[simontaylor]

If I can use the key in the pfx file anc then I think it should work to do auth.

[deborshi_nag]

Hello @simontaylor 

I believe you are referring to Fabric User Defined Functions. These do indeed support Managed Identity or Service Principal, as outlined in the blog below.

Service principal and private library support for Fabric User data functions | Microsoft Fabric Blo…

By default, the use of Service Principals is restricted until access is granted at either the workspace or item level, and the relevant tenant setting is enabled to allow Service Principals to use Fabric APIs. If these steps are not completed, the initial attempt may succeed due to cached credentials, but subsequent attempts will fail.

To grant SPN access to the Fabric item, please refer to the following link:

Service Principals in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

The tenant setting to enable can be found in:

Fabric Admin Portal > Tenant settings > Developer settings > “Service principals can use Fabric APIs”

[simontaylor]

            logging.info("Trying with UAMI to do auth for fabric data warehouse connection")

            # Put your UAMI Client ID in an env var or config

            uami_client_id = os.environ.get("UAMI_CLIENT_ID")  # e.g. "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

            if not uami_client_id:

                raise ValueError("UAMI_CLIENT_ID is not set. Please set it to the User Assigned Managed Identity Client ID.")

            credential = ManagedIdentityCredential(client_id=uami_client_id)

            token = credential.get_token(self.SQL_SCOPE).token

            token_bytes = token.encode("utf-16-le")

            token_struct = struct.pack("<I", len(token_bytes)) + token_bytes  # [1](https://blog.fabric.microsoft.com/en-us/blog/announcing-support-for-workspace-identity-authenticatio...)

            logging.info(f"Auth mode resolved to '{auth_mode}'. Target server: {self.server} | DB: {self.database}")

            logging.info("Ready to use the token to try the connection.")

            self.connection = pyodbc.connect(base_conn_str, attrs_before={self.SQL_COPT_SS_ACCESS_TOKEN: token_struct})

            self.connection.autocommit = False

Here is my function app python code to get connections to Data Warehouse. I need to find a way to do auth so that data warehouse connection token won't expire.

[deborshi_nag]

Hello  @simontaylor 

Thanks for supplying the details. It seems you're using Python to access a SQL Endpoint. Unfortunately

Managed Identity (MI) does not currently work when connecting from Azure services (e.g., Azure Container Apps, Functions) to Fabric Lakehouse/Warehouse SQL endpoints. The recommended workaround is to use a Service Principal instead.

 

You can however store the SPN secret in Key Vault and use Managed Identity to access Key Vault. 

 

I've added small changes to add those bits -

import pyodbc
from typing import Callable
from azure.identity import DefaultAzureCredential, ClientSecretCredential
from azure.keyvault.secrets import SecretClient


KEYVAULT_URL = # e.g., "https://my-kv.vault.azure.net"
FABRIC_SERVER = # e.g., "xxxx.datawarehouse.fabric.microsoft.com"
FABRIC_DATABASE = # exact Warehouse/Lakehouse name

# Key Vault secret names (your choice)
TENANT_SECRET_NAME = "fabric-sp-tenant-id"
CLIENT_ID_SECRET_NAME = "fabric-sp-client-id"
CLIENT_SECRET_NAME = "fabric-sp-client-secret"
    

cred = DefaultAzureCredential()  # picks MI in Azure, falls back to other dev creds locally
client = SecretClient(vault_url=KEYVAULT_URL, credential=cred)

tenant_id = with_retry(lambda: client.get_secret(TENANT_SECRET_NAME).value)
client_id = with_retry(lambda: client.get_secret(CLIENT_ID_SECRET_NAME).value)
client_secret = with_retry(lambda: client.get_secret(CLIENT_SECRET_NAME).value)


sp_cred = ClientSecretCredential(tenant_id=tenant_id, client_id=client_id, client_secret=client_secret)
    token = with_retry(lambda: sp_cred.get_token("https://database.windows.net/.default").token)

    # ODBC connection string (no Authentication keyword; we'll pass token via attrs_before)
    conn_str = (
        "Driver={ODBC Driver 18 for SQL Server};"
        f"Server={FABRIC_SERVER};"
        f"Database={FABRIC_DATABASE};"
        "Encrypt=Yes;TrustServerCertificate=No;Connection Timeout=30;"
    )

    # Convert token to the format expected by the SQL Server ODBC driver
    token_bytes = token.encode("utf-16-le")
    token_struct = struct.pack("<I", len(token_bytes)) + token_bytes
    SQL_COPT_SS_ACCESS_TOKEN = 1256

    # Connect with token
    return pyodbc.connect(conn_str, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct}) 

Please ensure your MI has appropriate grants to read the SPN details from the KV. 

[simontaylor]

Sorry. SPN + Secret is not my choice. I can't use it in that way.

[simontaylor]

Solved: Using Managed Identity for Fabric Lakehouse SQL An... - Microsoft Fabric Community

I think the solution of the above link may work for my case. But I am not sure if I need to use key vault cert or federated credentials to do auth.