RLS in synapse server-less SQL pool using views

Question

RLS in synapse server-less SQL pool using views

Sri Challa 0

Hi,

Issue: In synapse serverless SQL pool when I try to access view data created from file stored in ADLS with AD/Entra users having "Synapse SQL pool admin" rights on the synapse workspace and no direct access to files in ADLS, I am getting error that "event.csv" cannot be opened because it does not exist or it is used by another process". How can this be resolved, so that row level security (RLS) works on top of the view without allowing ad/entra users to access adls files directly?

Below is what I have implemented

In azure synapse serverless SQL pool I have created a database "events_db"
I have two entra/AD users user1 and user2
I have created an external table "user_access" in synapse serverless sql pool with 2 columns "username" and "can_see_hidden" of type varchar(25) from user_access.csv file stored in ADLS. "can_see_hidden" can be either 0 or 1. "username" is populated with org/company email values.
I then created a view "vw_events_rls" from file events.csv stored in ADLS and joined the external table created in step-3 as below"

create view vw_events_rls
SELECT v.* FROM OPENROWSET (
   	BULK '/events.csv',
   	DATA_SOURCE = 'events_src', 
 	FORMAT = 'CSV',
    FIRSTROW = 2, 
 	FIELDTERMINATOR = ',',   
 	ROWTERMINATOR = '\n',
	PARSER_VERSION = '2.0'
)
WITH (
 event_id INT,
 event_date VARCHAR(25),
 event_name VARCHAR(25),
 event_show VARCHAR(25)
) AS v
JOIN user_access u ON u.user_name = SUSER_SNAME()
WHERE (v.event_show = 'show')  OR (v.event_show = 'hide' AND u.can_see_hidden = 1);

The views has columns "event_id", "event_date", "event_name", "event_show". If event_show = "hide" these rows should be hidden from entra user "user2" entra user "user1" must see all the rows irrespective of "event_show = show" or "event_show = hide"
Azure synapse User Managed identity has "storage data contributor" rights on ADLS tied to synapse
AD/Entra user1 and user2 has "Synapse SQL administrator" rights on the synapse workspace
AD/Entra user1 and user2 should not go and directly access the files stored in ADLS. They should only access data in the files via the view. And they indeed dont have access to ADLS.

Thanks in advance

Venkat Reddy Navari 5,840 Reputation points Microsoft External Staff Moderator

2025-03-24T14:43:21.92+00:00
@Srinivas Challagolla
The Synapse Serverless SQL Pool checks whether users have direct access to Azure Data Lake Storage (ADLS) when using OPENROWSET. Even though user1 and user2 have Synapse SQL administrator roles, those roles don’t automatically give them permission to access files in ADLS.

Use Synapse Managed Identity for ADLS Access.

Since users shouldn’t have direct access to ADLS but still need to query data through the view, the query should run using Synapse’s Managed Identity, which has the necessary storage permissions.

Enable Managed Identity in Synapse Go to your Synapse workspace in Azure. Under Identity, ensure that Managed Identity is enabled.

Grant Storage Access to the Managed Identity Go to Azure Data Lake Storage (ADLS) > Access Control (IAM). Assign the Storage Blob Data Contributor role to the Synapse Managed Identity.

Update your data source in Synapse
Run this SQL command to ensure Synapse uses its own identity to access ADLS files:
CREATE EXTERNAL DATA SOURCE events_srcLOCATION = 'https://yourstorageaccount.dfs.core.windows.net/container', CREDENTIAL = ManagedIdentity);

Modify your view to use the Managed Identity
Instead of running OPENROWSET under the current user’s permissions, configure it to use Synapse’s Managed Identity by setting the CREDENTIAL parameter.

CREATE VIEW vw_events_rls AS SELECT v.* FROM OPENROWSET ( BULK 'events.csv', DATA_SOURCE = 'events_src', FORMAT = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', PARSER_VERSION = '2.0' ) WITH ( event_id INT, event_date VARCHAR(25), event_name VARCHAR(25), event_show VARCHAR(25) ) AS v JOIN user_access u ON u.user_name = SUSER_SNAME() WHERE (v.event_show = 'show') OR (v.event_show = 'hide' AND u.can_see_hidden = 1);

I hope this information helps. Please do let us know if you have any further queries. Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Venkat Reddy Navari 5,840 Reputation points Microsoft External Staff Moderator

2025-03-25T09:53:50.69+00:00

@Srinivas Challagolla 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.

1 answer

Your answer

Venkat Reddy Navari 5,840 Reputation points Microsoft External Staff Moderator

2025-03-25T09:53:50.69+00:00

@Srinivas Challagolla 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.

Answer 1

Sri Challa 0

@Venkat Reddy Navari Thanks for your response!!! The approach you have suggested i.e. creation and use of credential = "Manged identity" did not work.

I have created DB scope credential as below:- CREATE DATABASE SCOPED CREDENTIAL [umi-credential-name] WITH IDENTITY = 'synapse-umi-managed-identity' I then used the credential within CREATE EXTERNAL DATA SOURCE as below:- CREATE EXTERNAL DATA SOURCE events_srcLOCATION = 'https://yourstorageaccount.dfs.core.windows.net/container', CREDENTIAL = umi-credential-name);

Above was throwing error for DB scope credential with message: syntax error near IDENTITY. When I did R&D on the error, I found that SAS token is supported, but use of UMI is not supported. But at org level use of "access token" and "sas token" is disabled via azure policy. So forced to use UMI only.

I have also directly specified credential of synapse workspace umi inside CREATE EXTERNAL DATA SOURCE as below, but it din't work either:-

CREATE EXTERNAL DATA SOURCE events_srcLOCATION = 'https://yourstorageaccount.dfs.core.windows.net/container',

CREDENTIAL = 'synapse-umi-managed-identity');

So the issue havent resolved yet. I know that this can be solved, if we go with synapse Dedicated SQL pool. But the cost of DWH is very high.

Venkat Reddy Navari 5,840 Reputation points Microsoft External Staff Moderator

2025-03-27T12:26:11.8833333+00:00
@Sri Challa Synapse Serverless SQL Pool does not currently support Managed Identity for external data sources, which explains the error you encountered.

You can refer to this documentation for more details: Microsoft Docs - Synapse Storage Access Control.

Here are some alternative approaches that might work

Use Synapse Pipelines to Preload Data (Cost-Effective Alternative): Since direct access to ADLS via OPENROWSET is blocked by policy, consider using Synapse Pipelines or Dataflows to load data into a staging table in Synapse Serverless.This avoids the need for SAS tokens or UMI authentication in CREATE EXTERNAL DATA SOURCE.Once loaded, you can enforce Row-Level Security (RLS) on the staging table, just like in a dedicated SQL pool.Serverless SQL queries only charge per query execution, so this remains a lower-cost option than Dedicated SQL Pool.

Confirm If Storage Account Key Can Be an Exception: Azure policy allows an exception, you might be able to use a Storage Account Key in place of UMI. However, since policies restrict SAS tokens, it’s possible that Storage Account Key authentication is also restricted.

Microsoft Entra ID Passthrough (Preview Feature in Some Regions):Check if Entra ID passthrough authentication for Synapse Serverless SQL Pool is available in your region. This feature allows Synapse to query ADLS using user-based authentication without requiring SAS tokens or storage account keys.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Venkat Reddy Navari 5,840 Reputation points Microsoft External Staff Moderator

2025-03-28T12:33:31.5466667+00:00

@Sri Challa Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

RLS in synapse server-less SQL pool using views

1 answer

Your answer