• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Blog
  • Power Platform
    • Power Apps
    • Power Automate
    • Power BI
    • Power Virtual Agents
  • Dynamics 365
  • Azure
  • About Me
Joe Gill Logo

Joe Gill

Microsoft MVP - Power Platform Consultant

Synapse Link for Dataverse Security

16th December 2022 by Joe Gill

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 Security - Joe Gill

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.

Synapse Link for Dataverse Security - Joe Gill

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 Security - Joe Gill

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.

Synapse Link for Dataverse Security - Joe Gill

2. Storage Blob Data Reader Rights

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

Synapse Link for Dataverse Security - Joe Gill

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.

Share This On Social:
  • Tweet
  • Synapse Link for Dataverse Security - Joe Gill

Filed Under: Dataverse, Power Platform

Primary Sidebar

More to See

Power Platform Requests – Base Request Capacity

17th December 2020 By Joe Gill

Power Automate Desktop

Power Automate Desktop – UI Flow

26th November 2020 By Joe Gill

Synapse Link for Dataverse Security

16th December 2022 By Joe Gill

Power Fx -Power Virtual Agents

28th November 2022 By Joe Gill

Footer

Joe Gill

Microsoft Business Applications MVP – Power Platform, Dynamics 365 and Azure.

An architect with over twenty years experience designing and developing technology solutions. Specializing in the Microsoft technology stack including Power Platform, Dynamics 365 and Azure. Microsoft MVP Profile

Connect on Social

Useful Links

  • Home
  • Blog
  • About Joe Gill
  • Power Platform
  • Dynamics 365
  • Azure

Featured Posts

Power Platform Requests – Base Request Capacity

Power Automate Desktop – UI Flow

Synapse – SQL Admin

Synapse Link for Dataverse Security

Power Fx -Power Virtual Agents

Synapse Link for Dataverse – Append-Only

© 2023 ยท Joe Gill