Least Privilege for Pipeline item to ingest data from SQL Server to Snowflake

[harrybao0901]

I am using Pipeline item in Fabric to ingest data from SQL Server to Snowflake. I already granted the role some minimal permisisons like USAGE ON WAREHOUSE, USAGE ON DATABASE, USAGE ON SCHEMA; GRANT INSERT, SELECT, TRUNCATE ON TABLE; GRANT CREATE STAGE ON SCHEMA, GRANT CREATE FILE FORMAT ON SCHEMA, GRANT CREATE TEMPORARY TABLE ON SCHEMA. But i still hit error when running Copy Data activity. What permission should I grant to the role ? Also, does Pipeline item support Incremental load when loading data from SQL Server to Snowflake ?

Thanks for your help!!! Much appreciate it.

[harrybao0901]

Hi @Olufemi7 @v-menakakota , thanks for replying.

This is the error that I mentioned, I can see the stages created in the schema in Snowflake (it showed as External Stages), but I do not have the access to it even though I have the owner role. Another strange thing is I use Role A to run the pipeline in Fabric, but the created stages is owned by role B - And when I use role B to access that external stages, it showed error with permission (same as the one below when running the pipeline in Fabric):

ErrorCode=GenericAdoNetReadError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to execute the query command during read operation.,Source=Microsoft.DataTransfer.Connectors.GenericAdoNet,''Type=Apache.Arrow.Adbc.C.CAdbcDriverImporter+ImportedAdbcException,Message=[Snowflake] 002003 (02000): SQL compilation error:
Stage 'database.schema."adf_stage_import_f466ef83b77748798685d6bc8f1d3561"' does not exist or not authorized.,Source=Apache.Arrow.Adbc,'

[Olufemi7]

Hello @harrybao0901,

From that error, this doesn’t look like a basic permission issue anymore.
If the stage (adf_stage_import_) is already getting created, then the failure is happening when Snowflake tries to actually use it. The “does not exist or not authorized” message in this case is a bit misleading, it often shows up when the stage is there, but Snowflake can’t access the external location behind it.
The role behavior you’re seeing (Role A vs Role B) also lines up with that. Fabric can run the COPY using the role defined in the connection, not necessarily the one you expect, so the stage ends up owned by a different role.
At that point, the usual cause is:
no USAGE on the storage integration, or
no access to the underlying storage (Blob / ADLS / S3)
That would explain why even the owning role can’t use the stage.
 Check:
which role is set in the Snowflake connection
whether that role has USAGE on the storage integration and whether the external storage permissions are correct
If this keeps happening, a simple workaround is to create a stage yourself, grant the right permissions to your role, and use that instead of the auto-created adf_stage_import_.
Incremental load is supported, but you have to implement it yourself (watermark or CDC).

[harrybao0901]

I tried to create a container in ADLS gen 2 to land stage their, but Fabric did not let me do that, it shows this when I do that in settings copy data activity.
Azure Data Lake Storage Gen2 is not supported in staging settings when using Snowflake sink dataset

[harrybao0901]

Hi @Olufemi7 , I noticed that both role have USAGE permission, but in schema X role A was granted earlier so it's executable (I use role B to execute pipeline in Fabric). However, in schema Y, role A was granted after role B. I dunno how exactly Snowflake would handle stuffs like this, I am trying to find their docs for explanation.

[Olufemi7]

Hello @harrybao0901,

1. ADLS Gen2 staging

This is expected behavior.

When using Snowflake as the sink, Fabric Copy activity does not support configuring ADLS Gen2 as a staging location. The staging process is handled internally by Fabric and Snowflake, so you cannot create or select an external container for this scenario.

References:
https://learn.microsoft.com/fabric/data-factory/copy-data-activity
https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake

So this is not a permission or configuration issue on your side.

2. Role grant order (Role A vs Role B)

Snowflake does not use the order of role grants to determine access.

From Snowflake access control model:
https://docs.snowflake.com/en/user-guide/security-access-control-overview

• Access is evaluated at runtime
• Only the active role matters
• Grant timing (before or after another role) has no effect on execution

So differences between schema X and schema Y are not related to when roles were granted.

3. Original issue (least privilege for Pipeline ingestion)

For Fabric Pipeline (SQL Server → Snowflake), the role needs:

• Warehouse: USAGE
• Database/Schema: USAGE
• Table: INSERT, SELECT (DELETE/TRUNCATE optional depending on load pattern)
• Schema-level: CREATE STAGE, CREATE FILE FORMAT (if Fabric creates them)
• If external staging is used: USAGE on storage integration + stage access

Incremental load is supported, but it is not automatic. You need to implement it using:

• watermark column logic, or
• CDC-based approach

Summary

• ADLS staging is not supported in this scenario (by design)
• Role grant order does not affect Snowflake execution
• Pipeline permissions depend on correct alignment of table, schema, and staging access
• Incremental load must be implemented manually

This aligns with Fabric and Snowflake behavior for this scenario.

[harrybao0901 · Accepted solution]

I found the root cause, I checked and found that one of my college set grant future ownership to role CICD, that's why new stage always goes to role CICD. Thanks for your help!!!

[v-menakakota]

Hi @harrybao0901 ,

Good to hear you were able to identify the root cause that explains the behavior you were seeing.

Appreciate you sharing the finding here, it’ll be useful for others who might run into a similar situation.

Best Regards, 
Community Support Team

[Olufemi7]

Hello @harrybao0901,

The issue is about missing permissions for how Snowflake loads data.
Fabric Copy activity typically uses Snowflake's native loading (such as COPY INTO), which loads data to a stage instead of directly to the table.
So just table level permissions just won’t do it.
Additional permissions required
Stage: USAGE, READ, WRITE
File format USAGE
Storage Integration: USAGE (when using external stage)
Reference (Snowflake) :
Snowflake Documentation
Incremental Load Yes – supported, but must be done manually (e.g. watermark or Change Data Capture, CDC).
Microsoft (Reference)
How to copy data using copy activity

 Hint
Check the error message, it will tell you if the missing permission is for stage, file format or integration.

[v-menakakota]

Hi @harrybao0901  ,
Thanks for reaching out to the Microsoft fabric community forum. 

You already granted most of the required permissions for the Pipeline Copy activity. In some cases, Fabric may also require additional permissions such as CREATE TABLE on the target schema (if auto-create is enabled) and OPERATE on the warehouse. The pipeline can also create temporary objects internally during the load process, so missing permissions on those objects may cause the activity to fail. Sharing the exact error message from the Copy activity would help identify the missing permission more accurately.

Yes, Fabric Pipeline does support incremental load from Microsoft SQL Server to Snowflake. This is usually implemented using a watermark column such as LastModifiedDate or by using SQL Server change tracking/CDC, so that only new or updated records are copied during each pipeline run.

If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

Best Regards, 
Community Support Team