Home / server / Questions / 795767
In Process
Scott Salyer
Asked: 2016-08-10 05:51:20 +0800 CST

SQL Server 2016 Web Backup to URL

  • 772

I recently stood up a new Windows Server 2012 R2 instance with SQL Server 2016 Web Edition and am trying to setup automated backups to Azure, but not having any luck. I created the storage account on Azure first, then created the Shared Access Signature (SAS) and that's where I start to get lost. From here, I've tried:

  • Creating local credential using this script:

    CREATE CREDENTIAL [BlobUrlWithContainerName]
WITH IDENTITY = '[FriendlyName]', 
SECRET = '[SAS Token]'`

    No errors were reported with the above script, however issuing a BACKUP DATABASE command to a URL results in a not supported command (full error is: Cannot open backup device 'https://[storagename].blob.core.windows.net/[containername]/AdventureWorks2016.bak'. Operating system error 50(The request is not supported.).

  • I then tried going through SSMS on an individual database backup option, with right-clicking on a database, selecting Tasks ==> Back Up.., changing Back Up To to a URL and clicking Add. When I do that, I get prompted with this:

    SQL Backup Prompt

    No registered containers exist (nor can I find any details on how to register one), so I click New Container which gives me a Connect to a Microsoft Subscription dialog. I signed in with my Azure account, see two subscriptions (Free Trial, which is expired, and Pay As You Go, which is the correct one), select Pay As You Go, and then when I go to select my storage account, the dialog closes and I get a "Index was out of range" error, which implies it can't find any containers. Here are the relevant screenshots:

    Microsoft Subscription Prompt

    Index out of range error

I'm at a loss for where to go from here. Any ideas?

backup azure sql-server-2016

3 Answers

  1. Two backup options: BACKUP DB TO URL WITH CREDENTIAL - makes blob, requires credential to use token, not SAS, when creating [Cannot stripe backups across multiple files]

    BACKUP DB TO URL [sans credential] - makes blocks, requires credential name to match the url [storage account + container], SAS in secret, [CAN stripe backups across multiple files]

    Removing the leading '?' is hardly ever mentioned and stuffs everyone around.

    Operating System error 50 (for me on Windows Server 2012 R2; sql server SQL Server 2016) is possible even when everything is correct: I have a job which backups dbs to Azure striping across files - failed 1 database on Friday, succeeded same db without changing anything on Saturday... just to add fuel to the fire.

    It may be your authentication which is causing your failure to locate containers. 

       USE master  
   CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] -- this name must match the container path, start with https and must not contain a trailing forward slash.  
  WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.   
     , SECRET = 'sharedaccesssignature' –- this is the shared access signature token   
  GO

    Source: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql.

    /* Leaving this as historic context */ REFER "MSDN: SQL Server Backup to URL Best Practices and Troubleshooting"

    I found your post because I too am struck with the 50 error, even after reading "MSDN: Tutorial: Using the Microsoft Azure Blob storage service with SQL Server 2016 databases"

    I think you should access http://portal.azure.com and then login to your Azure subscription. You change "directories" from Trial to Pay-as-you-go in the top right of the screen...

    BTW I think you may need Shared Access Signature in SQL 2016 because when I copy a SQL 2012 script {Backup DB X using CREDENTIAL Y} to 2016 instance, it fails (FORBIDDEN) DESPITE them saying you can use credentials...

    Also, Operating System error 50 (The request is not supported), leads me to think server 2012 r2 may be the error... testing currently on my laptop (windows 8) yields same error.

    Good luck.

    • 2

  2. For me, everything was correct EXCEPT for the SAS token. The SAS token should NOT begin with the '?' question mark character.

    Example SAS Token (Credential Secret/Password)

    sv=2017-01-02&ss=abcd&srt=abc&sp=rwdlacup&se=2027-09-11T05:00:00Z&st=2017-09-11T04:55:00Z&spr=https&sig=randomcharactersandnumbers
    • 1

  3. I had a similar issue (Operating System error 50 (The request is not supported)) and for me it helped to use storage account identity and access key instead of the SAS-key. Not optimal in all cases, but solved my case.

    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url#credential

    Creating the credentials:

    IF NOT EXISTS  
(SELECT * FROM sys.credentials   
WHERE name = '<mycredentialname>')  
CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>'  
,SECRET = '<mystorageaccountaccesskey>';

    Using the credentials:

    BACKUP DATABASE AdventureWorks2016  
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak'   
      WITH CREDENTIAL = '<mycredentialname>'   
     ,COMPRESSION  
     ,STATS = 5;  
GO
    • 0