Forum Discussion
Least Privilege for Pipeline item to ingest data from SQL Server to Snowflake
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.
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!!!
9 Replies
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 TeamHello 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 activityHint
Check the error message, it will tell you if the missing permission is for stage, file format or integration.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.
