Forum Discussion
Least Privilege for Pipeline item to ingest data from SQL Server to Snowflake
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!!!
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,'
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).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.
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-snowflakeSo 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.
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
