Synapse link and security

Hi @MrFlinstone,
Welcome to the Microsoft Q&A Platform.

When we synch Synapse Link for Dataverse the data always comes down as files into ADLS Gen2 and is automatically surfaced through the serverless SQL pool. That is a new dataset and doesn't inherit the security of the Dataverse, so it must be secured separately.

The first place to be careful about is storage access. Anyone who has the Storage Blob Data Reader role can grab those files directly. Do not assign that role widely at the account level, restrict it to the container or folder that Synapse Link uses. You can also use ADLS ACLs if you want smaller grained control. On that, restrict the storage account itself with private endpoints as well as turning off public access. It is worthwhile turning on the defender that is specifically designed for storage so that you pick up anomalous access or bulk downloads.

Then there’s the Synapse workspace identity. As a default, this managed identity is given the right to read the lake, but I’ve also observed people giving it too much leeway. That’s normally the largest error. Try to keep the scope as small as possible. Synapse RBAC also be careful about – don’t make people too many admin role bearers and restrict who gets to provision linked services. If passthrough under the user’s own identity with Azure AD is an option, then that’s preferable so the queries still execute under the user’s identity.

On the SQL side, serverless pools are restricted. They don’t allow row-level security, column-level security, or dynamic data masking. You can brute some of this around with views or creating filtered replicas using CETAS, but that’s not actual enforcement. The correct approach is to handle serverless as a stagging layer only. If proper security is needed, then the data must be loaded into a dedicated SQL pool or Fabric SQL. That’s where the RLS, CLS and DDM come into play. You can transfer the data over with Synapse pipelines, Poly Base or CETAS. Users then only interact across the curated dedicated pool tables, while the denormalized lake and serverless façade is kept locked down.

Now consider governance. Use Purview to classify and tagging sensitive data, enforce retention so the raw data is not kept indefinite, and keep track of access with Azure Monitor, Sentinel and the storage logs. This way you’re not only trusting permissions, you’re also monitoring and tracking usage. Synapse Link always terminates in ADLS and exposes through serverless, but you don't need to provide users access there. Bolts down the raw lake and the workspace identity and use dedicated SQL where you need strong security controls.

let us know if you need more details.

Hi Swapnesh Panchal

Thank you for the comment/thoughts, can you please point me towards ready made pipelines or solutions that allow for data to be transferred onto a dedicated SQL pool or a SQL server database for querying data, having extra pipelines might mean that there is further lag in the data and its not going to be near realtime.

Secondly, is there a way to avoid setting the storage account as publicly assesible, once the storage acount is set to restrict access the spark pool jobs no longer work, and it breaks the data flow from synapse link. From a security perspective, this is a concern.

Hi @MrFlinstone,
There are two points:
first, landing the Synapse Link data into a dedicated SQL surface with very low lag
second, locking down the storage so it’s private without breaking Spark or Synapse Link.

For near-real-time into dedicated SQL (or SQL Server), treat the lake as a landing zone and micro-batch into curated tables. The most reliable pattern is event-driven: when Synapse Link drops new parquet files, the storage account raises a BlobCreated event; Event Grid triggers a Synapse pipeline; the pipeline runs a script in the dedicated SQL pool that does a COPY INTO to a small staging table and then a MERGE into your final table using Dataverse keys, honoring the delete flag. This usually gives a few minutes end-to-end. If you want something simpler, create external tables over the Link folders and run a scheduled insert or CTAS plus a MERGE; that’s higher latency but fewer moving parts. If your target is plain SQL Server, do the same pattern with ADF Copy or a small Spark job and finish with a MERGE.

For keeping the storage private without breaking anything, run Synapse in a managed VNet and create managed private endpoints from the workspace to your storage account for both dfs and blob; approve them on the storage side. If you use Key Vault or Event Grid, add private endpoints there too. Make sure private DNS zones are present and linked to the managed VNet for privatelink.dfs.core.windows.net and privatelink.blob.core.windows.net. In the storage account, disable public network access. Give the workspace managed identity the Storage Blob Data Contributor role only on the container used by Synapse Link, not at the whole account; add ADLS ACLs on folders if you want tighter scoping. If you haven’t moved Event Grid to private endpoints yet, you can temporarily allow trusted Microsoft services. After you lock things down, open Synapse Link and re-validate the connection.

If Spark or pipelines fail after hardening, check these in order: the dfs and blob private endpoints exist and show Approved; the two private DNS zones above are linked to the workspace VNet; the managed identity has the role at the correct container scope; and all linked services and scripts are using Managed Identity rather than account keys.

Do you have an example of a solution that will synch a few tables to a dedicated SQL pool or a SQL server database, which one is better (dedicated SQL pool or SQL server database ?)