BACKUP DATABASE TO URL with Azure Storage Emulator (Azurite) fails

[Cogax]

We use Azurite as an emulator for Azure Blob Storage accounts in local and testing environments. We have an issue, that BACKUP DATABASE TO URL commands will fail if we use an URL to Azurite (localhost) and not .blob.core.windows.net (see Docs):

System.Exception: Error on executing SQL Query: 'BACKUP DATABASE [RestoreTest] TO URL = 'http://127.0.0.1:20000/devstoreaccount1/baks/TestBackup.bak' WITH COMPRESSION, INIT, COPY_ONLY, FORMAT, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, STATS = 5;' 
---> Microsoft.Data.SqlClient.SqlException: The file name "http://127.0.0.1:20000/devstoreaccount1/baks/TestBackup.bak" is invalid as a backup device name for the specified device type. Reissue the BACKUP statement with a valid file name and device type.

In Issue https://github.com/Azure/Azurite/issues/1089 we evaluated the problem and it seems not related to Azurite, but to mssql-server.

Details:

C# Code:

var uri = blobClient.GenerateSasUri(BlobSasPermissions.All, DateTimeOffset.UtcNow.AddHours(2));
var queryString = uri.Query;
var sasToken = queryString.Substring(1);
var blobUri = uri.AbsoluteUri.Replace(queryString, "");

var sqlDropCredential = $@"
  IF EXISTS (SELECT * from sys.credentials WHERE name = '{blobUri}')
      DROP CREDENTIAL [{blobUri}]";

var sqlCreateCredentials = @$"
    CREATE CREDENTIAL [{blobUri}] WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '{sasToken}'";

var sqlStartBackup = @$"
      BACKUP DATABASE [{databaseName}] TO URL = '{blobUri}'
      WITH COMPRESSION, INIT, COPY_ONLY, FORMAT, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, STATS = 5;";

docker-compose.yaml

version: "2.4"
services:
  blob:
    image: mcr.microsoft.com/azure-storage/azurite:latest
    ports:
      - 20000:10000
      - 20001:10001
      - 20002:10002
    command: ["azurite", "-l", "/data", "--blobHost", "0.0.0.0","--queueHost", "0.0.0.0", "--tableHost", "0.0.0.0", "-d", "/data/debug.log"]
    volumes:
      -  /tmp/dat:/data
    healthcheck:
      test: nc -z localhost 10000 || exit -1
      interval: 5s
      timeout: 5s
      retries: 3
      start_period: 5s
  db:
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - 1433:1433
    environment:
      ACCEPT_EULA: "Y"
      SA_PASSWORD: Top-Secret
      MSSQL_PID: Developer
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -U sa -P Top-Secret || exit 1
      interval: 2s
      timeout: 2s
      retries: 10
      start_period: 15s

[Cogax]

Same problem occurs with RESTORE DATABASE FROM URL when bak file is on Azurite.

[mikeatmods]

I have the same issue. Checking the sql server logs I get error code 3041, which is something that was fixed in ms sql server 2017 CU 2. As this image is mcr.microsoft.com/mssql/server:2019-latest it should include that fix.

[Sossenbinder]

Any update here? I'm running into the same issue with no further information on how to possibly solve it. Also running an up to date image

[rafaelrodrigues3092]

+1 bumping it up. Also seeing the same issue

[potatoqualitee]

Bumping, need a solution when possible. then again, this happens with desktop version too, so its not a docker issue.

[potatoqualitee]

I have referenced these issues in the SQL Server Feedback forum and hope to hear back from the SQL team soon:

https://feedback.azure.com/d365community/idea/7a582cd4-11d2-ed11-a81c-000d3ae51e62

If not, I will ping the SQL Server Product Group to see if we can get some movement 🙏🏼

[andribergs]

Any updates on this? 🙏