Error using Managed Identity with OPENROWSET on Cosmos DB Analytical Store in Synapse

Question

I’m trying to query Azure Cosmos DB data using a serverless SQL pool in Synapse with OPENROWSET and Managed Identity authentication.

Previously, OPENROWSET with Managed Identity wasn’t supported, but according to the official documentation it should now be available: Query Azure Cosmos DB analytical store in serverless SQL pool using OPENROWSET.

However, I keep getting the following error:

Resolving CosmosDB path has failed with error 'Access to the database account 'mycosmosaccount' is forbidden.'

Steps I followed:

Enabled Azure Synapse Link on Cosmos DB.

Enabled Analytical Store on the required containers.

Assigned the Synapse Workspace Managed Identity to Cosmos DB with Reader role.

Since the Cosmos DB account is configured with private endpoints, I also set up network isolation for this account from the Synapse workspace as described here: Use analytical store with private endpoints.

Connected Synapse to Cosmos DB.

Ran the following query:

SELECT TOP 100 *
FROM OPENROWSET(
    'CosmosDB',
    'account=mycosmosaccount;database=test_db;region=eastus;AuthType=ManagedIdentity',
    test_container
) AS container_alias;

Using credentials (with key), the query works fine.

Using Managed Identity, it fails with the error above.

Since there are no blogs or working examples available for this Managed Identity method, I’d like to confirm:

Am I missing a configuration step (role assignment, permissions, or networking)?

• Or is this feature still not fully supported even though the documentation says it is?

Answer 1

Posting here because I was having the same issue and eventually found this: https://learn.microsoft.com/en-us/azure/cosmos-db/synapse-link-frequently-asked-questions#what-are-the-ways-to-authenticate-with-the-analytical-store-when-securing-access-over-vnets-or-private-endpoints-

You can't use Managed identity to connect from SQL Serverless to Cosmos DB analytical store when the Cosmos DB account has private endpoints.

Authentication with the analytical store must be key-based authentication when using these networking access features. Settings DisableLocalAccess on the account will prevent access to the analytical store.

Answer 2

Hello Poorna Sanjeewa,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Regarding the issue, yes, Managed Identity authentication is supported for querying Cosmos DB Analytical Store using OPENROWSET in Synapse Serverless SQL Pool. The connection string must include: AuthType=ManagedIdentity as you have specified. However, the following must be corrected:

• Use Cosmos DB RBAC for data access.
• Configure Network ACL Bypass.
• Ensure DNS zone for analytical store.
• Confirm same tenant for Synapse and Cosmos DB.

The below are the steps to resolve the issue:

Step 1:

Use Azure CLI to assign both roles:

# Control Plane Access
az role assignment create \
  --assignee "<Synapse Managed Identity GUID>" \
  --role "Cosmos DB Account Reader Role" \
  --scope "/subscriptions/<sub-id>/resourceGroups/<rg-name>/providers/Microsoft.DocumentDB/databaseAccounts/<cosmos-account-name>"
# Data Plane Access (Cosmos DB RBAC)
az cosmosdb sql role assignment create \
  --account-name "<cosmos-account-name>" \
  --resource-group "<resource-group>" \
  --scope "/" \
  --principal-id "<Synapse Managed Identity GUID>" \
  --role-definition-id "<Cosmos DB Built-in Data Reader Role ID>"

https://learn.microsoft.com/en-us/azure/cosmos-db/synapse-link

Step 2:

If using private endpoints, run:

az cosmosdb update \
  --name "<cosmos-account-name>" \
  --resource-group "<resource-group>" \
  --network-acl-bypass AzureServices \
  --network-acl-bypass-resource-ids "/subscriptions/<sub-id>/resourceGroups/<rg-name>/providers/Microsoft.Synapse/workspaces/<workspace-name>"

https://learn.microsoft.com/en-us/azure/cosmos-db/analytical-store-private-endpoints#using-synapse-serverless-sql-pools and https://learn.microsoft.com/en-us/azure/cosmos-db/analytical-store-private-endpoints

Step 3:

If using private DNS zones:

• Create zone: privatelink.analytics.cosmos.azure.com
• Link it to the Cosmos DB VNet.

https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-configure-private-endpoint and https://learn.microsoft.com/en-us/azure/private-link/private-endpoint-dns

Step 4:

Verify both Cosmos DB and Synapse workspace are under the same tenant. This is required for Managed Identity to work.

Step 5:

Run the query again.

SELECT TOP 100 *
FROM OPENROWSET(
  'CosmosDB',
  'account=<cosmos-account-name>;database=<db-name>;region=<region>;AuthType=ManagedIdentity',
  <container-name>
) AS container_alias;

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-cosmos-db-analytical-store

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 3

Hi Poorna Sanjeewa,

Based on the error message you're encountering “Access to the database account 'mycosmosxxxx' is forbidden” it seems like the Managed Identity might not have required permissions or networking configurations that are preventing access.

Here are a few steps you could check:

1. Verify Role Assignment: Ensure that the Managed Identity assigned to the Synapse workspace indeed has the appropriate role. The "Reader" role allows you to view resources but may not provide full access to query data. Consider assigning a role like "Cosmos DB Account Reader" or a customized role with read permissions.
2. Managed Identity Setup: Verify that the Managed Identity is enabled and correctly configured in both Synapse and Cosmos DB. Sometimes, reassigning the Managed Identity or reconfiguring the permissions can resolve access issues.
3. Networking: Since your Cosmos DB uses private endpoints, ensure that the networking configurations allow Synapse to access Cosmos DB. Ensure that there are no IP restrictions or firewall rules blocking the connection.
4. Database and Container Name: Double-check that the account name, database name, and container name are all specified correctly in your query.

Can you go through this once https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-cosmos-db-analytical-store?tabs=openrowset-key

please let us know after checking all the above steps.

Thanks!

Kalyani