Access cosmos db through azure synapse analytics notebook using system assigned managed identity linked service

Syed Ali Naqi 0 Reputation points
2023-09-13T09:50:20.6066667+00:00

i have made a linked service for cosmos db no sql using system assigned managed identity as auth type and linked service is published as well. Now when i access this linked service from synapse analytics notebook using below code it gives me this error:POST failed with 'Bad Request' (400) and message: {"result":"DependencyError","errorId":"BadRequest","errorMessage":"[Code=AuthTypeNotSupported, Target=LS_MI_CosmosDb, Message=Linked Service Auth Type not supported]}. my code is:

import pandas as pd
import json

from azure.identity import DefaultAzureCredential
from azure.cosmos import CosmosClient
# Read CSV data into a DataFrame
csv_file_path = "file path"
data_frame = pd. read_csv(csv_file_path, encoding='latin1')
# Convert DataFrame to JSON
json_data = data_frame.to_json(orient='records')

# Initialize Cosmos DB client with managed identity
cosmosdb_endpoint = "endpoint"
database_name = "SNM"
container_name = "AliTest"
credential = mssparkutils.credentials.getConnectionStringOrCreds("Linked service") // error on this line

client = CosmosClient(cosmosdb_endpoint, credential=credential)

# Get a reference to the Cosmos DB container
container = client.get_database_client(database_name).get_container_client(container_name)

# Insert JSON data into Cosmos DB container
for item in json.loads(json_data):
    container.upsert_item(item)
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,329 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Amira Bedhiafi 14,571 Reputation points
    2023-09-13T11:21:15.9533333+00:00

    The error message you're receiving indicates that the method or utility you're using to obtain the credentials (mssparkutils.credentials.getConnectionStringOrCreds("LS_MI_CosmosDb")) does not support the Managed Identity authentication type for Cosmos DB, at least in the way it's configured.

    To use System Assigned Managed Identity with Azure Cosmos DB, you'll generally follow these steps:

    1. Grant the System Assigned Managed Identity of your Synapse workspace permissions to your Cosmos DB.
    2. Use Azure Identity libraries to authenticate the client.

    However, in the Synapse context, the mssparkutils.credentials.getConnectionStringOrCreds method doesn't provide the expected token or credential type for Cosmos DB when using Managed Identities.

    1. Make sure you have the azure-identity package installed in your Synapse environment.
    2. Use the DefaultAzureCredential from the azure.identity library to get the token for Cosmos DB.
    3. Use that token with the Cosmos DB SDK.
    
    import pandas as pd
    
    import json
    
    from azure.identity import DefaultAzureCredential
    
    from azure.cosmos import CosmosClient
    
    # Read CSV data
    
    csv_file_path = "https://snmpjpedevsa.blob.core.windows.net/alitest/MachinesData.csv"
    
    data_frame = pd.read_csv(csv_file_path, encoding='latin1')
    
    # Convert DataFrame to JSON
    
    json_data = data_frame.to_json(orient='records')
    
    # Initialize Cosmos DB client with managed identity
    
    cosmosdb_endpoint = "https://snmp-jpe-dev-cosmos.documents.azure.com:443/"
    
    database_name = "SNM"
    
    container_name = "AliTest"
    
    # Use DefaultAzureCredential to obtain a token credential
    
    credential = DefaultAzureCredential()
    
    client = CosmosClient(cosmosdb_endpoint, credential=credential)
    
    # Get a reference to the Cosmos DB container
    
    container = client.get_database_client(database_name).get_container_client(container_name)
    
    # Insert JSON data into Cosmos DB container
    
    for item in json.loads(json_data):
    
        container.upsert_item(item)
    
    

    Make sure your Managed Identity has been given appropriate permissions on the Cosmos DB. Typically, this involves:

    1. Going to the Azure Portal.
    2. Navigate to your Cosmos DB.
    3. In the settings pane, select "Identity & Access Management (IAM)".
    4. Add a role assignment and select the system-assigned identity of your Synapse instance and grant it the necessary permissions.

    The above code uses DefaultAzureCredential, which will seamlessly use the Managed Identity when run inside Synapse. Ensure your Synapse workspace's managed identity has been given the necessary permissions on Cosmos DB.