Azure Synapse Link to Dataverse - SQL Connection not working for external data source

Question

Ok, so setup Azure Synapse Link in make.powerautomate.com for a Dynamics 365 implimentation.

During the setup, It asks you to create a sqladmin user, which I did.

I have it syncing as a test, 2 dataverse tables, which of course, go into the Azure Data Lake Gen2 as a group of CSV files.

I noticed it creates, for every table, an external data source, an external file, and an external table, which, ok, that's fine.

While logged in (in anyway) as myself (Azure AD Login), I'm able to query these tables.

If I log in using the SQLADMIN user I created (and note, you cannot create new sql users in a replicated DB), and attempt to read the external tables, I get an error that it's unable to read the files in the data lake due to security.

I guess what i'm asking is, is it possible to somehow allow that SQL Admin user that you setup, to read/view the data in the lake proprly?

The error I get is:
Cannot find the CREDENTIAL 'https://synapsedlg2storageacct.dfs.core.windows.net/dataverse-demoaltaidem-org37df66a2/product/*.csv', because it does not exist or you do not have permission.

Again, that is while logged in as the true SQL Login for the SQL Admin user.

Answer 1

Welp, I take that back. I did the shared access thing, and didn't log out of SQL and back in, I'm now able to read the table using the sql admin user.!!! Woot.

Answer 2

Hi @John Klemetsrud ,

Thank you for posting query in Microsoft Q&A Platform.

Database user can read the content of the files from the data source using external table or OPENROWSET function that references the data source.

You could try following steps.

• Create external credential object using managed identity of your workspace. Make sure your workspace managed identity has Storage blob Data contributor role.
• Create external datasource object on top of that credential object.
• Now create external table with above objects and then try to query.

Kindly check below link to know Access a data source using credentials.
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=user-identity#access-a-data-source-using-credentials

Click here to know about creating external data source.
Click here to know about External tables in Synapse.
Click here to know about create login and user for serverless SQL pool in Synapse.

please note, User must have SELECT permission on an external table to read the data.

Hope this helps. Please let us know how it goes.

---------------

Please consider hitting Accept Answer. Accepted answers helps community as well.

Answer 3

Hi @John Klemetsrud ,

In below example I created a External table with external data source having external database scope credential object created using Managed Identity of workspace.

And then created a SQL Login & User for my Synapse SQL and then tried to queried my External table. All works fine. If you make sure below steps are there in your case too then hopefully you will not see error.

Step1: Create External Table.

Step2: SQL login and user created.

Step3: Connected SSMS with above created Login and then tried to queried External table.

So, point to note is When ever we try to query external tables from client tools. The credential object of external data source is going to read data form underlying file from storage. If that credential object Identity dont have permission on that file then we end up with error. Hence we need to make sure our synapse workspace MSI which we used in external credential object should have Storage blob data contributor role on storage.

Hope this helps. Please let us know if any further queries.

------------

Please consider hitting Accept Answer. Accepted answers helps community as well.