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.

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 disable-auto-bkp-01

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

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 disable-auto-bkp-02

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)

SQL Azure, SQL Backup, SQL Error Messages, SQL Log, SQL Scripts, SQL Server, SQL Server Security
Previous Post
SQL SERVER – SQL Server Configuration Manager Error: 0x80010108 – The Object Invoked Has Disconnected From its Clients
Next Post
SQL SERVER – SSIS – Package Error: “ODBC Source” Failed Validation and Returned Validation Status “VS_NEEDSNEWMETADATA”

Related Posts

2 Comments. Leave new

  • Derk Bernhardt
    October 6, 2021 1:58 pm

    Hi!

    Many thanks for your investigations! I ran into the same issue and could solve it with deleting the autoadmin* tables with “exec autoadmin_metadata_delete”. But first I got a new error message that the autoadmin* tables were bad/corrupt. A SQL server service restart fixed it then:

    1.) exec autoadmin_metadata_delete
    2.) restart SQL server service
    3.) Initiate the Backup via the Azure Portal agan

    Thanks,
    Derk

    Reply
  • Huge thank you! I was going down the same path of frustration and didn’t know where to go next after getting that same error. It’s been an interesting process learning how azure simply automates all of these various stored procedures behind the scenes. I wish there was a little more transparency available through the portal on such things, although I’m not sure the best way surface that information. Thanks again.
    Mike

    Reply

Leave a Reply