First published on MSDN on Nov 02, 2018
We have been seeing a trend in customer support cases with the following error
When attempting to backup a database in an Azure SQL Managed Instance to Azure Blob Storage you may run into the following error.
“Msg 3201, LEVEL 16,STATE 1,Line 5 Cannot
OPEN BACKUP device '
https://storageacct.blob.core.windows.net/container/db.bak'. Operating system error 50 (The request IS NOT supported.)”
Note that this is not specific to Managed Instance (MI); rather it indicates an issue with accessing the blob storage.
When you are having issues connecting to your blob storage container to perform a backup, it’s due to the Share Access Token (SAS) token being outdated. Listed below are the major components and their function.
1. Storage Account - The storage account is the starting point for all storage services.
2. Container – The container provides a grouping of a set of blobs and can store an unlimited number of blobs. To write a SQL Server backup to the Microsoft Azure Blob storage service, you must have at least the root container created. You can generate a Shared Access Signature token on a container and grant access to objects on a specific container only.
3. Blob - A file of any type and size. There are two types of blobs that can be stored in the Microsoft Azure Blob storage service: block and page blobs. SQL Server backup can use either blob type depending upon the Transact-SQL syntax used. Blobs are addressable using the following URL format: https://<storage account>.blob.core.windows.net/<container>/<blob>
4. URL - URL specifies a Uniform Resource Identifier (URI) to a unique backup file. The URL is used to provide the location and name of the SQL Server backup file. The URL must point to an actual blob, not just a container. Example: http[s]://ACCOUNTNAME.blob.core.windows.net/<CONTAINER>/<FILENAME.bak>
5. Credential - An SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. Here, SQL Server backup and restore processes use credential to authenticate to the Microsoft Azure Blob storage service and its container and blob objects. The Credential stores either the name of the storage account and the storage account access key values or container URL and its Shared Access Signature token. Once the credential is created, the syntax of the BACKUP/RESTORE statements determines the type of blob and the credential required.
The storage access policy can be created via Azure PowerShell, Azure Storage SDK, Azure Rest API, or a third-party application. I would like to focus on using the Portal and SSMS to drop the problematic SQL scoped credential, create new credential, create a new SAS token, and then using that SAS Token to create a new credential.
First, I always like to drop the credential and just create a new one from scratch with a new SAS token. This will eliminate time and effort when troubleshooting the current connection issue.
1. If for some reason SAS credential token has expired, check the start and end date as shown in the screenshot below. One of the biggest mistakes I’ve seen, is that the start date will have the “Now” date/time and when coping the token string, the “?” symbol is included. It’s best to set this to 15 minutes prior to your actual time and the end date to what you need. If this doesn’t work, proceed as follows.
2. To drop the current credential, connect to SQL Server Management Studio.
3. Open a new query window and connect to the SQL Server Instance for the DB engine in your On-Premise environment
4. In the query window, past the following and execute:
a. DROP DATABASE SCOPED CREDENTIAL [yourcredentialName]; GO
b. Verify that the credential has been drop by performing a new query using, SELECT * from sys.credentials
5. Now to create a new credential with a new SAS token
6. Using storage explorer in the portal is the easiest way to create the SAS token. All you need to take is the part of the string after the question mark for “SAS token” and use that in the SECRET clause for CREATE CREDENTIAL in step 9.
