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:
- Create same storage account again with new SAS token given in SQL credential so that SQL can connect to storage and disable it.
- 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)
2 Comments. Leave new
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
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