Cannot Backup SQL Database to Azure Blob Storage with PowerShell or T-SQL

Question

I believe I am following Microsoft's documentation for backing up SQL databases to Azure Blob Storage; however, I keep hitting the same error no matter what I try.

For example, the following code creates a SQL credential and attempts to backup a database.

Upon running it, the error states that I cannot use WITH CREDENTIAL and SAS, but Microsoft demonstrates using both directly in their documentation (https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017#Examples)!

declare @databaseName varchar(50)
declare @destinationAzureStorageContainerPath varchar(256)
declare @destinationBlobPath varchar(256)
declare @timestampUtc as nvarchar(30)

select @timestampUtc = replace(convert(nvarchar(30), getutcdate(), 126), ':', '_');
set @databaseName = 'DWConfiguration'
set @destinationAzureStorageContainerPath = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/'
SET @destinationBlobPath = @destinationAzureStorageContainerPath + @databaseName + '_' + @timestampUtc + '.BAK'

if not exists
(select * from sys.credentials   
where name = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/')  
create CREDENTIAL [https://mystorageaccount.blob.core.windows.net/mystoragecontainer/] 
  with IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = 'sv... this is my token ...';

backup DATABASE @databaseName   
to URL = @destinationBlobPath
with CREDENTIAL = 'https://mystorageaccount.blob.core.windows.net/mystoragecontainer/'
,COMPRESSION  
,STATS = 5

The error:

Msg 3225, Level 16, State 1, Line 28 Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature. Msg 3013, Level 16, State 1, Line 28 BACKUP DATABASE is terminating abnormally.

As an alternative approach, I decided to use PowerShell.

Backup-SqlDatabase -ServerInstance "myserver" -Database "DWConfiguration" -BackupFile "https://mystorageaccount.blob.core.windows.net/mystoragecontainer/mydatabase_2019-01-04T20_01_03.127.bak" -SqlCredential "https://mystorageaccount.blob.core.windows.net/mystoragecontainer/"

As you can see, it results in the same annoying error!

Backup-SqlDatabase : System.Data.SqlClient.SqlError: Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.

On the blob itself, I have set "Private (no anonymous access)." I only want authenticated requests to be able to access the blob. Could this be the problem? If so, why doesn't WITH CREDENTIAL resolve this?

How can I simply save a backup of my SQL database to my Azure Storage Account?

Ref. https://blog.sqlauthority.com/2018/07/17/sql-server-backup-to-url-script-to-generate-credential-and-backup-using-shared-access-signature-sas/

Did you read this blog post? blogs.technet.microsoft.com/msftpietervanhove/2016/02/05/… — Mötz
Yes, it seems to say that, "WITH CREDENTIAL is deprecated" as of SQL 2016. However, when I try creating the backup without using "WITH CREDENTIAL," it fails to authenticate. It seems like it is required but it's not letting me use it! — user1477388
This example appears to omit the WITH CREDENTIAL altogether. docs.microsoft.com/en-us/sql/t-sql/statements/…. What error message are you getting when you omit it? — Nick.McDermaid
The error I get when I don't include the WITH CREDENTIAL is: Cannot open backup device 'mystorageaccount.blob.core.windows.net/mystoragecontainer/…'. Operating system error 50(The request is not supported.). Msg 3013, Level 16, State 1, Line 28 BACKUP DATABASE is terminating abnormally." — user1477388

Ivan Yang Ivan Yang · Accepted Answer · 2019-01-08T09:33:50

I think you made a mistake in your sql scripts.

You created the credentials "Using Shared Access Signature", but when backup you used "To URL using storage account identity and access key" which is not matched the sas you created before.

I test it at my side, and works fine(create credentials "Using Shared Access Signature", and backup using "To URL using Shared Access Signature").

IF NOT EXISTS (SELECT * FROM sys.credentials   
               WHERE name = 'https://xxx.blob.core.windows.net/container')  
CREATE CREDENTIAL [https://xxx.blob.core.windows.net/container] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = 'sv=xxx';


BACKUP DATABASE MyTest   
TO URL = 'https://xxx.blob.core.windows.net/container/123.bak'  
GO

Test result as below:

Cannot Backup SQL Database to Azure Blob Storage with PowerShell or T-SQL

1 Answers