Views:

Summary


This article describes the permissions required for a domain user account configured for DPX cmagent to backup and restore a SQL Server cluster.

 

Step By Step

 

Domain Admin Account is Necessary to Install DPX

The user account, hereinafter refered to as “DPX domain user account”, provided during the installation may not necessarily be the same as the DPX services account, i.e. cluster cmagent service, physical node cmagent servcies, and advanced protection services to run backups and restores. Note that different levels of privileges are generally required to backup and restore volumes, SQL Server/Exchange/Sharepoint applications, active directory, system state/table, BMR, stand-alone node, and Windows cluster nodes.

Summary of User Permissions to Run Backups and Restores of a SQL Cluster

To summarize, the following user group and privileges are the minimum requirement to run backups and restores of a SQL cluster containing SQL (db/transaction logs, and etc.), cluster volumes, local volumes, and BMR:

  • DPX domain user account with the privilege to copy files between the cluster nodes (BMR backups need to use UNC share to copy backup metadata files across the cluster nodes of the cluster)

  • DPX domain user account needs to have permission to open cluster resources (DPX need to identify cluster disks, and local disks)

  • DPX domain user account needs to have local administrator rights to open volumes to get volume information, backup, restore files, adjust privileges of a user account, read DPX driver registry, write to DPX logs/tmp locations. The highest privilege required is the volume/disk query operations. Based on Microsoft documentation, the user account must have administor privilege to access volumes/disks at: http://msdn.microsoft.com/en-us/library/aa363858%28VS.85%29.aspx

  • DPX domain user account needs to have SQL sysadmin privilege to connect to the SQL server, query SQL version, restore SQL db/transaction logs (db_owner has the permission to restore its created dbs. Sysadmin or db_creator roles are required to restore dbs by non-owner users), mount/dismount SQL databases. Refer to the following Microsoft article about the permissions to backup/restore SQL servers at: http://technet.microsoft.com/en-us/library/cc966495.aspx#E0DB0AA

“Permissions Required for Backup and Restore
Any logon that requires permissions to perform backup or restore operations should be provided membership in the following SQL Server roles:
Server Role : sysadmin
DB role : db_backupoperator, dbo_owner
Permissions required for performing restore -
Server role : sysadmin, dbcreator
DB role : db_owner

“

Setting Up a DPX Domain User for Backup and Restore of a SQL Cluster

  1. Within domain controller computer, create a DPX user; for example “sqluserrd” and assign the user to Domain Users group.

 

 


 

 

2. Within the SQL client of the SQL cluster, start SQL management studio and login with a sysadmin user account depending on the SQL authentication type (i.e. Windows authentication or SQL/Windows mixed authentication).
Add the domain user created for DPX, i.e. “sqluserrd”, to the SQL sysadmin group. Refer to the “summary” section for detailed roles and permission for SQL backup/restore.


3. Within the SQL client (log in with the Domain or local admin privileges to give user account type and assign user rights), start server manager > local users and groups > groups to add “sqluserrd” to local Administrators group.



4. Within the SQL cluster, start the failover cluster management console/cluster administration control and ensure that the administrators group and/or “sqluserrd” has full control of the cluster resources.



5. Assign Debug programs user rights to the user and/or Administrators within the Local security policy console > User right assignment, then issue the command to update the policy, restart DPX cmagent and nibbler services:gpupdate /force


 

Refer to this article at: http://technet.microsoft.com/en-us/library/dd277404.aspx for detailed procedure on Assigning User Rights.


6. Browse the SQL cluster within DPX GUI > Backup > ARSV > all objects, volumes, SQL, SQL version, BMR, physical server’s system state and system table. They display properly just as service accounts with administrator privileges.


 

By default, BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) accounts are not provisioned in the “sysadmin” server role in SQL.
This may result in error during backup:
MsUtils::sql_create_backup_temp_dir:Failed to get the log path of current db [XXXXXX]
Corresponding *.snp log on the SQL server may have:
80131904 Error: The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "XXXXXX" under the current security context.
To address this issue, we need to ensure that “NT AUTHORITY\SYSTEM” account is also a member of ‘syadmin” Role in MS SQL Server Management Studio.

This can be done in the Object Explorer by expanding Server -> Security -> Server Roles -> sysadmin:


 

Keywords: roles
Comments (0)