SQL SERVER – Backup to URL – Script to Generate Credential and Backup using Shared Access Signature (SAS)

July 17, 2018

As I mentioned in my earlier blog, backup to URL is one of the common methods used in SQL Server performs a backup to Azure Blob Storage. In this blog, I am going to share a script to generate the create credential and backup command using Shared Access Signature also called as SAS token.

If you don’t know already, Backup to URL also has two methods to connect to the storage account

Credential by using Access Keys.
Credential by using SAS token.

In my earlier blog, I have shared script to use the first method. SQL SERVER – Msg 3292: A Failure Occurred While Attempting to Execute Backup or Restore With a URL Device Specified

In this blog, I would show the second method – Backup using Shared Access Signature.

WORKAROUND/SOLUTION

In the script, we need to provide below parameters.

@StorageAccountName: In Azure portal, go to “Home” > “Storage accounts” and pick up the account which you want to use. In my demo, its “sqldbprodbackups”.
@ContainerName: To get a container name, you can refer below screenshot. You need to click on “Browse Blobs”. If you don’t have container created already then click on “+” symbol and create a new one. In my Azure, I have already created one called “dailybackups” as shown below. You can also see @StorageAccountName on the same page.

SQL SERVER - Backup to URL - Script to Generate Credential and Backup using Shared Access Signature (SAS) Backup-SAS-Script-01

@SASKey: Refer below steps for SAS Key generation.

We need to click on “Shared access signature” as shown below.

SQL SERVER - Backup to URL - Script to Generate Credential and Backup using Shared Access Signature (SAS) Backup-SAS-Script-02

Then we need to click on “Generate SAS and connection string” button. Once done, scroll down and we should see something like below.

SQL SERVER - Backup to URL - Script to Generate Credential and Backup using Shared Access Signature (SAS) Backup-SAS-Script-03

The value should be assigned to variable @SASKey

---- Backup To URL (using SAS Token) :
--- =================================== --- 
DECLARE @Date AS NVARCHAR(25)
	,@TSQL AS NVARCHAR(MAX)
	,@ContainerName AS NVARCHAR(MAX)
	,@StorageAccountName AS VARCHAR(MAX)
	,@SASKey AS VARCHAR(MAX)
	,@DatabaseName AS SYSNAME;
SELECT @Date = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100), '  ', '_'), ' ', '_'), '-', '_'), ':', '_');
SELECT @StorageAccountName = ''; --- Find this from Azure Portal
SELECT @ContainerName = ''; --- Find this from Azure Portal
SELECT @SASKey = ''; --- Find this from Azure Portal
SELECT @DatabaseName = 'master';
IF NOT EXISTS (
		SELECT *
		FROM sys.credentials
		WHERE name = '''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + ''''
		)
BEGIN
	SELECT @TSQL = 'CREATE CREDENTIAL [https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '] WITH IDENTITY = ''SHARED ACCESS SIGNATURE'', SECRET = ''' + REPLACE(@SASKey, '?sv=', 'sv=') + ''';'
	--SELECT @TSQL
	EXEC (@TSQL)
END
SELECT @TSQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO '
SELECT @TSQL += 'URL = N''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '/' + @DatabaseName + '_' + @Date + '.bak'''
SELECT @TSQL += ' WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 1;'
--SELECT @TSQL
EXEC (@TSQL)

Once the script was executed, we could see credential in SSMS and backup in Azure.

SQL SERVER - Backup to URL - Script to Generate Credential and Backup using Shared Access Signature (SAS) Backup-SAS-Script-04

SQL SERVER - Backup to URL - Script to Generate Credential and Backup using Shared Access Signature (SAS) Backup-SAS-Script-05

Hope this would help you in creating the script in an easier way.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Azure, SQL Backup, SQL Scripts, SQL Server

SQL SERVER – Msg 3292: A Failure Occurred While Attempting to Execute Backup or Restore With a URL Device Specified

SQL SERVER – Backup to URL – Script to Perform Stripped Backup using Shared Access Signature (SAS)

SQL SERVER – Microsoft Certification – SQL Server 2012

April 29, 2012

SQL SERVER – FCB::Open Failed – TEMPDB Files Fail to be Created with Error: “CREATE FILE Encountered Operating System Error 3”

January 21, 2019

SQL SERVER – Lots of Date Functions – Find Right One to Use – Quiz – Puzzle – 27 of 31

January 28, 2012

9 Comments. Leave new

Gina Hohenstatt
November 30, 2018 2:35 am
Hello,
We are attempting to create a credential on our SQL Server Azure Paas instance using this helpful script. The results of the script fail with message “‘CREATE CREDENTIAL’ is not supported in this version of SQL Server.”.
We are creating this credential for the purpose of restoring a database from a .bak file stored in blob storage in Azure.
The current version for this SQL instance is:
‘Microsoft SQL Azure (RTM) – 12.0.2000.8 Nov 20 2018 21:01:54 Copyright (C) 2018 Microsoft Corporation ‘
This instance was created a couple of weeks ago with an empty database that we hoped to load from a recent backup for testing.
Can you provide advise on how we can proceed with getting the credential created?
Thank you in advance!
Reply
Paul
June 20, 2019 6:59 pm
Can you use the same credential, storage account for Managed Backup. When using smart_admin.sp_set_db_backup with it I get “Msg 45207, Level 17, State 6, Procedure managed_backup.sp_add_task_command, Line 102 [Batch Start Line 22]
The credential ‘https://xxxxxxxx.blob.core.windows.net/xxxxxxbackupcontainer’ specified either does not exist or does not have the authentication information to access the storage. Provide a valid SQL credential.”
It works fine using backup to url using your script above.
Reply
G Laxmikanth
August 27, 2020 3:38 am
Thank you for the detailed post. Though you have used REPLACE(@SASKey, ‘?sv=’, ‘sv=’) in your script, If you would have mentioned about removal of the question mark (?) from the SAS token, it would have saved me a good amount of time. I created the credential using the SAS key through T-SQL command directly, I didn’t generate the script as you did. Microsoft also didn’t mention anything about this in their document.
Great post and thank you.
Reply
Ahmed Shehatoo
June 27, 2021 1:42 pm
Thank you very much Pinal , Is there a way to make this work in SQL 2014 as it worked in SQL 2019 but not in 2014
‘A Backup device of type URL was specified without a Credential, Backup/Restore operation cannot proceed.
‘.
Reply
Ahmed shehatoo
June 27, 2021 2:20 pm
Thank you for your great blog , I’m getting an error while using it on SQL 2014
Reply
Tom
July 12, 2022 9:27 pm
Hi Pinal,
Is it possible to use the ‘Managed Identity’ in Azure (whereby the machine is secured against the StorageAccount) so that we do not to store a credential with a secret?
CREATE CREDENTIAL ServiceIdentity WITH IDENTITY = ‘Managed Identity’;
The following confirms that we can use the machine’s identity
.\azcopy login –identity
.\azcopy list https://(location).blob.core.windows.net/(container)
but, we get the following when backing up:
Fail to obtain or decrypt secret for credential ‘ServiceIdentity’
Thanks
Reply
pds
May 28, 2024 1:08 am
Pinal, Thanks for Excellent Articles, very useful and handy script using variable.
I tried follow exactly how you have shown to use it, everything is fine but I am getting following error, appreciate your help!
I have no ‘?’ in my SAS token, set up all the permissions you have shown
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘https://sqlstg.blob.core.windows.net/sqlbackup/AdventureWorks2022_May_27_2024_2_34PM.bak’. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Reply

Apr 2025 Discount: Comprehensive Database Performance Health Check | Testimonials