One of my blog readers was having trouble with backup to URL using SAS token. He followed my below blog to fix his issue. SQL SERVER – Backup to URL error: Operating system error 50(The request is not supported.) Now let us look in this blog post how we can fix errors related to credentials.
So far, so good! Later he was unable to take a backup using maintenance plan. He contacted me, and it was sounding very interesting to me. He informed that he is getting below errors when he configures backup to URL using maintenance plan.
Executing the query “BACKUP DATABASE [master] TO URL = N’https://sqlau…” failed with the following error: “Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.
BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
So, I decided to reproduce the issue and created a maintenance plan.
As he explained me, the backup failed! Looked into history
Use “View T-SQL” hyperlink and found below query.
BACKUP DATABASE [master] TO URL = N'LONG_URL_HERE_' WITH CREDENTIAL = N'https://sqlauthsqlbackups.blob.core.windows.net/sqlauthcontainer', NOFORMAT, NOINIT, NAME = N'master_backup_2017_12_07_222756_5893177', SKIP, REWIND, NOUNLOAD, STATS = 10 GO
When I ran above in SSMS, it failed with error
Msg 3225, Level 16, State 1, Line 1
Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
WORKAROUND/SOLUTION
I looked around in the documentation and found this document.
To create a striped backup set, a SQL Server file-snapshot backup, or a SQL credential using Shared Access token, you must use Transact-SQL, Powershell or C# rather than the Backup task in Maintenance Plan Wizard.
This confirms that Microsoft doesn’t support taking backup to URL using SAS token via maintenance plan. Now, the only option left is to use T-SQL and schedule that as a job. Painful!
Well, not to worry! You can use below script to generate backup file name with date time and schedule it in the job. How to Add Date to Database Backup Filename? – Interview Question of the Week #109
Please comment if you have encountered a similar error and found some other solution.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
To fix this you can remove the credentials by clicking on the job step and viewing the properties of the step (This is different than hitting edit). That docked screen will let you delete the credentials and the step will then work!
I tried that and I get an Operating System error and cannot backup…good idea though. Sql Server 2016
If you generate your credential like this:
IF NOT EXISTS
(SELECT * FROM sys.credentials
WHERE name = ”)
CREATE CREDENTIAL [] WITH IDENTITY = ”
,SECRET = ”;
… and then reference it in the maint plan, it will do the trick.
I’m not sure if you can stripe using this method under a maint plan. Striping always seemed to be something you had to do with TSql or some other script that you built to generate backup commands dynamically.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017