Database backup to Azure giving incorrect parameter errors about sql-server-maintenance-solution HOT 10 CLOSED

The @url needs to have a trailing slash; so try @url = 'https://myaccount.blob.core.windows.net/mycontainer/',

As for @credential, it should just be the name of the credential; if you expand Security > Credentials in Object Explorer, do you see 'mycredential' listed?

from sql-server-maintenance-solution.

I didn't have the slash, but adding it doesn't seem to have helped. I'm still getting the same error.
for credential, you're right i didn't know what to put in there. I originally put in the key for my azure storage. There are no credentials in my sql object explorer. What should be put inside there?

If it helps, i originally had a MaintenanceSolution.sql installed from a couple years ago, but i re-ran the latest one. Could it be possible that it didn't update, and i'm looking at an older version?

from sql-server-maintenance-solution.

Re-running the script should upgrade correctly.
What version of SQL Server are you using?

from sql-server-maintenance-solution.

The easiest way to determine that would be to script out dbo.DatabaseBackup and see if the @url parameter is in the list of possible arguments.

from sql-server-maintenance-solution.

it is. My parameter output is:

Parameters: @databases = 'USER_DATABASES', @Directory = NULL, @BackupType = 'FULL', @verify = 'Y', @Cleanuptime = NULL, @CleanupMode = 'AFTER_BACKUP', @compress = 'Y', @copyonly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @Checksum = 'N', @Blocksize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @description = NULL, @threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @url = 'https://XXX.blob.core.windows.net/XXX/', @credential = 'mycredential', @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @AvailabilityGroups = NULL, @Updateability = 'ALL', @LogToTable = 'N', @execute = 'Y'

I'm running SQL version 10.50.6220.0, and using sql management studio 17.1

from sql-server-maintenance-solution.

I got the same error message several months ago. And in my situation the backup and restore was working fine on SQL 2012/2014 because the credentials have been created using storage account 'access key'. When I started using SQL 2016 and wanted to create the credential using 'Shared Access Signature' and use the optional switches to tune backups (split the backup files, change the buffer count, change the transfer size) immediately I got the same error message. But credential created using storage account 'access key' worked. I think at this time the backup procedure does not support the credentials created using 'shared access signature'. I did find a way to make it work by updating (I meant hacking the procedure) but I am not proud to share it yet. But it works for me. Maybe something that I can add and share it here later. Here is a sample scripts I pulled from MSDN that shows how to create the credential. If you used the second one, use the first one to create a new credential and test it to make sure it works.

-- Create credential using storage account access key
CREATE CREDENTIAL mycredential
WITH IDENTITY= 'mystorageaccount' – this is the name of the storage account you specified when creating a storage account
, SECRET = '' – this should be either the Primary or Secondary Access Key for the storage account
GO

-- Create credential using shared access signature
CREATE CREDENTIAL 'https://.blob.core.windows.net/' – this name must match the container path, start with https and must not contain a 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 key
GO

from sql-server-maintenance-solution.

Ah; you're on SQL 2008 R2. It doesn't have the ability to backup to a URL (the scripts here use the underlying BACKUP DATABASE capabilities).
You might be able to resolve it by installing [https://www.microsoft.com/en-us/download/details.aspx?id=40740](SQL Server Backup to Azure Tool)

from sql-server-maintenance-solution.

Thanks for everyones help. A few points:
@dsolodow :
Yes, that was SQL 2008. When i moved to SQL 2014 I got rid of the @url error
@bulentgucuk :
storage access key worked w/out issues at all
your script to create the credential for the shared access signature doesn't work, it had to be something more like:

CREATE CREDENTIAL myCredential – just any name really
WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.
, SECRET = 'https://.blob.core.windows.net/sv=XXXXX' –- the very long SAS that gets generated, with URL and everything

Unfortunately the shared access signature still doesn't work however, when i try to run the backup the same way that i did with the access key, i get the following 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.

So basically, i'm up and running fine, so i'm good. But I couldn't get it to work with shared access signatures.

from sql-server-maintenance-solution.

DatabaseBackup supports backup to Azure Blob Storage using a Shared Access Signature. Please download and test the latest version of the script if you are still having the issue.

from sql-server-maintenance-solution.

Msg 3225, Level 16, State 1, Line 1
Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.

For anyone else landing here, some more things to check (SQL 2017)

• Do not use the @credential parameter on DatabaseBackups
• The credential's name must be https://ACCOUNT.blob.core.windows.net/CONTAINER
• The credential's value must be the SAS token only and without its leading ?
  Just: sv=......................
• Only Storage Account SAS tokens are supported, not the more flexible Blob Container SAS token Edit I might be mistaken. There is an example with container SAS tokens.

Ref:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/8220-cannot-open-backup-device-8221-error-seen-when-backing-up/ba-p/369101

from sql-server-maintenance-solution.