“Error while decoding the storage key” when backing up SQL Server database to Azure Blob using Shared Access Signature

Question

Objective: I am trying to backup our on premises SSMS databases to our Azure Cloud storage.

I have a blob container https://zutosqlbackupslive.blob.core.windows.net/dw1/ This is set to private.

I have created a Shared Access Signature with full permissions (all resource types allowed, all permissions allowed), with a start date of yesterday, and an expiry date of next year.

It is set to allo HTTPS and HTTP, using key 1.

Using this SAS, I am able to access a test image in the blob by adding it to the end of the URL for the image file. So my SAS works.

The SQL script

CREATE CREDENTIAL [AzureDWBackup]
        WITH IDENTITY = 'Shared Access Signature',
        SECRET = '<<SAS key>>' -- this the key taken from the highlighted section of the screenshot
GO

BACKUP DATABASE Maintenance
TO URL = 'https://zutosqlbackupslive.blob.core.windows.net/dw1/Maintenance_DW1_FullBackup_20062017T1518.bak'
WITH INIT, NOFORMAT, NAME = N'Maintenance_DW1_FullBackup_20062017T1518', STATS = 10
, CREDENTIAL = N'AzureDWBackup'

The resulting error is:

Msg 3298, Level 16, State 2, Line 11
Backup/Restore to URL device error: Error while decoding the storage key.

Msg 3013, Level 16, State 1, Line 11
BACKUP DATABASE is terminating abnormally.

Can anybody see from that what is wrong, or suggest what to try next.

Answer 1

According to your description, I followed here for creating a SQL Credential with the SAS URL. Here is my test, you could refer to it:

Create a SQL Credential for the SAS URL and backup db

--Create a SQL Credential for the SAS URL
CREATE CREDENTIAL [https://{storage-account-name}.blob.core.windows.net/dbbackups] WITH IDENTITY = 'Shared Access Signature'  
,SECRET = 'st=2017-04-22T03%3A55%3A00Z&se=2017-07-29T03%3A55%3A00Z&sp=rwdl&sv=2015-12-11&sr=c&sig=Txv%2FWrStGYb6ax1dzb47WbBjO7iNCbwohl02jodhuNw%3D'

--backup the database
BACKUP DATABASE [{your-database-name}]
TO URL = 'https://{storage-account-name}.blob.core.windows.net/dbbackups/brucedb_20160623114800.bak'

Note: You need to remove the first ? from your SAS token.

Result:

Additionally, for creating a SQL Server Credential with storage account name and account access key, you could refer to Lesson 2: Create a SQL Server Credential and Lesson 3: Write a Full Database Backup to the Windows Azure Blob Storage Service.

Answer 2

Short answers is use an Access Key instead of SAS if you are using SQL Server 2014 or earlier.

I was getting the same error using a SAS (Shared Access Signature.) My understanding was that using a SAS was the preferred method and that this would work even on older SQL Server versions. SAS did not work on SQL Server 2014, I had to use the Access Key method and then I no longer received this error message.

I also modified the script so when I run this again, I can update the key if it ever changes. (This makes more sense for a SAS because they expire)

IF EXISTS  
(SELECT * FROM sys.credentials   
WHERE [name] = '<mycredentialname>')  
BEGIN
  CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>'  
,SECRET = '<Access Key>';
END
ELSE
BEGIN
CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>'  
,SECRET = '<Access Key>';
END

BACKUP DATABASE <database name>
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/<mybackupfilename>'
WITH CREDENTIAL = '<mycredentialname>'   
     ,COMPRESSION  
     ,STATS = 1
     ,CHECKSUM ;
GO 

Answer 3

This - "CREATE CREDENTIAL [AzureDWBackup]" needs to be the access url when creating access to a shared access signature type connection.

You have used a cust0m name instead of

CREATE CREDENTIAL [https://zutosqlbackupslive.blob.core.windows.net/dw1]

Example Template:

CREATE CREDENTIAL [https://<storage_account_name>.blob.core.windows.net/<container>] 

   WITH IDENTITY = 'SHARED ACCESS SIGNATURE' 

   , SECRET = '<shared_access_signature_key_with_removed_first_?_symbol>'