SQL with Manoj

 
Today I got an email from someone regarding an issue he was facing. He was trying to Backup SQL Server database to URL i.e. the Azure Blob Storage, by issuing below Backup command:

USE [master]
GO

BACKUP DATABASE [ManTest]
 TO  URL = N'https://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak'
 WITH 
	COMPRESSION  
    ,STATS = 5;  
GO

And he was getting following error:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
‘https ://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak’.
Operating system error 50(The request is not supported.).

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

By checking the above error, you can make out that we are not able to connect to the Azure Storage resource due to some access issues. I checked online and found that you need to create Credentials on SQL Server from where you want to access the Azure Storage resource, and there are few methods for the same:
 

–> Method #1: Using storage account identity and Access Key

Azure Access Keys authenticates your applications when making requests to the Azure storage account. You will have to create a Credential on SQL Server end by providing the Access Key from your Azure Storage account, check below:

USE [master]
GO

CREATE CREDENTIAL [DBBackupCred] 
WITH IDENTITY = 'mantestaz'  -- Storage Account Name
,SECRET = 'xyx35HWTOnkDpiHkNWayz2Gsw6Figyxyx=='; -- Access key
GO

– Now you can fire the BACKUP command with the additional WITH CREDENTIAL option so that the Access can be authenticated for storing and writing backups on Azure Blob Container:

USE [master]
GO

BACKUP DATABASE [ManTest]  
 TO  URL = N'https://mantestaz.blob.core.windows.net/sqlbackups/mantest.bak' 
     WITH CREDENTIAL = 'DBBackupCred'   
    ,COMPRESSION  
    ,STATS = 5;  
GO

38 percent processed.
77 percent processed.
99 percent processed.
Processed 328 pages for database ‘ManTest’, file ‘ManTest’ on file 1.
100 percent processed.
Processed 3 pages for database ‘ManTest’, file ‘ManTest_log’ on file 1.
BACKUP DATABASE successfully processed 331 pages in 15.792 seconds (0.163 MB/sec).

… and its done successfully.
 

–> Method #2: Using Shared Access Signature (SAS)

A Shared Access Signature (SAS) is a URI that grants restricted access rights to Azure Storage resources. You can provide a shared access signature to clients who should not be trusted with your storage account key but whom you wish to delegate access to certain storage account resources. By distributing a shared access signature URI to these clients, you grant them access to a resource for a specified period of time.

Here is the syntax to create SAS Credential:

USE [master]
GO

CREATE CREDENTIAL 
	[https://<storageaccountname>.blob.core.windows.net/<containername>] 
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = '<SAS_TOKEN>';