Hello everyone!

I know that sometimes we do not want to use the "master" SQL admin account to perform the backups. There are customers that are not pretty sure about how to set it up properly, without the need of giving the master SQL admin account credentials to perform backups.

With these steps you will be able to perform the backups using a custom SQL Login (Server-level) and preserve your SQL admin account.

On this demonstration I will use two Azure managed SQL servers, one with my DB and one for staging.

The DB is sitting on barella-sql-2-delete:

You will need to connect to the SQL Server to create the new Server-Level login and assign proper roles.

Make sure you configure the Firewall rules properly for your SQL servers, allowing you to connect to the server:

Create a new Login on your STAGING server:

--Create a new login
CREATE LOGIN /your_new_login] WITH PASSWORD = N’type password here’;

--Assing roles

ALTER SERVER ROLE ##MS_DatabaseManager## ADD MEMBER nyour_new_login];

ALTER SERVER ROLE ##MS_LoginManager## ADD MEMBER nyour_new_login];

ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER eyour_new_login];

ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER eyour_new_login];

The above are queries to create the login and the four roles you need to add to the new server-level login.

Login will appear under Logins section:

You can use the following query to validate all roles are added:

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

Now we are ready to switch back to Veeam Backup for Azure UI and create a new SQL policy.

Here is the User-guide page explaining how to create a SQL backup policy so I don't need to go into much details here except for the actually needed ones.

https://helpcenter.veeam.com/docs/vbazure/guide/sql_backup_create.html?ver=70

Under Processing Options you will select Staging server:

Select the Staging server and if you don't have the SQL account yet, click on +Add

Enter the credentials you created before on SQL queries:

Select the new account:

Finish your policy configuration.

This should be enough for you to perform backups of your SQL databases using a Staging server.

If you want to restore the databases to different servers, you must make sure you have the SQL Server login with the correct roles (as explained above) in the server you are restoring to.

I hope this will be helpful for you and do not hesitate to leave a comment or a question if you need any clarification.

See you next time!