Azure Backup: Configuring a backup for a SQL Server in an Azure VM fails with error UserErrorSQLNoSysadminMembership

These days you can also use Azure Backup to backup SQL Server databases and instances running inside Azure Virtual Machines (VMs). For this, the solution leverages the SQL native APIs to take backups of your SQL databases.

While configuring SQL Server Backup in Azure VMs for some new VMs hosting SQL databases, I encountered the following error code and message:

UserErrorSQLNoSysadminMembership

Azure Backup service creates a service account “NT Service\AzureWLBackupPluginSvc” (or “svc_AzWLBackupPlugin” user account in case of Windows Server 2008) for all operations and this account needs SQL sysadmin privilege.

You should know that in order to be able to discover databases running on Azure VMs, Azure Backup creates the NT Service\AzureWLBackupPluginSvc account (Windows virtual service account). This account requires SQL sysadmin permissions on all instances where you want to discover databases and be able to backup and restore them.

Because the VMs hosting the SQL Databases were not created from one of the Azure Marketplace SQL Server VM Images, the proper permissions for this account were not set automatically. So, I had to follow the steps below to make the backup work properly.

Solution

Open SQL Server Management Studio (SSMS) and connect to the SQL Server using an account with SQL Server sysadmin permissions to sign in. Except in case you need special permissions, Windows authentication should be fine.

These steps will work for all SQL Server versions except SQL 2008, SQL 2008 R2 and earlier.

On the SQL Server, first open the Security folder, and then right-click the Logins folder and select New Login…

On the new Login screen select Search.

Because the NT SERVICE\AzureWLBackupPluginSvc account is already created during the VM registration and the SQL discovery phase, you can enter the account name under the Enter the object name to select. Click Check Names to resolve the name and select OK.

NT SERVICE\AzureWLBackupPluginSvc

Next, click Server Roles and make sure the sysadmin role is selected. Then click OK.

Now the NT SERVICE\AzureWLBackupPluginSvc account with the necessary permissions should be created.

You can also use T-SQL to create the Login with the necessary permissions. In SSMS click New Query. Copy and paste the below in the query and click Execute.

CREATE LOGIN [NT SERVICE\AzureWLBackupPluginSvc] FROM WINDOWS;
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\AzureWLBackupPluginSvc]
GO

You can now go back to the Azure Portal to associate the databases with the Recovery Services vault. Go to the Protected Servers list and right-click the VM that is in an error state. Select Rediscover DBs.

You can follow along with the progress in the Notifications area. If all is now working like it should, the selected databases will be found and a success message will appear, saying that the Discovering DBs operation completed.

Conclusion

To be able to discover SQL databases running on Azure VMs, Azure Backup creates the NT Service\AzureWLBackupPluginSvc account which needs to have SQL sysadmin permissions on all instances where you want to be able to backup and restore them.