Sql-server – Setup Managed Backup failure

azure-blob-storageazure-sql-databasesql server

I am trying to setup Managed Backup (in Azure SQL) using SQLServer 2014 (Ent 64bit) and it is failing.

I have the credential created with Identity as the Storage account name and Password set with current Access key, I've tried both keys as well.

When I run through the GUI to Enable managed backup, set the retention period, add the SQL credential (which updates the Storage URL) and I press OK, I get the follow error message back:
Alter failed for

SmartAdmin 'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'. (Microsoft.SqlServer.Smo)

Failed to access the storage URL 'https://[storageaccountname].blob.core.windows.net/' using credential 'MyCredName'.
Provide a valid SQL Credential for the storage URL. (Microsoft SQL Server, Error: 45207)

I downloaded Azure Storage Explorer from codeplex to test connection and it works, I can browse, upload, delete from my local machine.

I tried this from one of our on prem servers as well and same error. What am I missing?

Best Answer

Issue identified. The network admin didn't have the separate Azure domain environments trusted, although he said that he did. I tried this and failed in our SQL DEV environment/domain in Azure, which isn't trusted. I then, for sake of troubleshooting and communicating to the powers to be, tried it from our SQL QA environment/domain in Azure and it worked flawlessly! The network admin compared the two environments and found the issue. Network issue after all.

Related Solutions

Sql-server – Backup failure in SQL Server 2005

Well, suppose you have a command to execute your backup like this

BACKUP DATABASE [naass] 
TO  DISK = N'C:\program files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\Naass.bak'' 
WITH NOFORMAT, INIT,  
NAME = N'naas-Complete Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

this will fail if the process that executes this command doesn't have the permissions to write in the mentioned folder. And Windows 7 (for very good security reasons) keeps the C:\program files folders and its subfolders write locked also to an administrator account.

You could mess with the default permissions and give to your folder above write permissions for your account but of course this is not the recommended way.
Much better, simply change the script to work on a different directory on your disks.

BACKUP DATABASE [naass] 
TO  DISK = N'D:\BackupSQL\naass.bak' 
WITH NOFORMAT, INIT,  
NAME = N'naas-Complete Database Backup'
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Of course this new directory should have the correct permissions.

Sql-server – Managed backup to Azure does not delete backup after retention period

I ended up making a Sql server agent job that runs this with powershell mode:

# Ps1-script to keep azure storageaccount for managed backups clean

Import-Module Azure

$storageAccount = "STORAGEACCOUNTNAME"
$storageKey = "STORAGEKEY"
$blobContainer = "CONTAINERNAME"

$CleanupTime = [DateTime]::UtcNow.AddDays(-4)
$context = New-AzureStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageKey
Get-AzureStorageBlob -Container $blobContainer -Context $context | 
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |
Remove-AzureStorageBlob
$CleanupTime = [DateTime]::UtcNow.AddDays(-4)
Get-AzureStorageBlob -Container $blobContainer -Context $context | 
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.log"} |
Remove-AzureStorageBlob

This does delete all .bak and .log files older than 4 days. It does not however work on blobs that does have active lease on them.

Best Answer

Related Solutions

Sql-server – Backup failure in SQL Server 2005

Sql-server – Managed backup to Azure does not delete backup after retention period

Related Question