Forum Discussion
Advocate IIIWarehouse – copy into command using an app registration cannot see file
Hi Community
I have a small problem I hope some of you guys my help with.
I have a Fabric Warehouse where I want to use the “copy into” command (COPY INTO (Transact-SQL) - Azure Synapse Analytics and Microsoft Fabric | Microsoft Learn). I have a case where a user wants to use python on local machine (this is not going to change for now) and wants to be able to make a copy into command. The app we own can make the copy into command but this new app cannot (the error in appendix says it cannot see the file). I simply cannot figure out why one app can do the copy command and the new one cannot.
We have
- Made sure the app has access to the target table in the warehouse
- Both apps are admin in workspace (one should be viewer but for this test both are admin)
- Both apps has the “GRANT ADMINISTER DATABASE BULK OPERATIONS to [app name];” sat within the warehouse
- Regarding the storage accocunt
- I made a new test storage account (gen2) and even before assigning access our own app has access (I guess it has some admin access in place that I cannot see)
- I tried granted the other app all that I could think of just to see if I could get it to work but no luck – I tried: “Storage account Contribitor”, “Storage account Data Reader”, “Storage account Data Owner”, “Owner”, “Reader”, “Contributor”
Does anyone has input / experience with using copy command and what I am missing on the one app?
Appendix 1: The copy command
(this command works for one app but not the other)
COPY INTO temp.DM_BD_PORTFOLIO_KTT FROM 'https://ktttest2.dfs.core.windows.net/temp/temp_DM_BD_PORTFOLIO_KTT.csv' WITH (FILE_TYPE='CSV',FIELDQUOTE='"',FIELDTERMINATOR='|',FIRSTROW=2, COMPRESSION='NONE');
Appendix 2: The error when trying to using the app that has not acces
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]File 'https://ktttest2.dfs.core.windows.net/temp/temp_DM_BD_PORTFOLIO_KTT.csv' cannot be opened because it does not exist or it is used by another process. (13822) (SQLExecDirectW)")
9 Replies
- burakkaragoz
Super User
Hi KimTutein ,
From what you described, it sounds like the issue might be related to Azure Data Lake Gen2 ACL permissions. Even if both apps have RBAC roles like “Storage Blob Data Reader” or “Contributor”, the COPY INTO command also requires filesystem-level ACLs.
Here’s what you can try:
- Make sure the app registration (App B) has read access at the file system level:
az storage fs access set --path /temp/temp_DM_BD_PORTFOLIO_KIT --acl "user:<app-object-id>:r-x" --account-name <storage-account-name> --auth-mode login
Double-check that the Object ID used in the ACL is correct for App B.
Confirm that both apps are using the same authentication method (Managed Identity vs. Client Secret).
If your storage account has network restrictions, make sure App B isn’t being blocked.
Let me know if you want help checking the ACLs or testing with a sample file – happy to assist!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
KimTuteinHi burakkaragoz (see my first answer atop this message stream)
I am in talking with Microsoft about this: We tried this commando: az storage fs access set --path / --acl "user:[my application app id]:r-x" --file-system temp --account-name ktttest2 --auth-mode login
That was accepted but the copy commando still does not work
- KimTutein
Hi burakkaragoz
Thank you for your input.
I have
1. double checket the object id.
2. The code for the two apps are identical - just change the application id and the secret
I am not sure how to run the command you specified - if i do it in the command prompt I get this "the following arguments are required: -f/--file-system"
I have verified that the "noneworking app" can select on tables in the warehouse. Is able to write a csv file to the conatiner. Is able to list the files in the container - however still not able to use the copy into command.
- burakkaragoz
Thanks for the extra info — that helps a lot.
If the app can list files and write to the container, then RBAC is likely fine. But as you mentioned, the issue is probably with filesystem-level ACLs, which are separate from RBAC.
About the error you got:
the following arguments are required: --/--file-system
That usually means the --file-system parameter is missing in your command. Here's a more complete version of the command you can try:
az storage fs access set \ --account-name <your-storage-account> \ --file-system <your-container-name> \ --path temp/temp_DM_BO_PORTFOLIO_KIT \ --acl "user:<app-object-id>:r-x" \ --auth-mode login
Make sure to:
- Replace <your-storage-account>, <your-container-name>, and <app-object-id> with the actual values.
- Run az login first if you're not already authenticated.
- Use --auth-mode login if you're running this as yourself, or --auth-mode key if you're using a storage key.
Let me know if that helps or if you want to test the ACLs with a quick az storage fs access show command to verify.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
- KimTutein
An update for people having the same problem. I am in dialog with Microsoft and the first answer is that copy-into might not be supported for service-principle in Fabric yet (even though I have one app working). The workaround for now will be to use shared access signatures - I am awaiting final answer from Microsoft which I am in direct dialog with now.
v-prasareCommunity Support
Hi KimTutein,
Hope your doing well.
It's great that you're already in contact with Microsoft and narrowing down the root cause. Please do keep posting any further updates here. it will definitely help others in the community who run into the similar issue.
Thanks,
Prashanth Are
MS Fabric community support
- v-prasare
Hi KimTutein,
We are following up once again regarding your query. Could you please confirm if you got any update from Microsoft?
If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. once you have confirmed, we’ll go ahead and close this thread.
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.
Thank you for your understanding and participation.
