Azure SQL restore - WITHOUT using system admin account

[goodr189]

We are currently trialing Veeam backup for Azure and are running into a security concern regarding restoring an Azure SQL database.

When restoring an Azure SQL database with Veeam for Azure, it appears that the server admin SQL account is required. We are using a staging server. If we create any other user account on the Azure SQL server and use it, we run into permission issues, even if it has all the limited admin roles as defined in the Microsoft article Veeam points to - https://learn.microsoft.com/en-us/azure ... ermissions

Is this a limitation for Azure SQL?

I was told by an employee that the service account handles this. However from my testing, you cannot have the service account run the restore into the SQL Server on Azure as it asks for the staging server and SQL account. If the SQL account is any other account other than the system admin, it fails.

[lyudmila.ezerskaya]

Hi! To perform database restores, the SQL account must have permissions to create and delete databases, as well as to export and import data. We will look into the issue and get back to you.

[amitracks]

Hi! To perform database restores, the SQL account must have permissions to create and delete databases, as well as to export and import data. We will look into the issue and get back to you.

That is correct however with Azure SQL, even with all the admin permissions added to the account, it still fails.

n SQL Database, create SQL logins with limited administrative permissions

Create an additional SQL login in the master database.
Add the Login to the ##MS_DatabaseManager##, ##MS_LoginManager## and ##MS_DatabaseConnector## server level roles using the ALTER SERVER ROLE statement.

Here is the output in Veeam if that helps:

Code: Select all

BACPAC import to veeam has failed: Worker VBA-b0d28886-ce9e-4710-b049-665f24fc49b6 (10.76.10.4): job completed with error. Error: Could not import package.
Warning SQL72012: The object [data_0] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
Warning SQL72012: The object [log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 15247, Level 16, State 13, Line 5 User does not have permission to perform this action.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [sys].[databases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
    END


 (trace ID: 94bf6c5b-a3ad-47e4-b071-7ef24ecf9e3f)

[lyudmila.ezerskaya]

Hi! Thanks for providing this information.
Could you confirm whether you performed a successful connection check at the Summary step of the wizard before starting the restore operation?

[amitracks]

Yes, the connection test passes.

[lyudmila.ezerskaya]

Thank you for sharing. We'll need some extra time for the research.

[amitracks]

If this helps, the repro steps are:

1. Create an Azure SQL user per https://learn.microsoft.com/en-us/azure ... ermissions: (note that Entra users are not supported by Veeam for Azure SQL, so the steps are to created the limited admin)

In SQL Database, create SQL logins with limited administrative permissions

Create an additional SQL login in the master database.
Add the Login to the ##MS_DatabaseManager##, ##MS_LoginManager## and ##MS_DatabaseConnector## server level roles using the ALTER SERVER ROLE statement.

Members of special master database roles for Azure SQL Database have authority to create and manage databases or to create and manage logins. In databases created by a user that is a member of the dbmanager role, the member is mapped to the db_owner fixed database role and can log into and manage that database using the dbo user account. These roles have no explicit permissions outside of the master database.

Important

You can't create an additional SQL login with full administrative permissions in Azure SQL Database. Only the server admin account or the Microsoft Entra admin account (which can be a Microsoft Entra group) can add or remove other logins to or from server roles. This is specific to Azure SQL Database.

2. Add the new SQL account to Veeam

3. Use this SQL account when restoring a database into the server

[amitracks]

Has there been any update or activity for this issue?

[lyudmila.ezerskaya]

Hi! We are still investigating the issue. Unfortunately it’s taking more time than we expected. We will update you as soon as possible.
Thank you!

[Steven Bricklayer]

I add the same problem and after too many exchange with the support, the answer is "ou must use a local admin account"

It's a real issue

[lyudmila.ezerskaya]

Thanks for bringing this to our attention.
We confirmed the issue with database restores and are currently working on fixing it and reducing the permission requirements in upcoming releases.