Cannot bulk load file from Azure Storage Account

Jez Walters 70

As of the early hours of this morning, I can no longer load files from Azure Storage Account containers using the following code in either of the Azure SQL Databases I've created.

I may have missed something, but I don't believe anything has changed to provoke this. The error I'm getting is as follows:

Cannot bulk load because the file <BLOB storage file URL> could not be opened. Operating system error code 86(The specified network password is not correct.)

Jez Walters 70 Reputation points

2024-11-19T20:08:19.9266667+00:00

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MyStrongPassword';
Jez Walters 70 Reputation points

2024-11-19T20:08:40.3366667+00:00

CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'MANAGED IDENTIY';
Jez Walters 70 Reputation points

2024-11-19T20:09:04.6433333+00:00

CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (TYPE = BLOB_STORAGE,
LOCATION = 'https://mystorageaccount.blob.core.windows.net',
CREDENTIAL = MyCredential);
Jez Walters 70 Reputation points

2024-11-19T20:12:33.6533333+00:00

I'm really struggling to even post my question here, as I keep being told I'm not authorized to respond
Jez Walters 70 Reputation points

2024-11-19T20:13:56.3333333+00:00
Jez Walters 70 Reputation points

2024-11-19T20:35:13.2433333+00:00

The storage account was created as follows:

BASICS SETTINGS
Primary service: Azure Blob Storage or ADLS Gen 2
Performance: Standard
Reduncancy: LRS

ADVANCED SETTINGS
Require secure transfer for REST API operations: Enabled
Allow enabling anonymous access on individual containers: Disabled
Enable storage account key access: Disabled
Default to Microsoft Entra authorization in the Azure Portal: Enabled
Minimum TLS version: Version 1.2
Permitted scope for copy operations: From any storage account
Enable hierarchical namespace: Enabled
Enable SFTP: Disabled
Enable network file system v2: Disabled
Allow cross-tenant replication: Disabled
Access tier: Hot

NETWORKING SETTINGS
Network access: Enable public access from all networks
Routing preference: Microsoft network routing

The SQL Server is configured with System assigned managed identity "On", and the storage account has the "Storage Blob Data Contributor" role assigned to this managed identity.
Oury Ba-MSFT 20,106 Reputation points Microsoft Employee

2024-11-19T21:34:28.89+00:00

@Jez Walters Thank you for reaching out ans sorry about the issue you are facing.

It looks like you've configured your storage account and SQL Server correctly, but you're still encountering the error.

Is the credential using a Shared Access Signature? Could you please verify if it is not expired.

Regards,

Oury
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Jez Walters 70 Reputation points

2024-11-19T23:26:53.94+00:00

Hi Oury. I'm trying to use a System Assigned Managed Identity, as you should see in the IDENTITY setting of the CREATE DATABASE SCOPED CREDENTIAL statement. Regards Jez
Sunley, Nick 0 Reputation points

2024-11-20T10:27:58.8433333+00:00

Exactly the same has happened for us at the same time. We are accessing the Blob Storage using a SAS key. We upload the file using powershell and then attempt to import the data by calling the stored procedure. The upload works fine and so we know there is nothing wrong with the SAS Key. It has not expired either. We are using code supplied by Microsoft as well!
Leon Armston 6 Reputation points MVP

2024-11-20T10:53:03.2533333+00:00
@Sunley, Nick @Oury Ba-MSFT @Jez Walters

I'm getting exactly the same since yesterday when it stopped working and nothing changed.

Msg 4861, Level 16, State 1, Procedure dbo.ImportXXContacts, Line 20 [Batch Start Line 0] Cannot bulk load because the file "https://xxxxxx.blob.core.windows.net/blob-storage-container/xxxx-contacts.json" could not be opened. Operating system error code 86(The specified network password is not correct.).

We access Azure Blob Storage through a SQL Stored Procedure using SAS key then use OPENROWSET to process blobs. I have even renewed the SAS key this morning and no joy!

Looks like it started failing in the early hours of the 19th
David Gracie 10 Reputation points

2024-11-20T10:55:05.8333333+00:00

We have the same issue and as a work around changed the import to use BULK INSERT as opposed to OPENROWSET. Hopefully that helps others in the short term until a proper solution is found.
Iain Lennox 0 Reputation points

2024-11-20T12:14:01.87+00:00

Same issue here, using Azure Data Factory that calls SQL Stored Proc to get files from BLOB using SAS, any update on this issue?
Jez Walters 70 Reputation points

2024-11-20T13:18:25.3266667+00:00

@David Gracie Many thanks for your suggestion which works for CSV files, although OPENROWSET also works for CSV files - at least for now!
Harison Jose Vadakkepeedika 10 Reputation points

2024-11-20T13:58:11.26+00:00

All our API JSON response related data load is affected and serious data refresh issues while using SQL Stored Proc to open files from BLOB either using "SHARED ACCESS SIGNATURE" token or "Managed Identity". Only "BULK INSERT" is working for now, but not good with "OPENROWSET" for JSON files. Any update on this issue please ?
Jez Walters 70 Reputation points

2024-11-20T15:12:05.4833333+00:00

As far as I can tell, this problem appears to only affect using OPENROWSET to read XML & JSON files. OPENROWSET can still successfully be used to read CSV files, as long as the FORMAT & FORMATFILE options are specified (though there may be other combinations of options that work/don't work).

Because I can successfully use OPENROWSET to read CSV files, this means that I have my permissions configured correctly - otherwise nothing would ever work of course.
David Gracie 10 Reputation points

2024-11-20T16:07:21.8733333+00:00

@Jez Walters Yeah, our work around was for importing XML files but it would work for JSON as well. We used BULK INSERT to insert the xml file content into a temp table with one field of type XML (a varchar would have worked and then convert on the fly).

CREATE TABLE #tmp (

XmlData XML NULL

)

BULK INSERT #tmp FROM 'FILE PATH 'WITH (

DATA_SOURCE = 'EXTERNAL DATA SOURCE',

ROWTERMINATOR = ''

)

At least it allows us to carry on, but for me this points at an underlying change that MS need to fix.
Jez Walters 70 Reputation points

2024-11-20T16:46:39.8733333+00:00

This problem is affecting Azure SQL Databases in the UK South region, but not in the North Europe region. I don't know what other regions are/aren't affected but it certainly seems to be region specific.
Jez Walters 70 Reputation points

2024-11-20T16:48:51.2733333+00:00

@David Gracie Thanks for the advice, though it would take a lot of effort for us to change loading strategy for all our various feeds. Hopefully the UK South region (and any others that are affected) will be fixed soon.
Jez Walters 70 Reputation points

2024-11-20T16:55:18.15+00:00

@David Gracie Thanks again for helping!
David Gracie 10 Reputation points

2024-11-20T16:55:36.2733333+00:00

@Jez Walters UKW is not affected either.
Jez Walters 70 Reputation points

2024-11-20T16:56:37.4166667+00:00

Unfortunately we can't create resources in the UK West region with the subscription we're using.
Jo Hacker 0 Reputation points

2024-11-21T09:06:44.53+00:00

Just commenting to report that we are also experiencing this issue in UK south and anxious for a resolution.
Bryan Parsons 5 Reputation points

2024-11-21T11:41:24.5233333+00:00

We are seeing the same issue; anyone had any luck with Microsoft Support?
Kev 21 Reputation points

2024-11-21T11:47:39.06+00:00

We also have the exact same issue.

It first manifested itself in the early hours of Tues 19/11/2024.

A manual re-run around 10am went through successfully as it always had done, and it's been fine since. We made no changes before or after that event.

Subsequently, in the early hours of Weds 20/11/2024 on a different tenancy within UK South we started experiencing the same failure, and subseqent re-runs continue to fail in this tenancy, even now.

Feels like MS have been rolling something out across UK South.

Hi @Jez Walters btw!
Kev 21 Reputation points

2024-11-21T11:52:09.0633333+00:00

We also have the exact same issue.

It first manifested itself in the early hours of Tues 19/11/2024.

A manual re-run around 10am went through successfully as it always had done, and it's been fine since. We made no changes before or after that event.

Subsequently, in the early hours of Weds 20/11/2024 on a different tenancy within UK South we started experiencing the same failure, and subseqent re-runs continue to fail in this tenancy, even now.

Feels like MS have been rolling something out across UK South.

Hi @Jez Walters btw!
SJC 5 Reputation points

2024-11-21T22:07:44.61+00:00

Also have this problem - UK South.

A select using OpenRowSet from blob storage failed on Tuesday (19th) morning with the incorrect password error.

But the same code ran later on Tuesday without a problem, and ran again on Wednesday - also without issue.

But has failed consistently today (Thu).
Kev 21 Reputation points

2024-11-22T09:04:32.3066667+00:00

The problem has spread. Another one of our tenancies has now been affected...
SJC 5 Reputation points

2024-11-24T00:11:48.06+00:00

Now working properly :) !!!
Leon Armston 6 Reputation points MVP

2024-11-24T20:04:57.8666667+00:00

Not working for me - still the same error! I hope we can get a rapid fix this week!

Msg 4861, Level 16, State 1, Procedure dbo.StoredProc, Line 25 [Batch Start Line 0]

Cannot bulk load because the file "https://xxxxxxxx.blob.core.windows.net/blob-storage-container/xxxx-xxx.json" could not be opened. Operating system error code 86(The specified network password is not correct.).

Completion time: 2024-11-24T20:01:46.3759799+00:00
Oury Ba-MSFT 20,106 Reputation points Microsoft Employee

2024-11-25T18:34:47.01+00:00

Kev,SJC,Leon Armston Please open a support ticket if you are still facing the same issue so we can help mitigate this issue.

Regards,

Oury
HouMez 371 Reputation points

2025-01-07T08:41:45.15+00:00

Hello,
We are facing this issue in the West Europe region.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

6 answers

Harison Jose Vadakkepeedika 10 Reputation points

2024-11-25T18:14:06.51+00:00

I have received an update from Microsoft as stated below and the ongoing issue has been fixed. I have tested it and currently OPENROWSET function is working fine.

"Preliminary update on the RCA (Root Cause Analysis) regarding the current issue.

RCA:

Starting 11/19/2024, customers running any Azure SQL Database in WestCentralUS, EastAsia, UKSouth regions and a few select servers in other regions, may started experiencing an error in a query trying to access remote file via OPENROWSET, specifically Error 4861 “Cannot bulk load because the file could not be opened. Operating system error code 86(The specified network password is not correct.)”. The product team confirmed that this issue happened because of deployment delivering updates to corresponding databases. The issue was caused by a configuration change which was mitigated in the mentioned regions/servers by 11/25/2024 4:00 AM UTC"
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Cannot bulk load file from Azure Storage Account

6 answers

Your answer