I am in the middle of moving a legacy database system from on-prem to Azure. The database is not hosted as an Azure database but will be hosted in an Azure based VM. This is all set up and working. We have also configured Azure file storage as the database system is a bit of a sausage machine that ingests files, converts them and then creates new files.
Ingestion is done via OPENROWSET BULK commands and output via XP_CMDSHELL and BCP (yes I know - we can't change it).
At the moment, the Azure File Storage (it is not blob storage) is mounted as a drive and SQL Server can see it. It can run XP_CMDSHELL commands to move files from the Azure storage to a local drive or vice versa and can move files on the Azure storage itself. However both the Openrowset and BCP commands fail with permissions denied. This happens whether they are run from Agent or from SSMS as a query.
Note that the BCP command when executed from the CMD window (unelevated) works without issue. It's only when executed by XP_CMDSHELL that the problem occurs.
Has anyone got any suggestions as to what the problem can be? The account running the commands is a windows account and in SQL Server is configured as a SysAdmin. It's also a member of the server administrators group.
We found the solution in the end. There were some issues with the fact that the Azure Fileshare was on a different AAD to the server. Recreating the fileshare fixed whatever gremlin was causing the issue.