How to connect Azure Data Explorer pool in Synapse to Cosmos DB

Question

Hi,

I am trying to connect Data Explorer (Synapse pool) to the Analytical Store in a Cosmos DB container. I was informed on this forum that this is indeed possible and the documentation shows how to do this. Unfortunately when I try to ingest data into ADX from Cosmos Db the option to create a connection to Cosmos DB is not present:

I would be grateful if someone could give me some instructions on this. I have already seen the MS documentation for ingesting data into Data Explorer but this is a normal Data Explorer not a Synapse Data Explorer cluster.

Regards,

Chris

Answer 1

Hello Christopher Wilkinson,

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

I understand that while connecting Azure Data Explorer pool in Synapse to Cosmos DB there are issues.

What you can do are not limited to the following:

Be sure that Synapse Link is enabled for your Cosmos DB account and containers. This is a prerequisite for connecting Cosmos DB to Synapse.

• Check that your Synapse workspace is correctly configured to connect to external data sources. Ensure that you have the necessary permissions to create connections.
• If the option to create a connection to Cosmos DB is not present, try the following troubleshooting steps:
  • Sometimes, refreshing the Synapse workspace can resolve UI issues.
  • Make sure that your Synapse workspace and related services are up to date.
  • Check that you have the necessary permissions to create connections in the Synapse workspace.
  • Make sure that your Synapse workspace and Cosmos DB account are in the same region.

Alternative Method

If the issue persists, consider using the Azure CLI or PowerShell to create the connection. This can sometimes bypass UI limitations.

1. Follow the steps in the official documentation - https://learn.microsoft.com/en-us/azure/cosmos-db/configure-synapse-link to enable Synapse Link for your Cosmos DB account and containers
2. In your Synapse workspace, go to the Data tab and select the + icon next to Data.
   1. Choose Connect to external data.
   2. Select the API you want to connect to (SQL or MongoDB) and click Continue.
   3. Provide a name for the linked service and select your Cosmos DB account and database name. - https://learn.microsoft.com/en-us/azure/synapse-analytics/quickstart-connect-synapse-link-cosmos-db
3. Once the connection is established, you can query the analytical store using serverless SQL pools in Synapse. Use the OPENROWSET function to query data from the analytical store. - 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.

---

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

Answer 2

@Christopher Wilkinson

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Issue:

How to connect Azure Data Explorer pool in Synapse to Cosmos DB.

Solution:

I opted to use a regular Data Explorer pool instead of a Synapse pool, and it has been performing exceptionally well.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.