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

Question

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)

Answer 1

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.

Answer 2

hi @Amira Bedhiafi ,

I am getting the same error. Could you please guide me to connect cosmosDB using azure synapse managed identity.

I see your response in this conversation that was a year old, just expecting we have some better solution after one year.

Tagging @Syed Ali Naqi please let me now if you are able to solve this issue.

Thank you in advance.