Synapse Link for Dataverse Security

Once you have configured Synapse Link for Dataverse you are likely to want to give your data consumers access to read the Dataverse data from Synapse. This post covers how to provide users and service principals access to read your data using an Azure AD group. Synapse supports both SQL and Azure AD authentication however Microsoft recommends using Azure AD credentials.

Synapse Link for Dataverse - Data consumers

When you configure Synapse Link for a Dataverse environment it automatically creates a Data Lake database for the exported data. A Data Lake database allows regular TSQL to be used to query raw data files. The Synapse Link for Dataverse process creates external tables that point to the underlying CSV data files that have been exported from Dataverse.

If you examine the definition for the external table you will see it uses a data source definition. The data source points to the location on your storage account where the CSV files are located.

Synapse Link for Dataverse - External data source

Hopefully, this explains why when you run a SQL query against Synapse you need permission to read both the SQL objects, in the form of the external tables and the underlying blob storage. If you run a query and get an “External table ? is not accessible because content of directory cannot be listed” this indicates that while the account running the query can access the database it does not have rights to read the underlying CSV data file.

The following are the steps you should follow to secure access to your Synapse data

1. Create an Azure AD Group

Create an Azure AD security group which will be used to control access to your Synapse data. Add the user accounts and service principals to this group that requires access to your Synapse data.

2. Storage Blob Data Reader Rights

Assign Storage Blob Data Reader rights to your AD group for your Synapse storage account.

3. Create a SQL Login for your AD Group

In your Synapse master database on Synapse run the following command to create a login for your AD group.

CREATE LOGIN [SynapseReader] FROM EXTERNAL PROVIDER;

4. Grant SQL Access to your AD Group

By running the following commands you can give your AD group rights to connect to any database and to view all data in those databases.

GRANT CONNECT ANY DATABASE TO [SynapseReader]
go
GRANT SELECT ALL USER SECURABLES TO [SynapseReader]

Summary

The approach I have outlined here is relatively straightforward. If you want to provide additional accounts with access to your Synapse data all you need to do is add them to your AD group. This is much easier than having to create a SQL login for each user. If you include additional tables in your Synapse Link for Dataverse configuration your AD group members automatically have access to the new tables.