none
Azure SQL backups on Azure VMs with SQL Server RRS feed

  • Question

  • Hi 

    Trying to configure Azure Backups for SQL Server on Azure VM

    We chose Recovery Services Vault and start discover - works fine - when trying to setup backup we get error UserErrorSQLNoSysadminMembership.

    We see on the database VM that we have service Azure Backup Workload Plugin Service running under account NT Service\AzureWLBackupPluginSvc -- this account must be added to SQL Server as sysadmin, BUT...the account cannot be found ??

    What can be wrong here?

    Best regards,

    Bjarne Kristensen

    SQL DBA / Milestone

    Tuesday, October 8, 2019 12:47 PM

All replies

  • The error you have reported “UserErrorSQLNoSysadminMembership”, is a common scenario, which should be solved by following below link and steps:
    https://docs.microsoft.com/en-us/azure/backup/backup-azure-sql-database#set-permissions-for-non-marketplace-sql-vms
     
    We need to have the following logins created on the SQL Instance:
    NT AUTHORITY\SYSTEM - no sysadmin need (but needs public)
    NT Service\AzureWLBackupPluginSvc  (with sysadmin)
     
    We have seen scenarios that even when  already having the logins created on SQL Instance, the error may arise when the underlying issue is in the uppercase name of NT SERVICE, so we need it to be camelCase.
     
    Usually we can fix this by a manual rename in SSMS: 

     
    Lets do this step by step:
     
    Does an account of this name NT Service\AzureWLBackupPluginSvc or this name NT SERVICE\AzureWLBackupPluginSvc exist in SQL as a login ?
    If not, just simply run:
     
    CREATE LOGIN [NT SERVICE \AzureWLBackupPluginSvc] FROM WINDOWS
    GO
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\AzureWLBackupPluginSvc]
    GO
    //
    //Now rename the account from ssms  to NT Service\AzureWLBackupPluginSvc
     
    If it exists as NT SERVICE\AzureWLBackupPluginSvc, then:
     
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT Service\AzureWLBackupPluginSvc]
    GO
    //
    // Then rename as above.
     
    This usually solves the issue. But on a very few customer's SQL servers we cannot rename the login and all the above fails...
     
    If the discovery still fails due to capitalization issues:
     
    Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\AzureWLBackupPluginSvc and look at the “ObjectName” Value.
     
    It will be NT Service\AzureWLBackupPluginSvc
     
    Rename this to NT SERVICE\AzureWLBackupPluginSvc
     
    Restart the server, and then things should work.
    Wednesday, October 9, 2019 1:25 PM
    Moderator