Synapse Spark - The access code is invalid
We have some Spark external tables set up to point to a CDM storage folder that is fed by Dynamics F&O. Most of the time, the queries to these tables work but on occasion one or more will fail with below error
Operation on target lkupLatestUpdate failed: Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot bulk load because the file "<path>/GENERALJOURNALENTRY_00005.csv" could not be opened. Operating system error code 12(The access code is invalid.). Statement ID: {D65F7B26-D4B7-4896-9260-} | Query hash: 0x | Distributed request ID: {79485A8C-CEEF-4915-9348-}. Total size of data scanned is 20 megabytes, total size of data moved is 0 megabytes, total size of data written is 0 megabytes.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot bulk load because the file "<path>/GENERALJOURNALENTRY_00005.csv" could not be opened. Operating system error code 12(The access code is invalid.). Statement ID: {D65F7B26-D4B7-4896-9260-} | Query hash: 0x | Distributed request ID: {79485A8C-CEEF-4915-9348-}. Total size of data scanned is 20 megabytes, total size of data moved is 0 megabytes, total size of data written is 0 megabytes.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4861,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=4861,State=1,Message=Cannot bulk load because the file "<path>/GENERALJOURNALENTRY_00005.csv" could not be opened. Operating system error code 12(The access code is invalid.).,},{Class=0,Number=15885,State=1,Message=Statement ID: {D65F7B26-D4B7-4896-9260-} | Query hash: 0x | Distributed request ID: {79485A8C-CEEF-4915-9348-}. Total size of data scanned is 20 megabytes, total size of data moved is 0 megabytes, total size of data written is 0 megabytes.,},],'
The F&O system writes frequently to the storage account so current thinking is that we're being refused read while this writing is happening so we tried setting transaction isolation beforehand but that hasn't resolved the issue
The fails are happening on a relatively simple query like below
set transaction isolation level READ UNCOMMITTED
select max(DataLakeModified_DateTime) as LastUpdate, count(*) as NumRows from dbo.GeneralJournalEntry
any suggestions on what we can try? or even some more detail on what is causing the error?