Understanding % Characters in Azure Blob File Names Using SQL OPENROWSET (BULK)

Why the % character is different

In URL percent-encoding, the % character starts an encoded sequence. For example:

%20 represents a space.
%25 represents a literal % character.

Because of this, file names that contain a literal % can behave differently from file names containing characters such as #, &, +, ;, =, or @, depending on how the path is processed before blob lookup.

Observed symptom

The customer reported an import failure for a file name similar to:

Company Data - Company Data - éêçëЮй_%25$£#.csv

The error message started at the % sequence instead of showing the full file name:

Invalid format specification: '%25$#.45752234198410731.csv.processed' cannot be opened

This was a useful clue because the reported path appeared to begin at the first % sequence, while the preceding portion of the file name was not present in the error text.

Test setup

The examples below use an external data source pointing to Azure Blob Storage. Replace all placeholders with values from the test environment. Sensitive values such as storage account names, container names, credential names, and SAS tokens should be masked before sharing.

CREATE DATABASE SCOPED CREDENTIAL []
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '';

CREATE EXTERNAL DATA SOURCE [MyBlobStorageSource]
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://.blob.core.windows.net/',
    CREDENTIAL = []
);

Reproduction results

Scenario	Blob name / character	OPENROWSET path	Result	Observation
Space in file name	`Test File.pdf`	`BULK 'Test File.pdf'`	Succeeded	Spaces alone did not reproduce the failure.
Literal `%` in file name	`Test%File.pdf`	`BULK 'Test%File.pdf'`	Failed	The file could not be opened when `%` was passed literally.
Literal `%` encoded as `%25`	`Test%File.pdf`	`BULK 'Test%25File.pdf'`	Succeeded	Encoding `%` as `%25` allowed the same blob to be accessed.
Literal string `%25` in file name	`Test%25File.pdf`	`BULK 'Test%2525File.pdf'`	Succeeded	The observed result is consistent with one percent-decoding pass during path resolution.
Other special characters	`#`, `&`, `+`, `;`, `=`, `@`	Tested individually	Succeeded	These characters did not reproduce the same behavior in the tests performed.

Commands used for testing

1. Space in the file name

INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;

2. Literal % in the file name

INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test%File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;

Observed error:

Msg 13822, Level 16, State 1
File 'Test%File.pdf' cannot be opened because it does not exist or it is used by another process.

3. URL-encoded % character

INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test%25File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;

4. Literal %25 in the blob name

INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test%2525File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;

Interpretation

The tests suggest that the path provided to OPENROWSET (BULK) is processed in a way that is consistent with URL-style percent-encoding rules.

A literal % character in a blob name may need to be referenced as %25 in the BULK path.
If the actual blob name contains the literal characters %25, the BULK path may need to use %2525 so that the observed path-resolution behavior resolves it back to %25.
Spaces and the tested characters #, &, +, ;, =, and @ did not reproduce the same behavior in these tests.
The customer error beginning at the %25 sequence is consistent with the file name being interpreted or transformed when the % sequence is encountered.

Practical guidance

Recommended handling for `%` characters in blob names
Main recommendation Avoid passing blob names containing a literal % directly to OPENROWSET (BULK).
When the blob name contains `%` URL-encode the character before generating the BULK path. For example, test referencing a literal % as %25.
When the blob name contains literal `%25` Test whether the path needs to use %2525. In the observed tests, this was consistent with one percent-decoding pass during path resolution.
Application or stored procedure checks Confirm the import process is not decoding, encoding, or re-encoding the file name multiple times before invoking OPENROWSET.

Optional PowerShell test using Invoke-Sqlcmd

If testing outside SSMS, Invoke-Sqlcmd can be used with an Entra ID access token. The SAS token is not needed in the command if the database scoped credential already exists in SQL.

Connect-AzAccount

$query = @"
INSERT INTO testfiles(pdfData)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'Test%25File.pdf',
    DATA_SOURCE = 'MyBlobStorageSource',
    SINGLE_BLOB
) AS PdfFile;
"@

Invoke-Sqlcmd `
    -ServerInstance ',3342' `
    -Database '' `
    -AccessToken (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token `
    -Query $query `
    -Verbose `
    -ErrorAction Stop

Conclusion

The controlled tests indicate that the import behavior is related to how % is handled in the file path supplied to SQL OPENROWSET (BULK). The observed results are consistent with percent-decoding during path resolution: %25 behaves like a literal %, and %2525 behaves like a literal %25.

Based on these tests, this should not be described as a general limitation with spaces, Unicode characters, or common special characters in blob names. The evidence points specifically to handling of the % character and percent-encoded sequences in the path passed to OPENROWSET (BULK).

This article was originally published by Microsoft's Azure Database Support Blog. You can find the original article here.

Tags: Active Directory, Big Data, Storage