Backing up SQL Server databases to Blob Storage using Impersonation
One of the main goals I’m trying to achieve when developing solutions is giving as much autonomy to individuals and teams while still keeping the boat tight. I had an interesting challenge that came up recently where a developer was doing massive changes in the data and needed to take incremental backups of the database, as he was working, to give himself a safety net in case he screwed up. We can say this is the source control way, database style.
The physical hardware space of the server is limited. Taking backups often can become expensive in terms of size. Thanks to the SQL Server team, we can backup (and restore) a database to (or from) an Azure Blob Storage.
The developer was going to backup more often than restore. Having this in mind, I chose to use a cold tier for the Azure Storage as the price is low and cheap for a lot of space. The other requirement is that to backup (and restore) databases, there’s a lot of permissions you need to grant. In order to minimize the permissions granting to all users that need this functionality, I chose to create a user who would have all the rights to accomplish this, and use impersonation of this user to the users who need to backup/restore.
Note that this article applies to SQL Server 2016 and above.
Setup
Creating the Database Tools
I like to regroup my maintenance solution scripts and tools into a single place. In this database, I have the fantastic SQL Server maintenance solution tools from Ola Hallengren. This is the backup solution I will use to backup databases.
Creating a Storage Account
The first thing you need to do is have yourself a Storage Account. To be able to backup and restore from SQL server using an URL, you need to need to make sure the kind of Storage account you provision (or use) is of type (kind) Storage or StorageV2. BlobStorage will not work for this. You will get an error when trying to backup/restore from it.
Configuring the Storage Account in the server
SQL Server can connect to your Storage Account using 2 ways: SAS Token or Access Keys. I chose to use the Access Keys of the storage account. Use the approach that you and your enterprise are comfortable with.
The WITH CREDENTIAL is a new option and required to back up to or restore from the Azure Blob storage service. I created the credential to use access keys as follow:
|
1 2 |
IF NOT EXISTS (SELECT * FROM sys.credentials WHERE credential_identity = 'azurestoragebackup') CREATE CREDENTIAL <credential name> WITH IDENTITY = 'mystorageaccount',SECRET = '<storage access key>'; |
Replace mystorageaccount with the name of your storage account and <storage access key> with one of your storage account access keys
Configuring the backup user
The first thing is to create the user that the user(s) (or groups) will impersonate and grant him a few permissions. Ola Hallengren database backup script gives a handful of information when being used like the state of the database and so on. To be able to get that information, we need to grant our user a few extra permissions on the server level.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [master] CREATE USER [backupoperator] FOR LOGIN [backupoperator] WITH DEFAULT_SCHEMA=[dbo] GO GRANT VIEW ANY DEFINITION TO backupoperator GO GRANT ALTER ANY CREDENTIAL TO backupoperator GO GRANT CREATE DATABASE TO backupoperator GO GRANT VIEW SERVER STATE TO backupoperator GO |
GRANT ALTER ANY CREDENTIAL is needed to be able to access the credential to backup.
GRANT CREATE DATABASE is needed to be able to do a RESTORE VERIFY if you use the verify option.
The database script also requires access to log_shipping_* tables in the msdb database
|
1 2 3 4 5 6 7 |
USE [msdb] CREATE USER [backupoperator] FOR LOGIN [backupoperator] WITH DEFAULT_SCHEMA=[dbo] GO GRANT SELECT ON msdb.dbo.log_shipping_primary_databases TO backupoperator GO GRANT SELECT ON msdb.dbo.log_shipping_secondary_databases TO backupoperator GO |
In my tools database, I only want the backup user to be able to execute certain procedures. To use Ola’s backup script, you need to grant him EXECUTE on 2 procedures
|
1 2 3 4 5 6 7 |
USE [DatabaseTools] CREATE USER [backupoperator] FOR LOGIN [backupoperator] WITH DEFAULT_SCHEMA=[dbo] GO GRANT EXECUTE ON dbo.DatabaseBackup TO backupoperator; GO GRANT EXECUTE ON dbo.CommandExecute TO backupoperator; GO |
Last, add the backup user in the database that you want the users to be able to backup
|
1 2 3 4 5 |
USE [DB_NAME_THAT_YOU_WANT_TO_ALLOW_TO_BACKUP] CREATE USER [backupoperator] FOR LOGIN [backupoperator] WITH DEFAULT_SCHEMA=[dbo] GO ALTER ROLE [db_backupoperator] ADD MEMBER [backupoperator] GO |
Configuring the user who will have the rights to backup
To let a user BACKUP a database, we need grant LOGIN impersonation, so that the user can impersonate on server level
|
1 2 3 |
USE [master] GRANT IMPERSONATE ON LOGIN::backupoperator TO <user> GO |
Replace <user> with the user in your database that you want to allow to impersonate your backup user.
Creating the backup procedure
The procedure that will be used by the users isn’t the procedures that are in Ola’s solution. I created a wrapped stored procedure and only give rights to the user to execute this procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE PROCEDURE [dbo].[DatabaseBackupForUsers] @Databases NVARCHAR(MAX) = NULL AS BEGIN IF (@Databases IS NULL) BEGIN RAISERROR('No databases were set',10,1) WITH NOWAIT END EXECUTE AS LOGIN = 'backupoperator' EXECUTE dbo.DatabaseBackup @Databases = @Databases, @URL = 'https://<storageaccountname>.blob.core.windows.net/<storagecontainername>', @Credential = 'azurestoragebackup', @BackupType = 'FULL', @Compress = 'Y', @Verify = 'Y'; END |
As you can see when the user is executing the procedure, it will be impersonating our backup user, that is run the procedure as if it was him.
We then need to GRANT the user who will backup databases EXECUTE on this stored procedure
|
1 2 3 |
USE [DBTools] GRANT EXECUTE ON dbo.DatabaseBackupForUsers TO <user> GO |
What about restore?
If you want to allow the user who can backup the possibility to restore as well, you need to add the backup user into the dbcreator server role. This will allow the user to create databases.
|
1 2 3 |
USE [master] ALTER SERVER ROLE [dbcreator] ADD MEMBER [backupoperator] GO |
You also need to grant that user ALTER DATABASE on the database that you want to restore
|
1 2 |
GRANT ALTER ON DATABASE::DATABASE_NAME TO [backupoperator] GO |
To restore, you can then create another wrapper stored procedure, to which you will grant EXECUTE to the users who can restore. Here are 2 examples of what can go in this stored procedure
Restore on the same database
|
1 2 3 4 5 6 7 8 9 |
ALTER DATABASE DATABASE_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE DATABASE_NAME FROM URL = 'https://<storageaccountname>.blob.core.windows.net/<storagecontainer>/<SERVER_NAME>/<DATABASE_NAME>/FULL/<DATABASE_BACKUP_NAME>.bak' WITH CREDENTIAL = 'azurestoragebackup', RECOVERY, STATS = 5; |
Restore to a different database name
|
1 2 3 4 5 6 7 |
RESTORE DATABASE DATABASE_NAME FROM URL = 'https://<storageaccountname>.blob.core.windows.net/<storagecontainer>/<SERVER_NAME>/<DATABASE_NAME>/FULL/<DATABASE_BACKUP_NAME>.bak' WITH CREDENTIAL = 'azurestoragebackup', RECOVERY, STATS = 5, MOVE 'OIPA_Data' to 'F:\Data\<DATABASE_NAME>.mdf', MOVE 'OIPA_Log' to 'F:\Log\<DATABASE_NAME>.ldf'; |
You can use Dynamic SQL to replace the database name and what not. Remember to add EXECUTE AS LOGIN = 'backupoperator' before running the Dynamic SQL command.
