Cannot bulk load file from Azure Storage Account

Question

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.)

Answer 1

We'e had a response from Micosoft Support who have confirmed an issue their end, and they're looking ino it!

Answer 2

We have raised ticket with Microsoft to resolve this on going issue (Azure SQL database OPENROWSET function while handling JSON/XML files from blob storage account) and waiting for it.

Answer 3

We raised this with Microsoft yesterday, and this morning they confirmed it is a known issue which appears to be affecting certain UK south deployments. They’ve had numerous identical reports though don’t appear to have a timescale for a solution.

If we create a new SQL server resource in any region and create a new database for test, it works fine. It appears to affect existing servers / DBs originating from a UK south resource.

We’ve had to create an external solution for our affected customer using an Azure function to read Azure storage file data, and insert it in to an SQL table due this causing a critical production outage.

The alternative we were considering was to test and migrate the entire database to a new Azure resource, though we didn’t fully test that, opting for the above solution.

I would recommend if the workload is critical not waiting for an immediate fix as we could not receive an ETA for a fix from Microsoft support.

Answer 4

Hello Jez Walters,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you cannot bulk load file from Azure Storage Account.

Regarding the errors and your questions, the "Cannot bulk load because the file could not be opened, and Operating system error code 86 (The specified network password is not correct)," is a common error that indicates an issue with authentication or permissions when accessing the Azure Storage Account from Azure SQL Database.

To resolve this, ensure your credentials are correct and have the necessary permissions. If using a managed identity, verify it has the appropriate role assignments, such as Storage Blob Data Contributor. Also, check for time synchronization between your SQL Database server and the storage account, network configurations, encryption settings, and DNS resolution. The below is modified or revised version of your SQL code:

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MyStrongPassword';
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'MANAGED IDENTITY';
CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (TYPE = BLOB_STORAGE,
LOCATION = 'https://mystorageaccount.blob.core.windows.net',
CREDENTIAL = MyCredential);

If the error persists, reviewing detailed error logs and/or contacting Azure support may provide further insights.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Answer 5

@Jez Walters Thank you for reaching out.

As mentioned above, we confirmed that is a known issue.

Customers in UK South region are experiencing issues with loading XML/JSON files from Azure Storage Account containers using OPENROWSET in Azure SQL Databases.

Our Product group is looking for customers to validate the scenario and fix the issue.

We will provide an update once issue is mitigated.

Thank you for being patient while working on this.

Please let me know if you need additional information or concerns on this known issue identified.

Regards,

Oury