Background

Error 4860 occurs when trying to perform a bulk load operation in Azure SQL Database, and the database is unable to open or access the specified file. Bulk loading is a process where data is imported from an external file into the database. Azure SQL Database supports loading data using external file storage services like Azure Blob Storage, but errors can arise when the file path is incorrect, access permissions are insufficient, or there are network-related issues.

Summary Table

Error Explanation

The error message typically reads:

Error 4860: Cannot bulk load because the file "..." could not be opened. Operating system error code 5(Access is denied.).

This error generally occurs due to issues with:

• File location (the file path is incorrect or inaccessible).
• Insufficient permissions to access the file.
• Network-related problems between Azure SQL Database and the external file source (like Azure Blob Storage).

Common Causes

1. Incorrect File Path: The file path provided in the BULK INSERT or OPENROWSET command is incorrect or does not point to a valid location.
2. Insufficient Permissions: The database does not have the necessary permissions to access the file or blob storage account.
3. Access Key/Token Expiration: The access key or shared access signature (SAS) token used to authenticate access to Azure Blob Storage has expired or is invalid.
4. Network or Firewall Issues: Network configuration, firewalls, or virtual network settings may be blocking access to the file storage.
5. Unsupported File Types: Azure SQL Database supports specific file types for bulk loading, and using unsupported formats can cause this error.

Steps to Troubleshoot and Resolve Error Code 4860

1. Verify the File Path

The first step is to ensure that the file path you provided in the BULK INSERT or OPENROWSET statement is correct. Azure SQL Database can load data from Azure Blob Storage, so the file path must use the appropriate format, such as a URL pointing to the file in the storage account.

Example of a Correct File Path:

BULK INSERT YourTable
FROM 'https://yourstorageaccount.blob.core.windows.net/container/yourfile.csv'
WITH (DATA_SOURCE = 'your_blob_storage');

Make sure that:

• The URL is correct.
• The file name, container name, and storage account are correctly specified.

2. Ensure Access Permissions Are Correct

Azure SQL Database needs permission to access the file. If you’re using Azure Blob Storage, ensure that the necessary access controls are in place.

You can provide access to Azure Blob Storage using:

• Shared Access Signatures (SAS): A token that grants limited access to the file.
• Storage Account Keys: Full access to the entire storage account.

If you’re using a Shared Access Signature (SAS) token, make sure it’s valid and not expired.

Example Using SAS Token:

BULK INSERT YourTable
FROM 'https://yourstorageaccount.blob.core.windows.net/container/yourfile.csv?sv=2021-04-05&st=2024-01-01T12:00:00Z&se=2024-12-31T12:00:00Z&sr=b&sp=racwdl&sig=yourSASToken'
WITH (DATA_SOURCE = 'your_blob_storage');

Ensure that:

• The SAS token is still valid (check the se parameter, which defines the expiration time).
• The necessary permissions (read access in this case) are granted via the sp parameter.

3. Check Firewall and Network Settings

If the file is in an external storage service, ensure that the storage account allows access from the Azure SQL Database instance. This is typically done by configuring the firewall settings of the storage account.

Steps to Check Firewall Settings:

1. Go to Azure Portal.
2. Navigate to your Storage Account.
3. Under Networking, check the Firewall and virtual networks settings.
4. Ensure that Allow trusted Microsoft services to access this storage account is enabled.

You can also allow access to specific virtual networks or IP addresses if your Azure SQL Database resides within a virtual network (VNet).

4. Grant the Correct Permissions to the External Data Source

In Azure SQL Database, when loading data from Azure Blob Storage, you need to create an External Data Source that has access to the storage account.

Steps to Create an External Data Source:

1. Create an External Data Source that points to the Azure Blob Storage container:

CREATE EXTERNAL DATA SOURCE YourBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://yourstorageaccount.blob.core.windows.net/',
    CREDENTIAL = 'YourStorageCredential'
);

2. Use this external data source in your BULK INSERT or OPENROWSET command:

BULK INSERT YourTable
FROM 'container/yourfile.csv'
WITH (DATA_SOURCE = 'YourBlobStorage');

5. Ensure File Format Compatibility

Ensure that the file you’re trying to load is in a supported format. Commonly supported formats for bulk loading in Azure SQL Database include:

• CSV
• TXT
• JSON (for specific operations)

Ensure that your file matches the expected format for the bulk load operation.

Example for Loading a CSV File:

BULK INSERT YourTable
FROM 'https://yourstorageaccount.blob.core.windows.net/container/yourfile.csv'
WITH (
    DATA_SOURCE = 'your_blob_storage',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);

Make sure the file is well-formed and matches the expected delimiters.

6. Use Azure Data Factory or Other ETL Tools

If the issue persists and direct bulk loading continues to fail, consider using Azure Data Factory (ADF) or another Extract-Transform-Load (ETL) tool to move the data from Azure Blob Storage into the Azure SQL Database. ADF provides better control over the data loading process and can handle more complex transformations or large datasets.

Steps to Use Azure Data Factory:

1. Set up a pipeline in Azure Data Factory.
2. Define the Blob Storage as your source and the Azure SQL Database as the destination.
3. Use the Copy Activity to transfer data from the blob storage to your database.

7. Check Access Key or SAS Token Expiration

If you’re using a SAS token or storage account key, ensure that the access key or token hasn’t expired. SAS tokens are time-limited, and once they expire, Azure SQL Database will not be able to access the storage.

Steps to Renew a SAS Token:

1. Go to Azure Portal.
2. Navigate to your Storage Account.
3. Under Shared access signature, generate a new SAS token with the necessary permissions.
4. Update the token in your BULK INSERT or OPENROWSET query.

Workarounds

• Verify File Path and URL: Double-check the file URL or file path to ensure that it’s correct and accessible.
• Grant Access via SAS Token: Generate a valid SAS token and include it in the file URL to provide Azure SQL Database access to the blob storage.
• Use Azure Data Factory: If the bulk load operation continues to fail, use Azure Data Factory for more complex data loading scenarios.

Solutions

1. Correct File Path: Ensure the file path is accurate and points to an existing file in Azure Blob Storage.
2. Check Permissions: Ensure the database has read access to the file via storage account keys or SAS tokens.
3. Configure Firewall Settings: Allow the Azure SQL Database to access the storage account by configuring the firewall.
4. Use an External Data Source: Define and use an external data source for Azure Blob Storage in your BULK INSERT or OPENROWSET commands.
5. Use Azure Data Factory: As a last resort, use Azure Data Factory to move data between Azure Blob Storage and the Azure SQL Database.

Example Scenario

Suppose you’re trying to bulk load data from an Azure Blob Storage CSV file into your Azure SQL Database using the following query:

BULK INSERT SalesData
FROM 'https://myblobaccount.blob.core.windows.net/salesdata/sales.csv'
WITH (
    DATA_SOURCE = 'MyBlobStorage',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);

Step 1: You receive the following error message:

Error 4860: Cannot bulk load because the file "https://myblobaccount.blob.core.windows.net/salesdata/sales.csv" could not be opened. Operating system error code 5(Access is denied.).

Step 2: You verify that the file path is correct and that the file exists in Azure Blob Storage.

Step 3: You check that the Azure SQL Database has read access to the blob using a SAS token:

BULK INSERT SalesData
FROM 'https://myblobaccount.blob.core.windows.net/salesdata/sales.csv?sv=2024-01-01&se=2024-12-31&sp=r&sig=yourSASToken'
WITH (DATA_SOURCE = 'MyBlobStorage');

Step 4: You also ensure that the Azure Blob Storage firewall allows access from trusted Microsoft services, including Azure SQL Database.

Step 5: After confirming access permissions and correcting the file path, the bulk insert operation completes successfully.