Dataverse Lake Database not visible when creating a data source in Cognitive Search

Question

Dataverse Lake Database not visible when creating a data source in Cognitive Search

Simon Jones 20

I have a Lake Database syncing data from a set of Dataverse tables via the Synapse Link. I then created a view of the data in a separate schema. I want to index this data in Cognitive Search so I created a SQL datasource pointing to the view. However, when I try connecting to my serverless SQL pool I can only see the master database and a dedicated database I created as a test. I don’t see the database listed which contains the Dataverse tables and the view I created. Is there some other step I’ve missed? I added the service principal the search service is running under to the Reader role and tried a managed identity too, but I still don’t see the database listed. Would greatly appreciate any thoughts.

Simon Jones 20 Reputation points

2023-10-05T23:26:48.2533333+00:00

Oh and I should mention I can connect to the database fine using SQL Management tools.
Anonymous

2023-10-06T12:41:54.0866667+00:00
@Simon Jones

Thanks for approaching Microsoft Q&A

It seems that the database containing the Dataverse tables and the view is not visible when creating a data source in Cognitive Search. Here are some steps that might help:

Check if the database containing the Dataverse tables and the view is associated with the correct serverless SQL pool. If not, associate it with the correct serverless SQL pool.

Ensure that the service principal or managed identity you added to the Reader role has the necessary permissions to access the database containing the Dataverse tables and the view.

Check if the firewall settings for the serverless SQL pool are configured correctly to allow access to the database containing the Dataverse tables and the view.

Try creating a new SQL datasource and see if the database containing the Dataverse tables and the view is visible. If it is visible, try using this new SQL datasource to index the data in Cognitive Search

.You might want to check these forums for additional information or reach out to Microsoft support for further assistance.In addition, it is good to know that you can connect to the database fine using SQL Management tools

for further queries please do let us know.
Simon Jones 20 Reputation points

2023-10-06T21:39:25.14+00:00

Thanks for your reply, do you have any links for steps 1, 3 you listed? It's a little unclear how I check if the dataverse tables and view is associated with the correct serverless SQL pool or what the firewall settings should be.

An update from my continued attempts to find a solution - I am able to connect to the database in the Serverless SQL pool if I copy the connection string from Built-in SQL pool in Synapse Analytics and then amend the Initial Catalog to the dataverse database name created by Synapse Link.

e.g. Server=tcp:myworkspacename-ondemand.sql.azuresynapse.net,1433;Initial Catalog=dataverse_environmentname_orgXXXXXXXX;Persist Security Info=False;User ID=sqladminuser;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

However when I do so, I'm not able to select a Managed Identity Authentication. For obvious reasons I don't really want to use a userid/pwd.
Anonymous

2023-10-11T08:20:00.4166667+00:00
@Simon Jones

Sure, here are some links to the Azure documentation that can help you with the steps I listed:

To check if the Dataverse tables and view are associated with the correct serverless SQL pool, you can follow the steps outlined in the "Connect to Synapse SQL with SQL Server Management Studio (SSMS)" article. This article explains how to connect to Synapse SQL using dedicated SQL pool and serverless SQL pool. You can use these steps to verify if the Dataverse tables and view are associated with the correct serverless SQL pool. Here's the link to the article: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/connect-overview-ssms

To reference the database and view in the SQL query, you should use the following syntax: [database_name].[schema_name].[view_name]. Here's the link to the "Create and use views in serverless SQL pool" article that explains how to create and use views in serverless SQL pool: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-views

Regarding the firewall settings, you can follow the steps outlined in the "Configure firewall settings for serverless SQL pool in Azure Synapse Analytics" article to configure the firewall settings for the serverless SQL pool. Here's the link to the article: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/configure-firewall-settings

Regarding the issue you mentioned about not being able to select Managed Identity Authentication, you can follow the steps outlined in the "Connect to Synapse SQL with SQL Server Management Studio (SSMS)" article to connect to Synapse SQL using Managed Identity Authentication. This article explains how to connect to Synapse SQL using Managed Identity Authentication for both dedicated SQL pool and serverless SQL pool. Here's the link to the article again: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/connect-overview-ssms

I hope this helps! Let us know if you have any further questions.
Anonymous

2023-10-12T06:55:43.28+00:00

@Simon Jones

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Anonymous

2023-10-16T04:28:29.88+00:00

@Simon Jones

just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.