Azure App Service SQL Database Backup Fails

Question

I am having issues creating a backup of SQL db with my Azure App Service. In the Azure Portal, when I go to backup I am seeing this error:

Create Database copy of [database] threw an exception. Could not create Database copy. Make sure to use the admin user in the database connection string.

I get this error means the user does not have the correct permissions, but I am not able to grant the correct permissions. I have tried the following so far:

• Created an Azure Active Directory user and set it as admin for the sql server in the portal
• Added role "dbmanager" and "loginmanager" to the user
• Created the user in master and tried adding permissions there but it gives the error it does not exist or you do not have permission

I am not sure what else to do. Seems like when I create a new SQL instance in Azure it doesn't give the admin user all permissions.

Answer 1

First, we can not add permissions to the user in master db, when we try to do this, it will give us the error it does not exist or you do not have permission. master db is usually used to create login. Please switch to user db.

1. We can use the following T-sql to create an AAD user. Then AAD user can login to the UserDB, but by default the AAD user doesn't have any permissions to control the UserDB.

use <UserDB>;
CREATE USER [bob@example.com] FROM EXTERNAL PROVIDER;

2. Then we can execute T-sql to add permissions to the AAD user of the UserDB. So that the AAD user can perform database DDL operations.

EXEC sp_addrolemember 'db_owner', [bob@example.com]