-
- Lurker
- Posts: 1
- Liked: never
- Joined: Oct 11, 2023 5:13 pm
- Full Name: Richy G
- Contact:
Azure SQL restore - WITHOUT using system admin account
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.
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.
-
- Veeam Software
- Posts: 111
- Liked: 34 times
- Joined: Oct 04, 2021 4:08 pm
- Full Name: Lyudmila Ezerskaya
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
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.
-
- Novice
- Posts: 6
- Liked: 1 time
- Joined: Oct 11, 2023 4:53 pm
- Full Name: Rick Good
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
That is correct however with Azure SQL, even with all the admin permissions added to the account, it still fails.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.
Here is the output in Veeam if that helps: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.
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)
-
- Veeam Software
- Posts: 111
- Liked: 34 times
- Joined: Oct 04, 2021 4:08 pm
- Full Name: Lyudmila Ezerskaya
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
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?
Could you confirm whether you performed a successful connection check at the Summary step of the wizard before starting the restore operation?
-
- Novice
- Posts: 6
- Liked: 1 time
- Joined: Oct 11, 2023 4:53 pm
- Full Name: Rick Good
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
Yes, the connection test passes.
-
- Veeam Software
- Posts: 111
- Liked: 34 times
- Joined: Oct 04, 2021 4:08 pm
- Full Name: Lyudmila Ezerskaya
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
Thank you for sharing. We'll need some extra time for the research.
-
- Novice
- Posts: 6
- Liked: 1 time
- Joined: Oct 11, 2023 4:53 pm
- Full Name: Rick Good
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
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)
3. Use this SQL account when restoring a database into the server
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)
2. Add the new SQL account to VeeamIn 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.
3. Use this SQL account when restoring a database into the server
-
- Novice
- Posts: 6
- Liked: 1 time
- Joined: Oct 11, 2023 4:53 pm
- Full Name: Rick Good
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
Has there been any update or activity for this issue?
-
- Veeam Software
- Posts: 111
- Liked: 34 times
- Joined: Oct 04, 2021 4:08 pm
- Full Name: Lyudmila Ezerskaya
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
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!
Thank you!
-
- Influencer
- Posts: 23
- Liked: 2 times
- Joined: Apr 04, 2017 8:42 am
- Full Name: Steven Bricklayer
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
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
It's a real issue
-
- Veeam Software
- Posts: 111
- Liked: 34 times
- Joined: Oct 04, 2021 4:08 pm
- Full Name: Lyudmila Ezerskaya
- Contact:
Re: Azure SQL restore - WITHOUT using system admin account
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.
We confirmed the issue with database restores and are currently working on fixing it and reducing the permission requirements in upcoming releases.
Who is online
Users browsing this forum: No registered users and 1 guest