How to restore azure sql server database from .bak file located in blob storage automatically with sql server agent jobs

Question

Hi!

My current setup consists of a blob storage account cointaining .bak backup files, in addition to an SQL server running on an Azure Virtual Machine. I want to automatically restore the database located at the sql server every night (when a new .bak file will arrive to the blob container).

Our plan was to set up a SQL Server Agent Job which is scheduled every night, which restores the wanted database from the newest .bak file in the blob container (note the .bak file has the same name every time so it will just be overwritten).

We have almost managed to get the T-SQL script for the restore operation to work, but are struggeling with the SQL SERVER AGENT not having permission to restore the database.

We have set the user that runs the restore step of the job to be the DBO user. We get the following error message:

Executed as user: dbo. User does not have permission to alter database 'XXXXX', the database does not exist, or the database is not in a state that allows access checks. [SQLSTATE 42000] (Error 5011) ALTER DATABASE statement failed. [SQLSTATE 42000] (Error 5069) Cannot open backup device 'https://....blob.core.windows.net....../.bak'. Operating system error 13(The data is invalid.). [SQLSTATE 42000] (Error 3201) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

The T-SQL code for the restore step of the job is:

ALTER DATABASE XXXXX SET TRUSTWORTHY ON

RESTORE DATABASE [....] FROM URL = 'https://......blob.core.windows.net/.....bak'

GO

NOTE: We have already saved the CREDENTIAL containnig the SAS-key for the blob storage, therefore it is not in the current T-SQL script.

Is there something we have to do in SSMS to make the SQL Server Agent Job manage to perform the restore? i am able to do the restore manually using the GUI, but not by using T-SWL in server agent job.

Thanks in advance!

Answer 1

We have set the user that runs the restore step of the job to be the DBO user.

So this is a T-SQL job step, and you have set the user on the Advanced page for the jobs step, like below? That will not work out. When you do this, SQL Server Agent will impersonate that database user and when you impersonate a database user, you are sandboxed into the current database, and you cannot access things outside of it. And since you are to restore that database, the job cannot run with that database as the current database. So this means that on top of all, you are impersonating the dbo of another user.

Just remove the Run as user and let the job execute as sysadmin, and it should run without issue.

By the way, any special reason you are setting the database to TRUSTWORTHY? This is a security risk if there are users in the that database with elevated permissions on database level, but who are not members if sysadmin.

Answer 2

@Erland Sommarskog Thanks for your answer! I will mark it as a solution to our problem as soon as we manage to solve the problem, however a new one appeared. Do you know anything about this:

Executed as user: NT SERVICE\SQLSERVERAGENT. A nonrecoverable I/O error occurred on file "https://......blob.core.windows.net/.../.......bak:" Backup to URL received an exception from the remote endpoint. Exception Message: Error occurred while referencing Blob in remote storage. [SQLSTATE 42000] (Error 3271) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

For example, why is there a COLON ":" at the end of the URL when we did not have it there in the first place?

Our TSQL code now looks like this:

CREATE CREDENTIAL [Credential_generalpurpose]

WITH IDENTITY = '<name of our storage account>',

SECRET = '<a secret key of our storage account>';

RESTORE DATABASE <name of database> FROM URL = 'https://......blob.core.windows.net/.../......bak'

WITH CREDENTIAL = 'Credential_generalpurpose'

As you can see the : was not there in the first place.

We have also tried using SAS-credentials but then we get another error:

. Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature. [SQLSTATE 42000] (Error 3225) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

NOTE: the .bak file is of type PAGE BLOB, and it has to be according to Microsoft pages, if not we can not restore from it.

Thanks again for your help Erland it is greatly appreciated!