SQL SERVER – Error: 45207 – SQL Server Managed Backup to Microsoft Azure Cannot Configure the Database Because a Container URL was Either not Provided or Invalid

While using SQL Server on Virtual Machines in Azure, I ran into an interesting error.  In this blog we would learn how to fix Msg 45207 – SQL Server Managed Backup to Microsoft Azure cannot configure the database ‘sqlauthdb’ because a container URL was either not provided or invalid. It is also possible that your SAS credential is invalid.

When I deployed SQL Server Azure Virtual machine, I enabled a feature called “Automatic Backup”. Due to this setting SQL Server was taking regular backups on blob storage. Since I am not running a production server, I decided to minimize the cost by deleting the unwanted resource. So, I delete the storage account. Now I noticed that SQL Server started giving an error in SQL ERRORLOG about backup failures, so I decided to disable this feature.

When I did from the Azure portal, the disabling operation failed with below error:

Error type

At least one resource deployment operation failed. Please list deployment operations for details. Please see https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/common-deployment-errors for usage details. (Code: DeploymentFailed)

Error details

The resource operation completed with terminal provisioning state ‘Failed’. (Code: ResourceDeploymentFailure)

VM has reported a failure when processing extension ‘SqlIaasExtension’. Error message: “SQL Server IaaS Agent: SQL Server Managed Backup to Microsoft Azure cannot configure the database ‘sqlauthdb’ because a container URL was either not provided or invalid. It is also possible that your SAS credential is invalid. ;The creator of this fault did not specify a Reason.;Automated Patching: Automated Patching enabled: False, Windows Update state: ScheduledInstallation, VM is up to date in applying important updates.;Automatic Telemetry: Performance Collector State: DisabledOptedOut”. (Code: VMExtensionProvisioningError)

Here is the screenshot for the same error.

I then decided to disable from SQL Server using managed backup related stored procedure. I executed below code:

EXEC managed_backup.sp_backup_config_basic  
	@enable_backup=0 
	,@database_name = 'sqlauthdb'

Well, it failed with an exact same error which we got from the portal.

Msg 45207, Level 17, State 11, Procedure managed_backup.sp_add_task_command, Line 102 [Batch Start Line 8]

SQL Server Managed Backup to Microsoft Azure cannot configure the database ‘sqlauthdb’ because a container URL was either not provided or invalid. It is also possible that your SAS credential is invalid.

There is nothing wrong with the error message. I have deleted the storage account, so URL is definitely invalid. What should I do now?

I had two choices:

1. Create same storage account again with new SAS token given in SQL credential so that SQL can connect to storage and disable it.
2. Find a way to cleanup all settings related to managed backup in SQL Server.

I am a lazy guy and I wanted to get things done by choice # 2.

WORKAROUND/SOLUTION

While looking at msdb stored procedures, I came across an interesting procedure. (thanks to intellisense feature of SSMS) autoadmin_metadata_delete

When I looked at the code of the stored procedure, it says below

— Procedure to delete entries in metadata tables

Perfect! This is what I was looking for. Here is the code which I ran, and it magically cleaned up everything.

NOTE: I must mention that you should use this with caution in production as it deletes everything about managed backup for all databases. Also, it is not documented on MDSN so Microsoft might remove it later from the product.

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