CODEWITHSUNDEEP
azure-storageazure-synapseazure-synapse-pipeline
Ian Carrick
Ian Carrick

Reputation: 368

Azure Synapse Pipeline - Cannot bulk load Az Data-lake files into an Synapse Pool DB Table - Operating system error code 5(Access is denied.)

I have a Synapse Managed VNet and Synapse Pipelines that interact with an Azure Datalake storage account and Synapse SQL Pool databases.

Within Synapse there is an Synapse Managed Private-End-Point to the datalake that is approved.

The Synapse identity has RBAC 'Storage Blob Data Contributor' on the Storage Account.

When trying to execute the following in the Pipeline..

COPY INTO [RawStaged].[Audit_PatientAudit_E8EBCE80]
FROM 'https://storageaccountXXX.blob.core.windows.net/versioner/inbox/landing/abcdef/XXXX-E619-40CA-B45F-9C3AC0328143/20221122105610/bulk_75686_Audit_PatientAudit_20221122105610_ABCDEF-GGGH-44E8-95C5-75D70EA9A45DF.csv'
WITH (FIRSTROW = 2);

Error Message

Type=System.Data.SqlClient.SqlException,Message=Cannot bulk load because the file https://storageaccount.blob.core.windows.net/XXXX/inbox/landing/PrimaryXXXX/abc/abcdef/20221122105610/bulk_75686_Audit_PatientAudit_20221122105610_ABCDEF-GGGH-44E8-95C5-75D70EA9A45DF.csv could not be opened. Operating system error code 5(Access is denied.).

I have looked at the following but these don't resolve my issue...

Azure blob to Azure SQL Database: Cannot bulk load because the file "xxxx.csv" could not be opened. Operating system error code 5(Access is denied.)

403 Forbidden when accessing Storage Account through firewall from Azure Synapse's dedicated SQL pool

I am thinking do I need to give the 'Synapse' managed identity roles/permissions within the 'sql server pool database' the files are being loaded in to ?

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=managed-identity

Not able to access data lake file in Azure synapse dedicated pool

Any idea on how to resolve the issue?

Upvotes: 0

Views: 95

Answers (1)

Ian Carrick
Ian Carrick

Reputation: 368

Had to add '(IDENTITY = 'Managed Identity')' then the Synapse pipleline worked...

COPY INTO [RawStaged].[Audit_PatientAudit_E8EBCE80-E619-40CA-B45F-9C3AC0328143] FROM 'https://abc.blob.core.windows.net/versioner/inbox/landing/abc/abc-E619-40CA-defg-9C3AC0328143/20221122105610/bulk_75686_.csv' WITH (CREDENTIAL = (IDENTITY = 'Managed Identity'), FIRSTROW = 2);

Upvotes: 0

Related Questions